On Mon, Jan 17, 2011 at 11:48 AM, Kevin Grittner
<Kevin.Grittner@wicourts.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
