I've created a small dump of my database that recreates the problem. I hope that this will help recreate the problem. It is attached. I'd be happy to hear if there is an easier way of doing this.
To rebuild the database: - create a database - run from the commandline `$ psql database-name < 1000genomes-schema.sql` - run this within a psql REPL ` #\copy public.qcregions FROM '/tmp/1000genomes-qcregions.tsv' DELIMITER ' ' CSV;` (where the delimiter is a tab) - similarly run this within a psql REPL, `#\copy public.vcf FROM '/tmp/1000genomes-vcf.tsv' DELIMITER ' ' CSV;` To see that the GIST index is not being hit, try running the following query: EXPLAIN SELECT * FROM vcf WHERE EXISTS (SELECT region FROM qcregions WHERE qcregions.chr = vcf.chr AND vcf.pos <@ qcregions.region); The actual query I am trying to run is: EXPLAIN SELECT * FROM vcf WHERE EXISTS (SELECT region FROM qcregions WHERE qcregions.chr = vcf.chr AND qcregions.type = 'include' AND vcf.pos <@ qcregions.region); Let me know what else I can try, Gideon. On Wed, Aug 12, 2015 at 11:07 AM Gideon Dresdner <gide...@gmail.com> wrote: > What's a good way for me to create a self-contained test case. AFAIU the > only way to make these test cases more self-contained would be to inline > the second table and its index. How do you create an index to an inlined > table of values? > > Or perhaps I could send over a dump of a subset of the data? > > Yes, I am fairly sure that I am running 9.4.4: > > $ psql --version > psql (PostgreSQL) 9.4.4 > > # select version(); > version > > > ----------------------------------------------------------------------------------- > PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) > 5.1.0, 64-bit > (1 row) > > Thanks for the help, > Gideon. > > On Tue, Aug 11, 2015 at 10:23 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > >> Gideon Dresdner <gide...@gmail.com> writes: >> > I had a discussion on IRC today with RhodiumToad regarding optimizing a >> > specific query. We didn't manage to figure out how to get postgres to >> hit a >> > GIST index. >> >> FWIW, I couldn't reproduce the described behavior. Can you provide a >> self-contained test case? Are you sure your server is 9.4.4? >> >> regards, tom lane >> >
1000genomes-dump.tar.gz
Description: application/gzip
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers