<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
The second note on the documentation page you referenced: "Index
only kicks in if one of the geometries is a constant (not in a
subquery/cte). e.g. 'SRID=3005;POINT(1011102 450541)'::geometry
instead of a.geom". Neither value passed to "<->" in your
query is a constant.<br>
<br>
<div class="moz-cite-prefix">On 6/22/22 09:22, Lars Aksel Opsahl
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:AM7P189MB10289C2FED1F2FEFC1B495E39DB29@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<style type="text/css" style="display:none;">P {margin-top:0;margin-bottom:0;}</style>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Hi</span></div>
<div><br>
</div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">I have two simple tables.</span></div>
<div><br>
</div>
<div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Table</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">"pg_temp_30.g1temp"</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">Column</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> |</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Type</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">|
Collation |
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Nullable</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> |</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">Default</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> </span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">--------+------------------------+-----------+----------+------------------------------------</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">geo</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">| geometry(</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">Polygon</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">,4258) | </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">|</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">|</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> </span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">id
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">|
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">integer</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">|
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">|
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">not</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">null</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> |
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">nextval</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">(</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">'g1temp_id_seq'</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">::</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">regclass</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">)</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Indexes</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">:</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">"g1temp_pkey"</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">PRIMARY</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">KEY</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">,
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">btree</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> (id)</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">"g1temp_geo_idx"</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">gist</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> (</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">geo</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">)</span></div>
<div><br>
</div>
<div><br>
</div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Table</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">"pg_temp_30.g2temp"</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">Column</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> |</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Type</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">|
Collation |
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Nullable</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> |</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">Default</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> </span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">--------+------------------------+-----------+----------+------------------------------------</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">geo</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">| geometry(</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">Polygon</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">,4258) | </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">|</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">|</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> </span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">id
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">|
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">integer</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">|
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">|
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">not</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">null</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> |
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">nextval</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">(</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">'g2temp_id_seq'</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">::</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">regclass</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">)</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Indexes</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">:</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">"g2temp_pkey"</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">PRIMARY</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">KEY</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">,
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">btree</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> (id)</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">"g2temp_geo_idx"</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">gist</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> (</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">geo</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">)</span></div>
<br>
</div>
<div><br>
</div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">And I need
to find the polygon in g2temp that has the longest distance to
any polygon in d1temp. </span></div>
<div class="elementToProof"><span style="margin:0px"><span
style="margin:0px;font-size:12pt;font-family:Calibri, Arial,
Helvetica, sans-serif">g1temp has </span><span
style="margin:0px;font-size:12pt;font-family:Calibri, Arial,
Helvetica, sans-serif">542 rows and table g2temp has </span><span
style="margin:0px;font-size:12pt;font-family:Calibri, Arial,
Helvetica, sans-serif">63483 rows. All polygons are simple
rectangles. </span></span><span style="margin:0px"></span><br>
</div>
<div class="elementToProof"><span style="margin:0px"><span
style="margin:0px;font-size:12pt;font-family:Calibri, Arial,
Helvetica, sans-serif"><br>
</span></span></div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">I run analyze </span></div>
<div class="elementToProof"><span style="color: rgb(0, 0, 0);
font-size: 12pt; font-family: Calibri, Arial, Helvetica,
sans-serif;">analyze</span><span style="color: rgb(0, 0, 0);
font-size: 12pt; font-family: Calibri, Arial, Helvetica,
sans-serif;"> g1temp;</span><br>
</div>
<div class="elementToProof">
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">analyze</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> g2temp;</span></div>
<br>
</div>
<div class="elementToProof"><br>
</div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">And then run this query</span></div>
<div><br>
</div>
<div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">EXPLAIN</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">ANALYZE</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> </span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">WITH</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> index_query
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">AS</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> (</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"></span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">SELECT</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> g2.</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">geo</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> , ST_Distance(g1.</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">geo</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">, g2.</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">geo</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">,</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">true</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">)
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">AS</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> d, 1
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">as</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> runnning_jos</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"></span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">FROM</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> </span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"></span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">g1temp
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">AS</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> g1,</span></div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);"></span><span style="font-family: Calibri, Arial,
Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0,
0);">g2temp
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">AS</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> g2</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"></span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">ORDER</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">BY</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> g1.</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">geo</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> <-> g2.</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">geo</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">desc</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"></span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">LIMIT</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> 1</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">)</span></div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">SELECT</span><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);"> *</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">FROM</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);"> index_query;</span></div>
<br>
</div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">And I get this </span></div>
<div><br>
</div>
<div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">QUERY PLAN</span><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">
</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">-----------------------------------------------------------------------------------------------------------------------------------------------</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">Subquery Scan on
index_query</span><span style="font-family: Calibri, Arial,
Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0,
0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">(cost=43613127.36..43613153.64
rows=1 width=132) (actual time=69029.773..69029.775 rows=1
loops=1)</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">-></span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Limit</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">(cost=43613127.36..43613153.63
rows=1 width=140) (actual time=69029.771..69029.772 rows=1
loops=1)</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">-></span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Result</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">(cost=43613127.36..947419646.12
rows=34407786 width=140) (actual time=69029.769..69029.770
rows=1 loops=1)</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">-></span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Sort</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">(cost=43613127.36..43699146.83
rows=34407786 width=292) (actual time=69019.575..69019.576
rows=1 loops=1)</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Sort Key:
((g1.geo <-> g2.geo)) DESC</span></div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Sort
Method: top-N heapsort</span><span style="font-family:
Calibri, Arial, Helvetica, sans-serif; font-size: 12pt;
color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Memory:
29kB</span></div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">-></span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Nested
Loop</span><span style="font-family: Calibri, Arial,
Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0,
0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">(cost=0.00..43441088.43
rows=34407786 width=292) (actual time=0.184..58446.116
rows=34407786 loops=1)</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">-></span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Seq Scan
on g2temp g2</span><span style="font-family: Calibri, Arial,
Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0,
0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">(cost=0.00..1245.33
rows=63483 width=120) (actual time=0.020..31.744 rows=63483
loops=1)</span></div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">-></span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Materialize</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">(cost=0.00..14.63
rows=542 width=160) (actual time=0.000..0.030 rows=542
loops=63483)</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">-></span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Seq Scan
on g1temp g1</span><span style="font-family: Calibri, Arial,
Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0,
0);">
</span><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">(cost=0.00..11.92
rows=542 width=160) (actual time=0.014..0.202 rows=542
loops=1)</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">Planning Time: 0.320
ms</span></div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">Execution Time:
69029.872 ms</span></div>
</div>
<div>(12 rows)<span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"></span></div>
<div><br>
</div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">I do understand that this may take time, but </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">I wonder why I see this
"Seq scan" both on g1 and g2 ?</span></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
From what I read on <a
href="https://postgis.net/docs/geometry_distance_knn.html"
id="LPNoLPOWALinkPreview" moz-do-not-send="true"
class="moz-txt-link-freetext">https://postgis.net/docs/geometry_distance_knn.html</a> it
should use indexes in some way.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
<br>
</div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">Have also tested with casting </span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">geogra</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">phy when creating table
g1 and g2 and that did not make any differen</span><span
style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">ce.</span></div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);"><br>
</span></div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">Tested also with big workmem (1000 MB ) and the result
was the same.</span></div>
<div class="elementToProof"><br>
</div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">I am running on </span><span style="font-family:
Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color:
rgb(0, 0, 0);">POSTGIS="3.3.0dev 3.1.0alpha2-1532-gc8eedf3ae"
[EXTENSION] PGSQL="120" GEOS="3.9.0-CAPI-1.16.2" PROJ="7.2.1"
LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3"
WAGYU="0.5.0 (Internal)" TOPOLOGY</span></div>
<div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">(1 row)</span></div>
</div>
<div><br>
</div>
<div><span style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Thanks.</span><br>
</div>
<div><br>
</div>
<div class="elementToProof"><span style="font-family: Calibri,
Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0,
0, 0);">Lars</span></div>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</body>
</html>