From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: query slowdown after 9.0 -> 9.4 migration
Date: Thu, 27 Oct 2016 07:38:38 +0200
Message-ID: <20161027053838.GA5952@tux> (raw)
In-Reply-To: <c933c63e-4050-49ef-566c-0b40de1f46dd@2ndquadrant.com>
References: <CAP_rwwn6AqwfCDsmpzVaoZd_6FSu7RFECqqfUPGf2n9uv+VVuA@mail.gmail.com>
<c933c63e-4050-49ef-566c-0b40de1f46dd@2ndquadrant.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Tomas Vondra <tomas.vondra@2ndquadrant.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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: akretschmer@spamfence.net
Subject: Re: query slowdown after 9.0 -> 9.4 migration
In-Reply-To: <20161027053838.GA5952@tux>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox