<div dir="ltr">I restored a backup of this database in pg10 2.5.3 and updated postgis to 3.0.7, at first the query used the Merge Semi Join and the performance was fine, so I force to use Nested Loop and perforce was dropped.<br>So i think our problem was that postgres changed the plan for this query and st_dwithin(geography, geography) in loop didn't perform well, but I still don't understand why postgres is choosing to run this query with Nested Loop.<br><br>I did other tests with a subset of the data and with a similar query using nested loop the performance was not good in 2.5 or 3.0.<br>I uploaded the subset to this repository <a href="https://github.com/aamallmann/postgis-region-db">https://github.com/aamallmann/postgis-region-db</a> if you want to test.<br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Em seg., 17 de jul. de 2023 às 16:40, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> escreveu:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div lang="EN-US"><div><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">I looked at our list of items between 3.1.7 and 3.1.8 <u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><a href="https://git.osgeo.org/gitea/postgis/postgis/src/branch/stable-3.1/NEWS" target="_blank">https://git.osgeo.org/gitea/postgis/postgis/src/branch/stable-3.1/NEWS</a><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">and can’t find any that would cause what you describe below.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">It’s possible whatever was changed we didn’t consider strong enough to put in the news.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">I haven’t checked the git repo yet to see what changed.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Usually these switches in plans are often triggered by changes in costs of the functions or just data and it is possible we adjusted some of the costs on these functions.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">I was thinking the 10 to 14 switch might have been caused by the function instrumentation changes.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">But that wouldn’t explain your experiencing the same issue on 14 3.1.7 vs 14 3.1.8<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">If you still have both running, can you give us the output <u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">SELECT postgis_full_version(), version();<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">So we can confirm we are truly comparing only a difference in PostGIS version and not the libraries used or PostgreSQL minor version.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Thanks,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Regina<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><div style="border-top:none;border-right:none;border-bottom:none;border-left:1.5pt solid blue;padding:0in 0in 0in 4pt"><div><div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid rgb(225,225,225);padding:3pt 0in 0in"><p class="MsoNormal"><b><span style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span style="font-size:11pt;font-family:Calibri,sans-serif"> postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Anderson Mallmann<br><b>Sent:</b> Monday, July 17, 2023 10:23 AM<br><b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>; <a href="mailto:lviecelli199@gmail.com" target="_blank">lviecelli199@gmail.com</a>; <a href="mailto:torres.glauco@gmail.com" target="_blank">torres.glauco@gmail.com</a><br><b>Subject:</b> [postgis-users] Problem with Nested Loop in a query with postgis<u></u><u></u></span></p></div></div><p class="MsoNormal"><u></u> <u></u></p><div><p class="MsoNormal">Hi,<br><br>Here at the company I work for, we upgraded a postgres database from version 10 to 14 and postgis 2.5.3 to 3.3.3 and in a specific query we noticed a huge performance decrease between these versions. This query was running less than 1s and now it takes more than 50s.<br>Now we are trying to figure out what the real problem is and in which version this started to happen. Some of our discoveries so far:<br><br>1) The query runs fine until postgis 3.1.7<br>2) In postgis 3.1.8 the query execution changed from Merge Semi Join to Nested Loop Semi Join.<br>3) Running this query on postgis >= 3.1.8 disabling nested loop with “set enable_nestloop = false” the query runs as before without any problem.<br><br>We don’t know why the plan changed in postgis >= 3.1.8 to use Nested Loop, but even in earlier versions if any query like this uses nested loop, the performance drops.<br><br>Query:<br>SELECT pols.pol_id, pols.type, kml, json_build_object('type', 'Feature', 'geometry', st_asgeojson(pols.geom)::jsonb) as geoJson, pols.status pol_status, pols.owner_id pol_owner, pols.details, pols.creation_date as pol_creation_date, pols.country as country, pols.state as state, pols.city as city, pols.label as label FROM polygons pols WHERE pols.owner_id is null AND pols.status = 'ACTIVE' AND pols.type in ('REGION') AND pols.details->>'state' in (SELECT s.state FROM states s WHERE ST_DWITHIN(Geography(ST_Transform(s.geom,4326)), ST_GeographyFromText('POINT(-43.113826 -22.9022)'),'15000')) AND (ST_DWithin(ST_GeographyFromText('POINT(-43.113826 -22.9022)'), Geography(pols.geom),'15000')) AND (ST_MaxDistance(ST_GeomFromText('POINT(-43.113826 -22.9022)', 4326), pols.geom) * 111195) <= '15000'<br><br>Here are the explains for this query:<br><br>pg 14 - postgis 3.1.7<br> Merge Semi Join (cost=15.83..16.04 rows=1 width=263) (actual time=529.041..586.241 rows=416 loops=1)<br> Output: pols.pol_id, pols.type, pols.kml, json_build_object('type', 'Feature', 'geometry', (st_asgeojson(pols.geom, 9, 8))::jsonb), pols.status, pols.owner_id, pols.details, pols.creation_date, pols.country, pols.state, pols.city, pols.label<br> Merge Cond: (((pols.details ->> 'state'::text)) = (s.state)::text)<br> Buffers: shared hit=3193<br> -> Sort (cost=4.49..4.50 rows=1 width=4656) (actual time=244.333..244.367 rows=416 loops=1)<br> Output: pols.pol_id, pols.type, pols.kml, pols.geom, pols.status, pols.owner_id, pols.details, pols.creation_date, pols.country, pols.state, pols.city, pols.label, ((pols.details ->> 'state'::text))<br> Sort Key: ((pols.details ->> 'state'::text))<br> Sort Method: quicksort Memory: 1314kB<br> Buffers: shared hit=2131<br> -> Index Scan using polygons_geography_state_idx on region.polygons pols (cost=0.28..4.48 rows=1 width=4656) (actual time=10.207..243.079 rows=416 loops=1)<br> Output: pols.pol_id, pols.type, pols.kml, pols.geom, pols.status, pols.owner_id, pols.details, pols.creation_date, pols.country, pols.state, pols.city, pols.label, (pols.details ->> 'state'::text)<br> Index Cond: (geography(pols.geom) && '0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography)<br> Filter: (('0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography && _st_expand(geography(pols.geom), '15000'::double precision)) AND _st_dwithin('0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography, geography(pols.geom), '15000'::double precision, true) AND ((_st_maxdistance('0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geometry, st_convexhull(pols.geom)) * '111195'::double precision) <= '15000'::double precision))<br> Rows Removed by Filter: 395<br> Buffers: shared hit=2125<br> -> Sort (cost=11.34..11.36 rows=9 width=3) (actual time=284.570..284.570 rows=1 loops=1)<br> Output: s.state<br> Sort Key: s.state<br> Sort Method: quicksort Memory: 25kB<br> Buffers: shared hit=1062<br> -> Seq Scan on region.states s (cost=0.00..11.19 rows=9 width=3) (actual time=180.759..284.564 rows=1 loops=1)<br> Output: s.state<br> Filter: st_dwithin(geography(st_transform(s.geom, 4326)), '0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography, '15000'::double precision, true)<br> Rows Removed by Filter: 26<br> Buffers: shared hit=1062<br> Query Identifier: -6178717669455780324<br> Planning:<br> Buffers: shared hit=481<br> Planning Time: 31.139 ms<br> Execution Time: 586.869 ms<br><br>-----------<br><br>pg 14 - postgis 3.1.8<br> Nested Loop Semi Join (cost=0.67..111.50 rows=1 width=263) (actual time=179.622..59918.477 rows=416 loops=1)<br> Output: pols.pol_id, pols.type, pols.kml, json_build_object('type', 'Feature', 'geometry', (st_asgeojson(pols.geom, 9, 8))::jsonb), pols.status, pols.owner_id, pols.details, pols.creation_date, pols.country, pols.state, pols.city, pols.label<br> Join Filter: ((pols.details ->> 'state'::text) = (s.state)::text)<br> Buffers: shared hit=72116 read=1075<br> -> Index Scan using polygons_geography_state_idx on region.polygons pols (cost=0.41..55.77 rows=1 width=4656) (actual time=9.477..221.758 rows=416 loops=1)<br> Output: pols.pol_id, pols.type, pols.kml, pols.geom, pols.status, pols.owner_id, pols.details, pols.creation_date, pols.country, pols.state, pols.city, pols.label, pols.last_updated<br> Index Cond: (geography(pols.geom) && _st_expand('0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography, '15000'::double precision))<br> Filter: (st_dwithin('0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography, geography(pols.geom), '15000'::double precision, true) AND ((_st_maxdistance('0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geometry, st_convexhull(pols.geom)) * '111195'::double precision) <= '15000'::double precision))<br> Rows Removed by Filter: 395<br> Buffers: shared hit=269 read=692<br> -> Index Scan using states_geography_idx on region.states s (cost=0.26..54.37 rows=1 width=3) (actual time=142.676..142.676 rows=1 loops=416)<br> Output: s.state_code, s.state, s.country, s.geom<br> Index Cond: (geography(st_transform(s.geom, 4326)) && _st_expand('0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography, '15000'::double precision))<br> Filter: st_dwithin(geography(st_transform(s.geom, 4326)), '0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography, '15000'::double precision, true)<br> Rows Removed by Filter: 1<br> Buffers: shared hit=70988 read=171<br> Query Identifier: -6178717669455780324<br> Planning:<br> Buffers: shared hit=401 read=45<br> Planning Time: 30.729 ms<br> Execution Time: 59919.343 ms<br><br>Regards,<br>Anderson M.<u></u><u></u></p></div></div></div></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</div></blockquote></div>