<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:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@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;}
/* List Definitions */
@list l0
{mso-list-id:1124999365;
mso-list-type:hybrid;
mso-list-template-ids:1561616476 -253432358 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}
@list l0:level1
{mso-level-start-at:0;
mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;
mso-fareast-font-family:Calibri;
mso-bidi-font-family:"Times New Roman";}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></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’m guessing it has to do with the costing we have on ST_DWithin.<o:p></o:p></p><p class=MsoNormal>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><o:p> </o:p></p><p class=MsoNormal>What does your costing read from ST_DWithin(geography, geography, …) read?<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Run this query to see the code:<o:p></o:p></p><p class=MsoNormal>SELECT pg_get_functiondef(oid)<o:p></o:p></p><p class=MsoNormal>FROM pg_proc<o:p></o:p></p><p class=MsoNormal>WHERE proname = 'st_dwithin' AND prosrc = 'geography_dwithin';<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Mine reads<o:p></o:p></p><p class=MsoNormal> COST <b>5000</b><o:p></o:p></p><p class=MsoNormal> IMMUTABLE STRICT PARALLEL SAFE SUPPORT postgis_index_supportf<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>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><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 (b.id = c.building_id AND st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000) );<o:p></o:p></span></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> postgis-users <postgis-users-bounces@lists.osgeo.org> <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 <postgis-users@lists.osgeo.org><br><b>Cc:</b> Cameron McCloud <cameron.mccloud@gmail.com><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><p class=MsoNormal>Hi,<o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>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><o:p> </o:p></p></div><div><p class=MsoNormal><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><o:p> </o:p></p></div><div><p class=MsoNormal><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><o:p> </o:p></p></div><div><p class=MsoNormal><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><o:p> </o:p></p><div><div><p class=MsoNormal>On Thu, Nov 9, 2023 at 10:27 AM Cameron McCloud <<a href="mailto:cameron.mccloud@gmail.com">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-right:0in'><div><p class=MsoNormal><a href="https://dbfiddle.uk/qJDW-DjP" target="_blank">https://dbfiddle.uk/qJDW-DjP</a><o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>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-right:0in'><div><div><p class=MsoNormal>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><o:p> </o:p></p></div><div><p class=MsoNormal> 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><o:p> </o:p></p></div><div><p class=MsoNormal>Here's the EXPLAIN ANALYZE:<o:p></o:p></p></div><div><p class=MsoNormal> 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><o:p> </o:p></p></div><div><p class=MsoNormal>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><o:p> </o:p></p></div><div><p class=MsoNormal> 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><o:p> </o:p></p></div><div><p class=MsoNormal>And the EXPLAIN ANALYZE:<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal> 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><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>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-right:0in'><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></body></html>