can't coax query planner into using all columns of a gist index
Greetings,
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.
The bigger picture is that I am trying to do some bioinformatics and
thought that postgres would be a great way of getting the 1000 genomes
project "in the palm of my hand" instead of submitting C++ programs to a
cluster, waiting for the results, etc.
Anyway, there is a multicolumn index that we created:
CREATE INDEX qcregions_chrregion_index ON qcregions USING gist(chr, region)
WHERE type = 'include'
- chr is an int between 1 and 24 (inclusive)
- region is an int4range
- type is an enum with two values, 'include' and 'exclude'.
Here are three relevant explain outputs:
Example 1:
EXPLAIN SELECT * FROM (values (12,5000), (13,5001) ) v(c,r)
WHERE EXISTS (SELECT region FROM qcregions
WHERE type = 'include' and region @> v.r and chr = v.c);
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Nested Loop Semi Join (cost=0.41..8.82 rows=1 width=8)
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=8)
-> Index Scan using qcregions_chrregion_index on qcregions
(cost=0.41..3464.26 rows=874 width=17)
Index Cond: ((chr = "*VALUES*".column1) AND (region @>
"*VALUES*".column2))
(4 rows)
Time: 1.284 ms
Example 2:
-- set enable_setbitmapscan = true
EXPLAIN SELECT * FROM (select * from vcf limit 2) AS vcf
WHERE EXISTS (SELECT region FROM qcregions
WHERE qcregions.chr = vcf.chr
AND qcregions.type = 'include'
AND vcf.pos <@ qcregions.region);
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Nested Loop Semi Join (cost=4862.57..18775.78 rows=1 width=64)
-> Limit (cost=0.00..0.04 rows=2 width=64)
-> Seq Scan on vcf (cost=0.00..1894654.40 rows=84801840 width=64)
-> Bitmap Heap Scan on qcregions (cost=4862.57..7873.60 rows=874
width=17)
Recheck Cond: ((vcf.pos <@ region) AND (type =
'include'::qcregiontype) AND (chr = vcf.chr))
-> BitmapAnd (cost=4862.57..4862.57 rows=874 width=0)
-> Bitmap Index Scan on qcregions_chrregion_index
(cost=0.00..977.76 rows=20980 width=0)
Index Cond: (vcf.pos <@ region)
-> Bitmap Index Scan on qcregions_chr_index
(cost=0.00..3884.12 rows=215158 width=0)
Index Cond: (chr = vcf.chr)
(10 rows)
Time: 0.708 ms
Example 3 (same as example 2 but with enable_bitmapscan = false).
-- set enable_bitmapscan = false
EXPLAIN SELECT * FROM (select * from vcf limit 2) AS vcf
WHERE EXISTS (SELECT region FROM qcregions
WHERE qcregions.chr = vcf.chr
AND qcregions.type = 'include'
AND vcf.pos <@ qcregions.region);
QUERY PLAN
--------------------------------------------------------------------------------------------------
Nested Loop Semi Join (cost=0.43..38691.26 rows=1 width=64)
-> Limit (cost=0.00..0.04 rows=2 width=64)
-> Seq Scan on vcf (cost=0.00..1894654.40 rows=84801840 width=64)
-> Index Scan using qcregions_chr_index on qcregions
(cost=0.43..12891.38 rows=874 width=17)
Index Cond: (chr = vcf.chr)
Filter: ((type = 'include'::qcregiontype) AND (vcf.pos <@ region))
(6 rows)
Time: 1.214 ms
I am running psql (PostgreSQL) 9.4.4 on a laptop with 4 cores and 16 GB RAM.
Looking forward to hearing your thoughts,
Gideon.
Gideon Dresdner <gideond@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Gideon Dresdner <gideond@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 tohit 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
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 <gideond@gmail.com> wrote:
Show quoted text
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 <tgl@sss.pgh.pa.us> wrote:
Gideon Dresdner <gideond@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 tohit 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
Attachments:
Gideon Dresdner <gideond@gmail.com> writes:
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.
Ah. Now that I see the database schema, the problem is here:
regression=# \d vcf
...
chr | smallint |
...
So "chr" is smallint in one table and integer in the other. That means
the parser translates qcregions.chr = vcf.chr using the int42eq operator
instead of int4eq --- and nobody's ever taught btree_gist about crosstype
operators. So the clause simply isn't considered indexable with this
index. If you change the query to "qcregions.chr = vcf.chr::int" then
all is well.
Personally I'd just change vcf.chr to integer --- it's not even saving you
any space, with that table schema, because the next column has to be
int-aligned anyway.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
That did it! I certainly should have been able to figure that out on my
own. Thanks for the help!
Unfortunately, I'm still looking at rather slow queries across my entire
dataset. I might wind up having to find another solution.
Gideon.
On Wed, Aug 12, 2015 at 6:29 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Gideon Dresdner <gideond@gmail.com> writes:
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.Ah. Now that I see the database schema, the problem is here:
regression=# \d vcf
...
chr | smallint |
...So "chr" is smallint in one table and integer in the other. That means
the parser translates qcregions.chr = vcf.chr using the int42eq operator
instead of int4eq --- and nobody's ever taught btree_gist about crosstype
operators. So the clause simply isn't considered indexable with this
index. If you change the query to "qcregions.chr = vcf.chr::int" then
all is well.Personally I'd just change vcf.chr to integer --- it's not even saving you
any space, with that table schema, because the next column has to be
int-aligned anyway.regards, tom lane