Fastgin Performance

fastgin_performance_workmem.sql

-- create and populate a table with a GIN-indexable column
-- we pre-populate it to a large size; autovacuum kicks in when more than
-- a configured proportion of the table is newly inserted, and we want to
-- model the case where that doesn't happen often

drop table gtst;

create table gtst as
  select array[i,j,i*j] as a
    from generate_series(1,1000) i, generate_series(1,1000) j;

-- make the index (the fast update queue will be empty)
create index gtst_idx on gtst using gin (a);

-- create and populate a table with a GIN-indexable column
-- we pre-populate it to a large size; autovacuum kicks in when more than
-- a configured proportion of the table is newly inserted, and we want to
-- model the case where that doesn't happen often

-- create table gtst as
--   select array[i,j,i*j] as a
--     from generate_series(1,1000) i, generate_series(1,1000) j;

-- make the index (the fast update queue will be empty)
-- create index gtst_idx on gtst using gin (a);

-- test performance; this query should be very fast:
--set fastgin_mem = '64kB';
explain analyze select * from gtst where a @> array[998001];

-- now fill the fast update queue with 10k rows:
set work_mem = '256MB';
--set fastgin_mem = '256MB';
insert into gtst select array[i,j,i*j] as a from generate_series(1,100) i, generate_series(1,100) j;

-- observe the query is much slower
explain analyze select * from gtst where a @> array[998001];

-- force the queue to be drained:

set work_mem = '64kB';
--set fastgin_mem = '64kB';

insert into gtst select array[1,1,1];

-- (or just do "vacuum gtst;" in place of the above two)

-- test performance again

explain analyze select * from gtst where a @> array[998001];