On Mon, Jan 17, 2011 at 11:48 AM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
