<div dir="ltr">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.<br></div>