<div dir="ltr">Hi,<div><br></div><div>Just pinging this again to see if anyone has an idea of what's going on. From the EXPLAIN output of the second query in the DBFiddle example it does seem that there is a cross-join going on:</div><div><br></div><div><span style="color:rgb(0,0,0);font-family:sans-serif;font-size:13.3333px;white-space:pre"> Rows Removed by Join Filter: 101705643</span><br></div><div><span style="color:rgb(0,0,0);font-family:sans-serif;font-size:13.3333px;white-space:pre"><br></span></div><div><font color="#000000" face="sans-serif"><span style="font-size:13.3333px;white-space:pre">, even though there is an "ON" clause in the join.</span></font></div><div><font color="#000000" face="sans-serif"><span style="font-size:13.3333px;white-space:pre"><br></span></font></div><div><font color="#000000" face="sans-serif"><span style="font-size:13.3333px;white-space:pre">Cam.</span></font></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Nov 9, 2023 at 10:27 AM Cameron McCloud <<a href="mailto:cameron.mccloud@gmail.com">cameron.mccloud@gmail.com</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 dir="ltr"><a href="https://dbfiddle.uk/qJDW-DjP" target="_blank">https://dbfiddle.uk/qJDW-DjP</a><br><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Nov 9, 2023 at 9:11 AM Cameron McCloud <<a href="mailto:cameron.mccloud@gmail.com" target="_blank">cameron.mccloud@gmail.com</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 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" target="_blank">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" target="_blank">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" target="_blank">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" target="_blank">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" target="_blank">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><div lang="EN-US"><div><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>
</blockquote></div>
</blockquote></div>