From: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: query slowdown after 9.0 -> 9.4 migration |
Date: | 2016-10-26 22:55:22 |
Message-ID: | c933c63e-4050-49ef-566c-0b40de1f46dd@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 10/26/2016 03:48 PM, Filip Rembiałkowski wrote:
> Hi.
>
> Query run time degraded after migration from Pg 9.0 + postgis 1.5 to Pg
> 9.4 + postgis 2.2.
>
> 1 ms versus 7 ms.
>
> Same query, same data, same schema, similar hardware. Data is small and
> fits in cache.
>
> EXPLAIN shows heap scan cost increase. What can be the reason for
> 40-fold increase in page scans needed to run Bitmap Heap Scan with
> Filter and Recheck?
>
On 9.0 the the scan accesses only 8 buffers:
Buffers: shared hit=8
while on 9.4 it has to inspect 316 of them:
Buffers: shared hit=316
Perhaps the table is organized / sorted differently, or something like
that. How did you do the upgrade?
ragards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2016-10-27 05:38:38 | Re: query slowdown after 9.0 -> 9.4 migration |
Previous Message | Filip Rembiałkowski | 2016-10-26 13:48:42 | query slowdown after 9.0 -> 9.4 migration |