Jeremy Palmer wrote:
> WHERE (
> (_revision_created <= 16
> AND _revision_expired > 16
> AND _revision_expired <= 40)
> OR (_revision_created > 16
> AND _revision_created <= 40))
> -> Bitmap Heap Scan on version_crs_coordinate_revision
> (actual time=70.925..13531.720 rows=149557 loops=1)
> -> BitmapOr (actual time=53.650..53.650 rows=0 loops=1)
This plan actually looks pretty good for what you're doing. The
Bitmap Index Scans and BitmapOr determine which tuples in the heap
need to be visited. The Bitmap Heap Scan then visits the heap pages
in physical order (to avoid repeated fetches of the same page and to
possibly edge toward sequential access speeds). You don't seem to
have a lot of bloat, which could be a killer on this type of query,
since the rowcounts from the index scans aren't that much higher than
the counts after you check the heap.
The only thing I can think of which might help is to CLUSTER the
table on whichever of the two indexes used in the plan which is
typically more selective for such queries. (In the example query
that seems to be idx_crs_coordinate_revision_created.) That might
reduce the number of heap pages which need to be accessed and/or put
place them close enough that you'll get some sequential readahead.
I guess you could also try adjusting effective_io_concurrency upward
to see if that helps.
-Kevin