From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | JPalmer(at)linz(dot)govt(dot)nz, andy(at)squeakycode(dot)net, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Possible to improve query plan? |
Date: | 2011-01-24 16:33:28 |
Message-ID: | AANLkTi=xD9hJEbtdzzaKO9eNBDSY7WZk=y6Y2KshgPF+@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2011-01-24 16:50:41 | Re: Possible to improve query plan? |
Previous Message | Achilleas Mantzios | 2011-01-24 15:40:40 | Re: "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2 |