Subject: Re: query slowdown after 9.0 -> 9.4 migration
To: pgsql-performance@postgresql.org
References: 
 <CAP_rwwn6AqwfCDsmpzVaoZd_6FSu7RFECqqfUPGf2n9uv+VVuA@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <c933c63e-4050-49ef-566c-0b40de1f46dd@2ndquadrant.com>
Date: Thu, 27 Oct 2016 00:55:22 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:46.0) Gecko/20100101
 Thunderbird/46.0a2
MIME-Version: 1.0
In-Reply-To: 
 <CAP_rwwn6AqwfCDsmpzVaoZd_6FSu7RFECqqfUPGf2n9uv+VVuA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance