From: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: query slowdown after 9.0 -> 9.4 migration |
Date: | 2016-10-27 05:38:38 |
Message-ID: | 20161027053838.GA5952@tux |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
> 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.
>> 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
nice point ;-)
>
> Perhaps the table is organized / sorted differently, or something like
> that. How did you do the upgrade?
Maybe table-bloat? Filip, check if autovacuum runs properly.
Regards, Andreas Kretschmer
--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Craig James | 2016-10-27 20:46:05 | limit 1 on view never finishes |
Previous Message | Tomas Vondra | 2016-10-26 22:55:22 | Re: query slowdown after 9.0 -> 9.4 migration |