<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Anderson,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Thanks. That all makes more sense now. <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So when you were running 3.1.7 on PostgreSQL 10 things were still good right?<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>And it was when you ran on PG14<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT postgis_extensions_upgrade();<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Is when your plans went south. That explains a lot.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The main difference between the 3.1 scripts < PG 12 and PostGIS 3.1+ > PG 12 is we changed many of the indexable functions like ST_DWithin to use the new index instrumentation which allowed us to provide more accurate costs for the functions. So the function itself is also different in the costing and its syntax.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Without the SELECT postgis_extensions_upgrade(); <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>You are running with scripts that use the non-instrumentation versions of the function definitions and have no meaningful costs on those functions to allow the indexes to be used.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Hopefully it’s just the costs at fault here.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>For PG < 12, The HIGH COST was set to 10 and for 12 and above was set to 10000<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Legend<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>#if POSTGIS_PGSQL_VERSION >= 120<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>#define _COST_DEFAULT COST 1<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>#define _COST_LOW COST 50<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>#define _COST_MEDIUM COST 500<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>#define _COST_HIGH COST 10000<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>#else<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>#define _COST_DEFAULT COST 1<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>#define _COST_LOW COST 1<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>#define _COST_MEDIUM COST 10<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>#define _COST_HIGH COST 10<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>#endif<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I would try switching the cost on your ST_Transform function and ST_DWithin geography functions back to 10 and see if that sets things back.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Something like below, but change the schema public to the schema you currently have PostGIS installed<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>CREATE OR REPLACE FUNCTION public.st_transform(<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> geometry,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> integer)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> RETURNS geometry <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> LANGUAGE 'c'<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> COST 10<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> IMMUTABLE STRICT PARALLEL SAFE <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>AS '$libdir/postgis-3', 'transform';<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>CREATE OR REPLACE FUNCTION public.st_dwithin(<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> geog1 geography,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> geog2 geography,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> tolerance double precision,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> use_spheroid boolean DEFAULT true)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> RETURNS boolean<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> LANGUAGE 'c'<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> COST 5000<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> IMMUTABLE STRICT PARALLEL SAFE SUPPORT public.postgis_index_supportfn<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>AS '$libdir/postgis-3', 'geography_dwithin';<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>If that works, try setting them to 5000 and see if that still works.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>As a general rule, I try to avoid nesting ST_Transform calls in indexable functions.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I think we did have this issue reported, and in 3.4, we lowered the costing of High cost to 5000.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Instigated by this observation <a href="https://lists.osgeo.org/pipermail/postgis-devel/2022-September/029818.html">https://lists.osgeo.org/pipermail/postgis-devel/2022-September/029818.html</a><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Which appears to be similar to what you are suffering.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I think we didn’t backport the change, cause we weren’t confident in our new costing.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Hope that helps,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Anderson Mallmann<br><b>Sent:</b> Monday, July 24, 2023 10:33 PM<br><b>To:</b> PostGIS Users Discussion <postgis-users@lists.osgeo.org><br><b>Subject:</b> Re: [postgis-users] Problem with Nested Loop in a query with postgis<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>Hi<br>I restored the backup from pg10 2.5.3 and updated postis to 3.1.7. After that I upgrade postgres to pg14.<o:p></o:p></p><div><p class=MsoNormal style='margin-bottom:12.0pt'>I think that in the first message plan, we forgot to run postgis_extensions_upgrade in pg14 3.1.7, because now I tested the query before and after the postgis upgrade, and the plan changed. That explains the difference from 3.1.7 and 3.1.8.<br><br>-------------------------------------------------------------------<br>postgis_full_version | POSTGIS="2.5.3 r17699" [EXTENSION] PGSQL="100" GEOS="3.8.1-CAPI-1.13.3" PROJ="Rel. 7.0.1, May 1st, 2020" GDAL="GDAL 3.0.4, released 2020/01/28 GDAL_DATA not found" LIBXML="2.9.1" LIBJSON="0.11" LIBPROTOBUF="1.0.2" RASTER<br>version | PostgreSQL 10.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit<br><br> Nested Loop (cost=4.92..12.04 rows=1 width=263) (actual time=190.398..459.920 rows=416 loops=1)<br> Output: pols.pol_id, pols.type, pols.kml, json_build_object('type', 'Feature', 'geometry', (st_asgeojson(pols.geom, 15, 0))::jsonb), pols.status, pols.owner_id, pols.details, pols.creation_date, pols.country, pols.state, pols.city, pols.label<br> Buffers: shared hit=1340<br> -> Unique (cost=4.64..4.65 rows=1 width=3) (actual time=189.532..189.535 rows=1 loops=1)<br> Output: s.state, ((s.state)::text)<br> Buffers: shared hit=478<br> -> Sort (cost=4.64..4.64 rows=1 width=3) (actual time=189.531..189.533 rows=1 loops=1)<br> Output: s.state, ((s.state)::text)<br> Sort Key: ((s.state)::text)<br> Sort Method: quicksort Memory: 25kB<br> Buffers: shared hit=478<br> -> Index Scan using states_geography_idx on region.states s (cost=0.14..4.63 rows=1 width=3) (actual time=188.775..189.492 rows=1 loops=1)<br> Output: s.state, s.state<br> Index Cond: (geography(st_transform(s.geom, 4326)) && '0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography)<br> Filter: (('0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography && _st_expand(geography(st_transform(s.geom, 4326)), '15000'::double precision)) AND _st_dwithin(geography(st_transform(s.geom, 4326)), '0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography, '15000'::double precision, true))<br> Rows Removed by Filter: 1<br> Buffers: shared hit=475<br> -> Index Scan using polygons_geography_state_idx on region.polygons pols (cost=0.28..4.78 rows=1 width=4835) (actual time=0.631..141.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) && '0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography) AND ((pols.details ->> 'state'::text) = (s.state)::text))<br> Filter: (((_st_maxdistance('0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geometry, st_convexhull(pols.geom)) * '111195'::double precision) <= '15000'::double precision) AND ('0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography && _st_expand(geography(pols.geom), '15000'::double precision)) AND _st_dwithin('0101000020E61000009EB7B1D9918E45C082734694F6E636C0'::geography, geography(pols.geom), '15000'::double precision, true))<br> Rows Removed by Filter: 395<br> Buffers: shared hit=862<br> Planning time: 27.886 ms<br> Execution time: 460.116 ms<br><br><br>-------------------------------------------------------------------<br>postgis_full_version | POSTGIS="3.1.7 aafe1ff" [EXTENSION] PGSQL="100" GEOS="3.9.2-CAPI-1.14.3" PROJ="7.2.1" GDAL="GDAL 3.3.3, released 2021/10/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER<br>version | PostgreSQL 10.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit<br><br> Merge Semi Join (cost=15.83..16.04 rows=1 width=263) (actual time=601.348..652.897 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=1255 read=1881<br> -> Sort (cost=4.49..4.50 rows=1 width=4835) (actual time=328.831..328.878 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=1168 read=909<br> -> Index Scan using polygons_geography_state_idx on region.polygons pols (cost=0.28..4.48 rows=1 width=4835) (actual time=9.944..328.144 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=1162 read=909<br> -> Sort (cost=11.34..11.36 rows=9 width=3) (actual time=272.386..272.386 rows=1 loops=1)<br> Output: s.state<br> Sort Key: s.state<br> Sort Method: quicksort Memory: 25kB<br> Buffers: shared hit=87 read=972<br> -> Seq Scan on region.states s (cost=0.00..11.19 rows=9 width=3) (actual time=173.108..272.375 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=87 read=972<br> Planning time: 2.386 ms<br> Execution time: 653.027 ms<br><br><br>*before run postgis_extensions_upgrade<br>-------------------------------------------------------------------<br>postgis_full_version | POSTGIS="3.1.7 aafe1ff" [EXTENSION] PGSQL="100" (procs need upgrade for use with PostgreSQL "140") GEOS="3.9.2-CAPI-1.14.3" PROJ="7.2.1" GDAL="GDAL 3.3.3, released 2021/10/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER<br>version | PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit<br><br> Merge Semi Join (cost=287.90..289.79 rows=1 width=328) (actual time=511.157..565.637 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=1275 read=1873 dirtied=1<br> -> Sort (cost=4.49..4.50 rows=1 width=328) (actual time=236.170..236.214 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=1184 read=901 dirtied=1<br> -> Index Scan using polygons_geography_state_idx on region.polygons pols (cost=0.29..4.48 rows=1 width=328) (actual time=9.117..234.960 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=1178 read=901 dirtied=1<br> -> Sort (cost=283.41..283.99 rows=233 width=12) (actual time=274.853..274.853 rows=1 loops=1)<br> Output: s.state<br> Sort Key: s.state<br> Sort Method: quicksort Memory: 25kB<br> Buffers: shared hit=91 read=972<br> -> Seq Scan on region.states s (cost=0.00..274.25 rows=233 width=12) (actual time=174.226..274.842 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=91 read=972<br> Query Identifier: -3917083104069499193<br> Planning:<br> Buffers: shared hit=441 read=27<br> Planning Time: 2.431 ms<br> Execution Time: 566.361 ms<br><br><br>*after run postgis_extensions_upgrade<br>-------------------------------------------------------------------<br>postgis_full_version | POSTGIS="3.1.7 aafe1ff" [EXTENSION] PGSQL="140" GEOS="3.9.2-CAPI-1.14.3" PROJ="7.2.1" GDAL="GDAL 3.3.3, released 2021/10/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER<br>version | PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit<br><br> Nested Loop Semi Join (cost=0.68..111.51 rows=1 width=328) (actual time=155.732..55359.680 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=73020<br> -> Index Scan using polygons_geography_state_idx on region.polygons pols (cost=0.41..55.77 rows=1 width=328) (actual time=0.158..195.166 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=836<br> -> Index Scan using states_geography_idx on region.states s (cost=0.27..54.38 rows=1 width=12) (actual time=132.415..132.415 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=71136<br> Query Identifier: -3917083104069499193<br> Planning Time: 0.360 ms<br> Execution Time: 55360.033 ms<o:p></o:p></p><div><p class=MsoNormal>Thanks,<br>Anderson<o:p></o:p></p></div></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>Em sex., 21 de jul. de 2023 às 01:37, Regina Obe <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> escreveu:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Was this upgrading pg10 from 2.5.3 to 3.0.7?</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I’m more interested in seeing this run in newer PostgreSQL from 12 on.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>There was a lot of stuff that changed in PostgreSQL 12 that affects planner behavior.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Any chance you can recompare your PG 14 3.1.7 vs. 3.1.8? </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The change detailed between 3.1.7 and 3.1.8 did not make any sense to us as nothing in the change log between those two versions would account for the issue you are describing here.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Or was that a typo and the issue was between 3.0.7 and 3.1.8? Planner changes between 3.0.7 and 3.1.8 is more likely.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>As mentioned, it would really help if when you state your versions, you provide output of</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT postgis_full_version(), version();</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Even micro differences between PostgreSQL versions have on rare occasions affected planner behavior too.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Thanks,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;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> Thursday, July 20, 2023 5:13 PM<br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> Re: [postgis-users] Problem with Nested Loop in a query with postgis</span><o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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" target="_blank">https://github.com/aamallmann/postgis-region-db</a> if you want to test.<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I looked at our list of items between 3.1.7 and 3.1.8 </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><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></span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>and can’t find any that would cause what you describe below.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>It’s possible whatever was changed we didn’t consider strong enough to put in the news.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I haven’t checked the git repo yet to see what changed.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I was thinking the 10 to 14 switch might have been caused by the function instrumentation changes.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>But that wouldn’t explain your experiencing the same issue on 14 3.1.7 vs 14 3.1.8</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>If you still have both running, can you give us the output </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT postgis_full_version(), version();</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So we can confirm we are truly comparing only a difference in PostGIS version and not the libraries used or PostgreSQL minor version.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Thanks,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;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</span><o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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.<o:p></o:p></p></div></div></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>_______________________________________________<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" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p></div></blockquote></div></div></div></div><p class=MsoNormal>_______________________________________________<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" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p></div></blockquote></div></div></div></body></html>