<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;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@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 style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal>I didn’t realize you are building the geography from longitude / latitude of your building.<o:p></o:p></p><p class=MsoNormal>Is there a reason you don’t have a geography column in your building table, instead of building it from scratch each time?<o:p></o:p></p><p class=MsoNormal>Right now you query can’t use a spatial index at all.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>If you had the geography in your building table:<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>ALTER TABLE building ADD geog geography(POINT, 4326);<o:p></o:p></p><p class=MsoNormal>UPDATE building SET geog = ST_Point(longitude, latitude)::geography;<o:p></o:p></p><p class=MsoNormal>CREATE INDEX ix_building_geog ON building USING gist(geog);<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>SELECT c.*<o:p></o:p></p><p class=MsoNormal>FROM buildings AS b INNER JOIN c ON b.id = c.building_id<o:p></o:p></p><p class=MsoNormal>WHERE ST_DWithin(b.geog, <span style='font-family:"Courier New"'>st_makepoint(-96.7804060, 33.2471770)::geography, 50000);<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Courier New"'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Courier New"'>Then that should use a spatial index and be much faster</span><o:p></o:p></p><p class=MsoNormal><span style='font-family:"Courier New"'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Courier New"'>Now why one of your queries is running at 5000 ms vs. 35 ms, since I can’t see the TEST EXPRESSIOn you are using, I’m not sure if those are equivalent. I assume the test expression is <b>true.<o:p></o:p></b></span></p><p class=MsoNormal><b><span style='font-family:"Courier New"'><o:p> </o:p></span></b></p><p class=MsoNormal>If for some reason, you can’t have a geography column in your building table, you could try to force the planners behavior with a subselect AS<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>FROM (SELECT *, ST_MakePoint(longitude, latitude) AS geog FROM building ) AS b INNER JOIN customer AS c ON b.id = c.building_id<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Also make sure you have a primary key on your building.id column and an index on your customer.building_id. Your plans don’t appear to be using a building_id index either, which might be the correct thing to do, or could be cause you don’t have an index on those columns.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><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><b>From:</b> Cameron McCloud <cameron.mccloud@gmail.com> <br><b>Sent:</b> Tuesday, November 28, 2023 9:45 AM<br><b>To:</b> Regina Obe <lr@pcorp.us><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<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:black'>This is what I get from that query:</span><o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:black'>IMMUTABLE PARALLEL SAFE STRICT COST 10000 SUPPORT postgis_index_supportfn</span><o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:black'>Putting the ST_DWithin into the join gives the same result.</span><o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>On Mon, Nov 27, 2023 at 5:15 PM Regina Obe <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> wrote:<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'>I’m guessing it has to do with the costing we have on ST_DWithin.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I recall we increased that back recently because of some complaints but can’t recall the version.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>What does your costing read from ST_DWithin(geography, geography, …) read?<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Run this query to see the code:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>SELECT pg_get_functiondef(oid)<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>FROM pg_proc<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>WHERE proname = 'st_dwithin' AND prosrc = 'geography_dwithin';<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Mine reads<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> COST <b>5000</b><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> IMMUTABLE STRICT PARALLEL SAFE SUPPORT postgis_index_supportf<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Also what happens if you switch your query to put the ST_Dwithin in the JOIN clause instead of the WHERE?<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Courier New"'>from building b join customer c ON (<a href="http://b.id" target="_blank">b.id</a> = c.building_id AND st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000) );</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <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>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> Monday, November 27, 2023 8:32 AM<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>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<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,<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'>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:<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:black'> Rows Removed by Join Filter: 101705643</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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:black'>, even though there is an "ON" clause in the join.</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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:black'>Cam.</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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>On Thu, Nov 9, 2023 at 10:27 AM Cameron McCloud <<a href="mailto:cameron.mccloud@gmail.com" target="_blank">cameron.mccloud@gmail.com</a>> wrote:<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><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><a href="https://dbfiddle.uk/qJDW-DjP" target="_blank">https://dbfiddle.uk/qJDW-DjP</a><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'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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:<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><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Here's the EXPLAIN for the second query with the join and WHERE on ST_DWithin:<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> 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<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Here's the EXPLAIN ANALYZE:<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> 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<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Here's the EXPLAIN for the 3rd query (JOIN but convert ST_DWithin to integer)<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> 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<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>And the EXPLAIN ANALYZE:<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> 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<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><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'> <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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>On Wed, Nov 8, 2023 at 6:10 PM Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<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'>First of all is that really your join clause? You seem to be missing an ON<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>from building b<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>join customer c<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Also please output<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>EXPLAIN <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>And the <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>EXPLAIN ANALYZE of each query<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <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>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<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'>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><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'>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:<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><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hi,<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'>Postgis Version: 3.3.4<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Postgres Version: 14.9<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>We have 2 tables, "building" with lat/long and "customer" with a FK to building. There's a 1:1 relationship between the two.<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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.<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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.<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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.<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>-- This query works as expected and takes 60ms returning 6K rows.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>select b.*<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>from building b<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>where<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000);<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>-- This query is orders of magnitude slower - 3000ms, even though joining the two tables without the WHERE takes 30ms<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>select b.*<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>from building b<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>join customer c<o:p></o:p></p></div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>where<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000);<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'>-- This query converts the result of ST_DWITHIN to an integer. It's fast and takes 80ms<o:p></o:p></p></div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>select b.*<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>from building b<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>join customer c<o:p></o:p></p></div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>where<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> case st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000)<o:p></o:p></p></div></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> when true then 1<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> else 0<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> end = 1;<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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.<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thanks,<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Cam.<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><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'> <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></blockquote></div></div></div></div></div></blockquote></div></blockquote></div></blockquote></div></div></div></div></div></blockquote></div></div></div></body></html>