Re: Possible to improve query plan?

On Mon, Jan 17, 2011 at 11:48 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> 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.

But isn't 13.5 seconds awfully slow to scan 149557 rows? The sort is
sorting 23960kB. Dividing that by 149557 rows gives ~169 bytes/per
row, or roughly 49 rows per block, which works out to 3k blows, or
about 24MB of data. Clearly we must be hitting a LOT more data than
that, or this would be much faster than it is, I would think.

Any chance this is 9.0.X? It'd be interesting to see the EXPLAIN
(ANALYZE, BUFFERS) output for this query.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company