test_gin_optimizations.sql

\timing on
DROP TABLE movies;
DROP TABLE lineitem;

CREATE TABLE movies(
    ReleaseYear int,
    Title text,
    Origin text,
    Director text,
    Casting text,
    Genre text,
    WikiPage text,
    Plot text
);

\COPY movies(ReleaseYear, Title, Origin, Director, Casting, Genre, WikiPage, Plot) FROM 'wiki_movie_plots_deduped.csv' DELIMITER ',' CSV HEADER;

CREATE TABLE lineitem (
	l_orderkey      BIGINT NOT NULL,
	l_partkey       BIGINT NOT NULL,
	l_suppkey       BIGINT NOT NULL,
	l_linenumber    INTEGER,
	l_quantity      DECIMAL,
	l_extendedprice DECIMAL,
	l_discount      DECIMAL,
	l_tax           DECIMAL,
	l_returnflag    CHAR(1),
	l_linestatus    CHAR(1),
	l_shipdate      DATE,
	l_commitdate    DATE,
	l_receiptdate   DATE,
	l_shipinstruct  CHAR(25),
	l_shipmode      CHAR(10),
	l_comment       VARCHAR(44)
);

COPY lineitem FROM 'lineitem.tbl' WITH delimiter '|';

SET max_parallel_maintenance_workers = 0;

CREATE INDEX ON movies USING gin(Plot gin_trgm_ops);
CREATE INDEX ON movies USING gin(Plot gin_trgm_ops);
CREATE INDEX ON movies USING gin(Plot gin_trgm_ops);
CREATE INDEX ON lineitem USING gin(l_comment gin_trgm_ops);
CREATE INDEX ON lineitem USING gin(l_comment gin_trgm_ops);
CREATE INDEX ON lineitem USING gin(l_comment gin_trgm_ops);