Re: can't coax query planner into using all columns of a gist index

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Gideon Dresdner <gideond(at)gmail(dot)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
>