[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: [postgis-users] Fwd: query slowdown after 9.0 -> 9.4 migration
From: Filip_Rembiałkowski <filip.rembialkowski () gmail ! com>
Date: 2016-11-03 11:00:11
Message-ID: CAP_rwwmUS13kvRqc-bSR9MG6+8USVjh2OLM0g973R2C25G6LDg () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Salut, postgis users.
Reposting from pgsql-performance. I'd be grateful for any pointers.
> 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
[Attachment #5 (text/html)]
<p dir="ltr">Salut, postgis users.</p>
<p dir="ltr">Reposting from pgsql-performance. I'd be grateful for any \
pointers.<br></p> <p dir="ltr">> Query run time degraded after migration from Pg \
9.0 + postgis 1.5 to Pg 9.4 + postgis 2.2.<br> ><br>
> 1 ms versus 7 ms.<br>
><br>
> Same query, same data, same schema, similar hardware. Data is small and fits in \
cache.<br> ><br>
> 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?<br> \
><br> > GIST index performance looks OK.<br>
><br>
><br>
> 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<br> > \
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)<br> > -> <a \
href="https://explain.depesz.com/s/C3Vw">https://explain.depesz.com/s/C3Vw</a><br> \
><br> > PostgreSQL 9.4.7 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) \
4.8.5, 64-bit<br> > 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)<br> > -> <a \
href="https://explain.depesz.com/s/24GA">https://explain.depesz.com/s/24GA</a><br> \
><br> ><br>
> Query:<br>
><br>
> 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;<br> ><br>
><br>
><br>
> thanks for any suggestions / ideas.<br>
><br></p>
<p dir="ltr">Filip</p>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic