<div dir="ltr"><div>Here's the EXPLAIN for the second query with the join and WHERE on ST_DWithin:</div><div><br></div><div> Nested Loop  (cost=0.00..403093.00 rows=2 width=20)<br>   Join Filter: (<a href="http://b.id">b.id</a> = c.building_id)<br>   ->  Seq Scan on customer c  (cost=0.00..231.00 rows=16000 width=4)<br>   ->  Materialize  (cost=0.00..402382.01 rows=2 width=20)<br>         ->  Seq Scan on building b  (cost=0.00..402382.00 rows=2 width=20)<br>               Filter: st_dwithin((st_makepoint((longitude)::double precision, (latitude)::double precision))::geography, '0101000020E61000008AE6012CF23158C065A9F57EA39F4040'::geography, '50000'::double precision, true)<br> JIT:<br>   Functions: 7<br>   Options: Inlining false, Optimization false, Expressions true, Deforming true<br></div><div><br></div><div>Here's the EXPLAIN ANALYZE:</div><div> Nested Loop  (cost=0.00..403093.00 rows=2 width=20) (actual time=29.391..5196.713 rows=6453 loops=1)<br>   Join Filter: (<a href="http://b.id">b.id</a> = c.building_id)<br>   Rows Removed by Join Filter: 82424169<br>   ->  Seq Scan on customer c  (cost=0.00..231.00 rows=16000 width=4) (actual time=0.004..2.484 rows=16000 loops=1)<br>   ->  Materialize  (cost=0.00..402382.01 rows=2 width=20) (actual time=0.000..0.149 rows=5152 loops=16000)<br>         ->  Seq Scan on building b  (cost=0.00..402382.00 rows=2 width=20) (actual time=3.292..28.549 rows=6453 loops=1)<br>               Filter: st_dwithin((st_makepoint((longitude)::double precision, (latitude)::double precision))::geography, '0101000020E61000008AE6012CF23158C065A9F57EA39F4040'::geography, '50000'::double precision, true)<br>               Rows Removed by Filter: 9547<br> Planning Time: 0.182 ms<br> JIT:<br>   Functions: 7<br>   Options: Inlining false, Optimization false, Expressions true, Deforming true<br>   Timing: Generation 0.256 ms, Inlining 0.000 ms, Optimization 0.158 ms, Emission 3.133 ms, Total 3.547 ms<br> Execution Time: 5197.586 ms<br></div><div><br></div><div>Here's the EXPLAIN for the 3rd query (JOIN but convert ST_DWithin to integer)</div><div><br></div><div> Hash Join  (cost=431.00..402854.10 rows=80 width=20)<br>   Hash Cond: (<a href="http://b.id">b.id</a> = c.building_id)<br>   ->  Seq Scan on building b  (cost=0.00..402422.00 rows=80 width=20)<br>         Filter: (CASE st_dwithin((st_makepoint((longitude)::double precision, (latitude)::double precision))::geography, '0101000020E61000008AE6012CF23158C065A9F57EA39F4040'::geography, '50000'::double precision, true) WHEN CASE_TEST_EXPR THEN 1 ELSE 0 END = 1)<br>   ->  Hash  (cost=231.00..231.00 rows=16000 width=4)<br>         ->  Seq Scan on customer c  (cost=0.00..231.00 rows=16000 width=4)<br> JIT:<br>   Functions: 12<br>   Options: Inlining false, Optimization false, Expressions true, Deforming true<br></div><div><br></div><div>And the EXPLAIN ANALYZE:</div><div><br></div><div> Hash Join  (cost=431.00..402854.10 rows=80 width=20) (actual time=6.470..33.691 rows=6453 loops=1)<br>   Hash Cond: (<a href="http://b.id">b.id</a> = c.building_id)<br>   ->  Seq Scan on building b  (cost=0.00..402422.00 rows=80 width=20) (actual time=4.280..30.210 rows=6453 loops=1)<br>         Filter: (CASE st_dwithin((st_makepoint((longitude)::double precision, (latitude)::double precision))::geography, '0101000020E61000008AE6012CF23158C065A9F57EA39F4040'::geography, '50000'::double precision, true) WHEN CASE_TEST_EXPR THEN 1 ELSE 0 END = 1)<br>         Rows Removed by Filter: 9547<br>   ->  Hash  (cost=231.00..231.00 rows=16000 width=4) (actual time=2.173..2.174 rows=16000 loops=1)<br>         Buckets: 16384  Batches: 1  Memory Usage: 691kB<br>         ->  Seq Scan on customer c  (cost=0.00..231.00 rows=16000 width=4) (actual time=0.007..0.931 rows=16000 loops=1)<br> Planning Time: 0.132 ms<br> JIT:<br>   Functions: 12<br>   Options: Inlining false, Optimization false, Expressions true, Deforming true<br>   Timing: Generation 0.311 ms, Inlining 0.000 ms, Optimization 0.184 ms, Emission 4.085 ms, Total 4.580 ms<br> Execution Time: 34.239 ms<br></div><div><br></div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Nov 8, 2023 at 6:10 PM Regina Obe <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> wrote:<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 class="msg-888197744476204129"><div lang="EN-US" style="overflow-wrap: break-word;"><div class="m_-888197744476204129WordSection1"><p class="MsoNormal">First of all is that really your join clause?  You seem to be missing an ON<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">from building b<u></u><u></u></p><p class="MsoNormal">join customer c<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">Also please output<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">EXPLAIN <u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">And the <u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">EXPLAIN ANALYZE of each query<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></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>From:</b> postgis-users <<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>> <b>On Behalf Of </b>Cameron McCloud via postgis-users<br><b>Sent:</b> Wednesday, November 8, 2023 5:38 AM<br><b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><b>Cc:</b> Cameron McCloud <<a href="mailto:cameron.mccloud@gmail.com" target="_blank">cameron.mccloud@gmail.com</a>><br><b>Subject:</b> Re: [postgis-users] ST_DWithin slow if query is joined to another table, but fast if ST_DWithin cast to an integer<u></u><u></u></p></div></div><p class="MsoNormal"><u></u> <u></u></p><div><p class="MsoNormal">Test code is here: <a href="https://github.com/cameronmccloud/postgis_slow_st_dwithin/blob/main/test.sql" target="_blank">https://github.com/cameronmccloud/postgis_slow_st_dwithin/blob/main/test.sql</a><u></u><u></u></p></div><p class="MsoNormal"><u></u> <u></u></p><div><div><p class="MsoNormal">On Wed, Nov 8, 2023 at 9:25 AM Cameron McCloud <<a href="mailto:cameron.mccloud@gmail.com" target="_blank">cameron.mccloud@gmail.com</a>> wrote:<u></u><u></u></p></div><blockquote style="border-top:none;border-right:none;border-bottom:none;border-left:1pt solid rgb(204,204,204);padding:0in 0in 0in 6pt;margin-left:4.8pt;margin-right:0in"><div><p class="MsoNormal">Hi,<u></u><u></u></p><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Postgis Version: 3.3.4<u></u><u></u></p></div><div><p class="MsoNormal">Postgres Version: 14.9<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">We have 2 tables, "building" with lat/long and "customer" with a FK to building. There's a 1:1 relationship between the two.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">The test tables we're using have 16K rows each. Our production data has a lot more, but we could reproduce this on a smaller dataset.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">We found some odd behaviour when using ST_DWITHIN in a WHERE clause but only when the "building" table is joined to the "customer" table.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">We also found that converting the result of ST_DWITHIN to an integer (1/0 for true/false) and using the integer in the WHERE is fast.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">-- This query works as expected and takes 60ms returning 6K rows.<u></u><u></u></p></div><div><p class="MsoNormal">select b.*<u></u><u></u></p></div><div><p class="MsoNormal">from building b<u></u><u></u></p></div><div><p class="MsoNormal">where<u></u><u></u></p></div><div><p class="MsoNormal">  st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000);<u></u><u></u></p></div><div><p class="MsoNormal">         <u></u><u></u></p></div><div><p class="MsoNormal">-- This query is orders of magnitude slower - 3000ms, even though joining the two tables without the WHERE takes 30ms<u></u><u></u></p></div><div><p class="MsoNormal">select b.*<u></u><u></u></p></div><div><p class="MsoNormal">from building b<u></u><u></u></p></div><div><p class="MsoNormal">join customer c<u></u><u></u></p></div><div><div><p class="MsoNormal">where<u></u><u></u></p></div><div><p class="MsoNormal">  st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000);<u></u><u></u></p></div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">-- This query converts the result of ST_DWITHIN to an integer. It's fast and takes 80ms<u></u><u></u></p></div><div><div><p class="MsoNormal">select b.*<u></u><u></u></p></div><div><p class="MsoNormal">from building b<u></u><u></u></p></div><div><p class="MsoNormal">join customer c<u></u><u></u></p></div><div><div><p class="MsoNormal">where<u></u><u></u></p></div><div><p class="MsoNormal">  case st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000)<u></u><u></u></p></div></div><div><p class="MsoNormal">    when true then 1<u></u><u></u></p></div><div><p class="MsoNormal">    else 0<u></u><u></u></p></div><div><p class="MsoNormal">  end = 1;<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">We have no idea why this is the case, but we're curious. In our production scenario using the "case...when" brought a query down from 6 minutes to 6 seconds. We'd love to know why this might be and if there are other scenarios like this that could increase the performance of our Postgis queries.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Thanks,<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Cam.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><p class="MsoNormal"><u></u> <u></u></p></div></div></blockquote></div></div></div></div></div></blockquote></div>