Sergei Shelukhin <realgeek@gmail.com> writes:
> The query is very slow (3 minutes on test data), here's what takes all
> the time, from explain results:
>> Bitmap Heap Scan on tag (cost=897.06..345730.89 rows=115159 width=8)
> Recheck Cond: ((name)::text = 'blah'::text)
> -> Bitmap Index Scan on tag_idxn
> (cost=0.00..897.06 rows=115159 width=0)
> Index Cond: ((name)::text =
> 'blah'::text)
It's usually a good idea to do EXPLAIN ANALYZE on troublesome queries,
rather than trusting that the planner's estimates reflect reality.
> The query without recheck will run like up to 100 times faster
> according to overall query plan.
Sorry, but you have no concept what you're talking about. The
difference between indexscan and heap scan estimates here reflects
fetching rows from the heap, not recheck costs. Even if it were
a good idea to get rid of the recheck (which it is not), it wouldn't
reduce the costs materially.
If the table is fairly static then it might help to CLUSTER on that
index, so that the rows needed are brought together physically.
regards, tom lane