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 <t...@sss.pgh.pa.us> wrote: > Gideon Dresdner <gide...@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 >
