Liviu Mirea-Ghiban wrote:
>
> My question is: Why is it doing a Bitmap Heap Scan / Recheck Cond?
> I've executed dozens of such queries and not once did the rechecking
> remove any rows. Is there any way to disable it, or do you have any
> other suggestions for optimizations (because I'm all out of ideas)?
It's probably because the index nodes store data values with a lossy
compression, which means that the index scan returns more rows than
wanted, and that in turn is filtered out by the rescanning. See the
comments for the 'RECHECK' parameter of CREATE OPERATOR CLASS
(http://www.postgresql.org/docs/8.4/static/sql-createopclass.html). Its
unwise to alter this behaviour without taking a look/modifying the
underlying index implementation. The gist index scann part could perhaps
be made a bit faster by using a smaller blocksize, but I'm not sure if
or how the recheck part can be improved. Maybe rewriting the top query
to not do bitmap heap scans in subqueries or inner loops?
regards,
Yeb Havinga