Created
June 11, 2013 17:35
-
-
Save Ilink/5758951 to your computer and use it in GitHub Desktop.
Fastgin Performance
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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]; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment