test.sql

application/sql

Download

BEGIN;
CREATE EXTENSION pg_trgm;

CREATE TABLE t1 (
  id text primary key
);

CREATE TABLE t2 (
  id text primary key,
  t1_id text
);

CREATE INDEX t1_gin_index ON t1 USING gin (id gin_trgm_ops);

insert into t1 (id)
select i::text FROM generate_series(1, 1500) as i;

insert into t2 (id, t1_id)
SELECT i::text, (i % 1500 + 1)::text FROM generate_series(1, 20000) i;

SET random_page_cost = 0.00000001;
SET enable_hashjoin = off;

ANALYZE t1, t2;

explain (analyze, buffers) select * from t1 join t2 on t1.id = t2.t1_id ;

DROP INDEX t1_gin_index;

explain (analyze, buffers) select * from t1 join t2 on t1.id = t2.t1_id ;

ROLLBACK;