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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
