pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery slowdown after 9.0 -> 9.4 migration
5+ messages / 4 participants
[nested] [flat]
* query slowdown after 9.0 -> 9.4 migration
@ 2016-10-26 13:48 Filip Rembiałkowski <filip.rembialkowski@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Filip Rembiałkowski @ 2016-10-26 13:48 UTC (permalink / raw)
To: pgsql-performance
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?
GIST index performance looks OK.
PostgreSQL 9.0.23 on x86_64-suse-linux-gnu, compiled by GCC gcc (SUSE
Linux) 4.8.3 20140627 [gcc-4_8-branch revision 212064], 64-bit
POSTGIS="1.5.4" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March
2012" LIBXML="2.7.8" USE_STATS (procs from 1.5 r5976 need upgrade)
-> https://explain.depesz.com/s/C3Vw
PostgreSQL 9.4.7 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
4.8.5, 64-bit
POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08
September 2015" GDAL="GDAL 2.1.0, released 2016/04/25 GDAL_DATA not found"
LIBXML="2.9.1" LIBJSON="0.12" (core procs from "2.2.1 r14555" need upgrade)
TOPOLOGY (topology procs from "2.2.1 r14555" need upgrade) RASTER (raster
procs from "2.2.1 r14555" need upgrade)
-> https://explain.depesz.com/s/24GA
Query:
SELECT
round(meters_to_miles(st_distance_sphere(ST_GeomFromText('POINT(-77.0364
38.89524)', 4326),llpoint))::numeric,2) as _distance FROM storelocator
WHERE st_expand(ST_GeomFromText('POINT(-77.0364 38.89524)', 4326),
miles_to_degree(50,38.89524)) && llpoint AND
st_distance_sphere(ST_GeomFromText('POINT(-77.0364 38.89524)', 4326),
llpoint) <= miles_to_meters(50) ORDER BY _distance LIMIT 10;
thanks for any suggestions / ideas.
Filip
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query slowdown after 9.0 -> 9.4 migration
@ 2016-10-26 22:55 Tomas Vondra <tomas.vondra@2ndquadrant.com>
parent: Filip Rembiałkowski <filip.rembialkowski@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tomas Vondra @ 2016-10-26 22:55 UTC (permalink / raw)
To: 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query slowdown after 9.0 -> 9.4 migration
@ 2016-10-27 05:38 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 1 reply; 5+ messages in thread
From: Andreas Kretschmer @ 2016-10-27 05:38 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query slowdown after 9.0 -> 9.4 migration
@ 2016-10-28 01:37 Filip Rembiałkowski <filip.rembialkowski@gmail.com>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 5+ messages in thread
From: Filip Rembiałkowski @ 2016-10-28 01:37 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-performance
On Thu, Oct 27, 2016 at 7:38 AM, Andreas Kretschmer <
akretschmer@spamfence.net> wrote:
> Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>
> >
> > Perhaps the table is organized / sorted differently, or something like
> > that. How did you do the upgrade?
>
>
Nothing special, dump + reload. The table in question is tiny - 280 kB, 859
rows.
> Maybe table-bloat? Filip, check if autovacuum runs properly.
>
>
Yes, it does. Just to be sure I ran VACUUM FULL, ANALZYE and REINDEX on all
tables and indexes - no change :-(
Any other ideas (before drawing on heavy tools like strace)?
Does it make sense to ask on postgis-users list?
Thanks,
Filip
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query slowdown after 9.0 -> 9.4 migration
@ 2016-11-02 23:43 Jim Nasby <Jim.Nasby@BlueTreble.com>
parent: Filip Rembiałkowski <filip.rembialkowski@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jim Nasby @ 2016-11-02 23:43 UTC (permalink / raw)
To: Filip Rembiałkowski <filip.rembialkowski@gmail.com>; Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-performance
On 10/27/16 8:37 PM, Filip Rembiałkowski wrote:
> Does it make sense to ask on postgis-users list?
Yes. I suspect that the reason Buffers: shared hit is so high is because
of something st_distance_sphere() is doing.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2016-11-02 23:43 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-10-26 13:48 query slowdown after 9.0 -> 9.4 migration Filip Rembiałkowski <filip.rembialkowski@gmail.com>
2016-10-26 22:55 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2016-10-27 05:38 ` Andreas Kretschmer <akretschmer@spamfence.net>
2016-10-28 01:37 ` Filip Rembiałkowski <filip.rembialkowski@gmail.com>
2016-11-02 23:43 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
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