Re: can't coax query planner into using all columns of a gist index - Mailing list pgsql-hackers

From Tom Lane
Subject Re: can't coax query planner into using all columns of a gist index
Date
Msg-id 28726.1439418586@sss.pgh.pa.us
Whole thread Raw
In response to Re: can't coax query planner into using all columns of a gist index  (Gideon Dresdner <gideond@gmail.com>)
Responses Re: can't coax query planner into using all columns of a gist index  (Gideon Dresdner <gideond@gmail.com>)
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Josh Berkus
Date:
Subject: Re: WIP: SCRAM authentication
Next
From: Robert Haas
Date:
Subject: Re: WIP: SCRAM authentication