From: | Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com> |
---|---|
To: | postgres performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | query slowdown after 9.0 -> 9.4 migration |
Date: | 2016-10-26 13:48:42 |
Message-ID: | CAP_rwwn6AqwfCDsmpzVaoZd_6FSu7RFECqqfUPGf2n9uv+VVuA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2016-10-26 22:55:22 | Re: query slowdown after 9.0 -> 9.4 migration |
Previous Message | Lars Aksel Opsahl | 2016-10-25 20:52:05 | Re: Fast insert, but slow join and updates for table with 4 billion rows |