<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
<div style="margin:0px"><span style="margin:0px;font-size:12pt">></span><span style="margin:0px;font-size:12pt">From</span><span style="margin:0px;font-size:12pt">:<span> </span></span><span style="margin:0px;font-size:12pt">postgis</span><span style="margin:0px;font-size:12pt">-users
<postgis-users-bounces</span><span style="margin:0px;font-size:12pt">@lists.osgeo.org</span><span style="margin:0px;font-size:12pt">><span> </span></span><span style="margin:0px;font-size:12pt">on</span><span style="margin:0px;font-size:12pt"><span> </span>behalf<span> </span></span><span style="margin:0px;font-size:12pt">of</span><span style="margin:0px;font-size:12pt"><span> </span></span><span style="margin:0px;font-size:12pt">Ben</span><span style="margin:0px;font-size:12pt"><span> </span></span><span style="margin:0px;font-size:12pt">Madin</span><span style="margin:0px;font-size:12pt"><span> </span><ben</span><span style="margin:0px;font-size:12pt">@ausvet.com.au</span><span style="margin:0px;font-size:12pt">></span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">>Sent: Thursday, June 23, 2022 4:04 AM</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">></span><span style="margin:0px;font-size:12pt">To</span><span style="margin:0px;font-size:12pt">: PostGIS Users Discussion <postgis-users</span><span style="margin:0px;font-size:12pt">@lists.osgeo.org</span><span style="margin:0px;font-size:12pt">></span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">>Subject: Re: [</span><span style="margin:0px;font-size:12pt">postgis</span><span style="margin:0px;font-size:12pt">-users] https://postgis.net/docs/geometry_distance_knn.html<span> </span></span><span style="margin:0px;font-size:12pt">and</span><span style="margin:0px;font-size:12pt"><span> </span></span><span style="margin:0px;font-size:12pt">index</span><span style="margin:0px;font-size:12pt"><span> </span>usage</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">></span><span style="margin:0px;font-size:12pt"> </span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">></span><span style="margin:0px;font-size:12pt">Hi</span><span style="margin:0px;font-size:12pt"><span> </span></span><span style="margin:0px;font-size:12pt">Lars</span><span style="margin:0px;font-size:12pt">,</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">></span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">>I</span><span style="margin:0px;font-size:12pt">'m just a user, but we have handled some pretty big<span> </span></span><span style="margin:0px;font-size:12pt">datasets</span><span style="margin:0px;font-size:12pt"><span> </span>so
I can understand your problem. I might be misunderstanding, but to confirm you are asking the system to cross join all of the possible points (something like 63483 * 4 * 542 * 4), use st_distance to convert them to geography and calculate the distance between
every combination of these points, store this in an<span> </span></span><span style="margin:0px;font-size:12pt">unindexed</span><span style="margin:0px;font-size:12pt"><span> </span>in memory table and and then sort it by a different but as yet<span> </span></span><span style="margin:0px;font-size:12pt">uncalculated</span><span style="margin:0px;font-size:12pt"><span> </span>value
(using the<span> </span></span><span style="margin:0px;font-size:12pt">knn</span><span style="margin:0px;font-size:12pt"><span> </span>operator) so that you can find the largest value?</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">></span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">>I wonder if the search strategy could be refined.</span><span style="margin:0px;font-size:12pt"> </span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">></span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">>a) The Explain output suggests to me that most of the time in the query is calculating all the distances in the join table (and I'</span><span style="margin:0px;font-size:12pt">m guessing calculations).<span> </span></span><span style="margin:0px;font-size:12pt">Having</span><span style="margin:0px;font-size:12pt"><span> </span>done
this, why<span> </span></span><span style="margin:0px;font-size:12pt">then</span><span style="margin:0px;font-size:12pt"><span> </span>invoke the<span> </span></span><span style="margin:0px;font-size:12pt">knn</span><span style="margin:0px;font-size:12pt"><span> </span>approach<span> </span></span><span style="margin:0px;font-size:12pt">to</span><span style="margin:0px;font-size:12pt"><span> </span>the
same points<span> </span></span><span style="margin:0px;font-size:12pt">to</span><span style="margin:0px;font-size:12pt"><span> </span>find the distances<span> </span></span><span style="margin:0px;font-size:12pt">to</span><span style="margin:0px;font-size:12pt"><span> </span>sort,<span> </span></span><span style="margin:0px;font-size:12pt">when</span><span style="margin:0px;font-size:12pt"><span> </span>you
have already calculated this<span> </span></span><span style="margin:0px;font-size:12pt">in</span><span style="margin:0px;font-size:12pt"><span> </span>the<span> </span></span><span style="margin:0px;font-size:12pt">first</span><span style="margin:0px;font-size:12pt"><span> </span>join
operation. It may be more efficient<span> </span></span><span style="margin:0px;font-size:12pt">to</span><span style="margin:0px;font-size:12pt"><span> </span>sort<span> </span></span><span style="margin:0px;font-size:12pt">by</span><span style="margin:0px;font-size:12pt"><span> </span>the
result<span> </span></span><span style="margin:0px;font-size:12pt">of</span><span style="margin:0px;font-size:12pt"><span> </span>the st_distance<span> </span></span><span style="margin:0px;font-size:12pt">function</span><span style="margin:0px;font-size:12pt">.</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">></span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">>b) rather than<span> </span></span><span style="margin:0px;font-size:12pt">force</span><span style="margin:0px;font-size:12pt"><span> </span>the calculation<span> </span></span><span style="margin:0px;font-size:12pt">of</span><span style="margin:0px;font-size:12pt"><span> </span>distance
between<span> </span></span><span style="margin:0px;font-size:12pt">all</span><span style="margin:0px;font-size:12pt"><span> </span>the points geographically, find the<span> </span></span><span style="margin:0px;font-size:12pt">furthest</span><span style="margin:0px;font-size:12pt"><span> </span>apart
polygons<span> </span></span><span style="margin:0px;font-size:12pt">using</span><span style="margin:0px;font-size:12pt"><span> </span>their inherent geometry,<span> </span></span><span style="margin:0px;font-size:12pt">then</span><span style="margin:0px;font-size:12pt"><span> </span>calculated
the exact distance<span> </span></span><span style="margin:0px;font-size:12pt">for</span><span style="margin:0px;font-size:12pt"><span> </span></span><span style="margin:0px;font-size:12pt">only</span><span style="margin:0px;font-size:12pt"><span> </span>the
two points.</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">></span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">>I</span><span style="margin:0px;font-size:12pt">'d play around a bit, but the<span> </span></span><span style="margin:0px;font-size:12pt">srid</span><span style="margin:0px;font-size:12pt"><span> </span>you
are using covers a small area, so differences between using<span> </span></span><span style="margin:0px;font-size:12pt">spheroid</span><span style="margin:0px;font-size:12pt"><span> </span>and not should not create massive errors.</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">></span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">>cheers</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">></span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">></span><span style="margin:0px;font-size:12pt">Ben</span></div>
<div style="margin:0px"><br>
</div>
<div class="x_elementToProof" style="margin:0px;font-size:12pt">Hi</div>
<div class="x_elementToProof" style="margin:0px;font-size:12pt"><br>
</div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">Thanks for comments I played around this more after reading your mail.</span></div>
<div style="margin:0px"><br>
</div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">First here is some more background info.</span></div>
<div style="margin:0px"><br>
</div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">This tables represent work areas and and rows are moved between g2_job_done and g2_job_ready_to_start all the time.</span></div>
<div style="margin:0px"><br>
</div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">We have 40 workers ready to do jobs and they work in parallel , but we want them do work as far part as possible .</span></div>
<div style="margin:0px"><br>
</div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">So the first work is to find a place as far away other jobs done/started as possible to reduce spatial<span> </span></span><span style="margin:0px;font-size:12pt">conflicts</span><span style="margin:0px;font-size:12pt"><span> </span>and
database deadlocks.</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">To find the next work cell on using<span> </span></span><span style="margin:0px;font-size:12pt">Postgres</span><span style="margin:0px;font-size:12pt"><span> </span>advisory
locks to avoid concurrency errors.</span><span style="margin:0px;font-size:12pt"> </span></div>
<div class="x_elementToProof" style="margin:0px"><br>
</div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">Here are the number of rows in base tables now, the 542 is after an ST_union and then doing ST_dump.</span></div>
<div style="margin:0px"><br>
</div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">SELECT count(*) from g2_job_done;</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> </span><span style="margin:0px;font-size:12pt">count</span><span style="margin:0px;font-size:12pt"> </span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">-------</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">2677</span></div>
<div style="margin:0px"><br>
</div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">SELECT count(*) from g2_job_ready_to_start;</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> </span><span style="margin:0px;font-size:12pt">count</span><span style="margin:0px;font-size:12pt"> </span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">-------</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> </span><span style="margin:0px;font-size:12pt">63483</span></div>
<div style="margin:0px"><br>
</div>
<div class="x_elementToProof" style="margin:0px"><br>
</div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">I played around with CROSS JOIN LATERAL and that reduced the time more than 50 %.</span><span style="margin:0px;font-size:12pt"> </span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">The light</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">red cell are jobs ready to start the yellow cells
are done/started </span><span style="margin:0px;font-size:12pt">jobs</span></div>
<div class="x_elementToProof" style="margin:0px"><br>
</div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">In the attached image the SQL below returns the green "diamond".</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt"> </span></div>
<div class="x_elementToProof" style="margin:0px"><br>
</div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">EXPLAIN ANALYZE</span><span style="margin:0px;font-size:12pt"> </span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">SELECT g2.id AS next_id,</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">g2.</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt">::geometry(Polygon,4258) AS
next_geo,</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">g2.</span><span style="margin:0px;font-size:12pt">dist</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">FROM</span><span style="margin:0px;font-size:12pt"> </span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">(SELECT ST_Union(</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt">)::geometry(MultiPolygon,4258) AS<span> </span></span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt"><span> </span>FROM
g2_job_done g1) g1</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">CROSS JOIN LATERAL (</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">SELECT g2.</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt">, g2.id, g1.</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt"><span> </span><->
g2.</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt"><span> </span>AS<span> </span></span><span style="margin:0px;font-size:12pt">dist</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">FROM g2_job_ready_to_start AS g2</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">ORDER BY<span> </span></span><span style="margin:0px;font-size:12pt">dist</span><span style="margin:0px;font-size:12pt"><span> </span></span><span style="margin:0px;font-size:12pt">desc</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">) g2</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">LIMIT 1;</span></div>
<div style="margin:0px"><br>
</div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">QUERY PLAN</span><span style="margin:0px;font-size:12pt">
</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">-------------------------------------------------------------------------------------------------------------------------------------------</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> </span><span style="margin:0px;font-size:12pt">Limit</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">(cost=86075.54..86075.56 rows=1
width=132) (actual time=26985.368..26985.370 rows=1 loops=1)</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">-></span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Nested
Loop</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">(cost=86075.54..87503.93 rows=63483 width=132) (actual time=26985.365..26985.367 rows=1 loops=1)</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">-></span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Aggregate</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">(cost=412.39..412.41
rows=1 width=32) (actual time=232.006..232.007 rows=1 loops=1)</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">-></span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Seq</span><span style="margin:0px;font-size:12pt"><span> </span>Scan
on g2_job_done g1</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">(cost=0.00..52.77 rows=2677 width=120) (actual time=0.018..1.010 rows=2677 loops=1)</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">-></span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Sort</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">(cost=85663.15..85821.85
rows=63483 width=132) (actual time=26753.353..26753.353 rows=1 loops=1)</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Sort Key: ((((st_union(g1.</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt">))::geometry(MultiPolygon,4258))
<-> g2.</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt">)) DESC</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Sort Method:<span> </span></span><span style="margin:0px;font-size:12pt">quicksort</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Memory:
18399kB</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">-></span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Seq</span><span style="margin:0px;font-size:12pt"><span> </span>Scan
on g2_job_ready_to_start g2</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">(cost=0.00..80599.08 rows=63483 width=132) (actual time=0.253..26699.266 rows=63483 loops=1)</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt"> </span><span style="margin:0px;font-size:12pt">Planning Time: 0.729<span> </span></span><span style="margin:0px;font-size:12pt">ms</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> </span><span style="margin:0px;font-size:12pt">Execution Time: 26986.358<span> </span></span><span style="margin:0px;font-size:12pt">ms</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">(10 rows)</span></div>
<div style="margin:0px"><br>
</div>
<div style="margin:0px"><br>
</div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">I have tested with point as you suggested like this,</span></div>
<div style="margin:0px"><br>
</div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">CREATE TABLE g2_job_ready_to_start_p AS SELECT</span><span style="margin:0px;font-size:12pt"> </span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"></span><span style="margin:0px;font-size:12pt">ST_PointOnSurface(g.geo)::geometry(Point,4258) as<span> </span></span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt"> </span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"></span><span style="margin:0px;font-size:12pt">FROM g2_job_ready_to_start g;</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">CREATE INDEX ON g2_job_ready_to_start_p USING<span> </span></span><span style="margin:0px;font-size:12pt">gist</span><span style="margin:0px;font-size:12pt"><span> </span>(</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt">);</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">ALTER TABLE g2_job_ready_to_start_p ADD COLUMN id serial primary key;</span></div>
<div class="x_elementToProof" style="margin:0px"><br>
</div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">EXPLAIN ANALYZE</span><span style="margin:0px;font-size:12pt"> </span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">SELECT g2.id AS next_id,</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">g2.</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt">::geometry(Point,4258) AS
next_geo,</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">g2.</span><span style="margin:0px;font-size:12pt">dist</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">FROM</span><span style="margin:0px;font-size:12pt"> </span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">(SELECT ST_Union(</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt">)::geometry(MultiPolygon,4258) AS<span> </span></span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt"><span> </span>FROM
g2_job_done g1) g1</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">CROSS JOIN LATERAL (</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">SELECT g2.</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt">, g2.id, g1.</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt"><span> </span><->
g2.</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt"><span> </span>AS<span> </span></span><span style="margin:0px;font-size:12pt">dist</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">FROM g2_job_ready_to_start_p AS g2</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">ORDER BY<span> </span></span><span style="margin:0px;font-size:12pt">dist</span><span style="margin:0px;font-size:12pt"><span> </span></span><span style="margin:0px;font-size:12pt">desc</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">) g2</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">LIMIT 1;</span></div>
<div style="margin:0px"><br>
</div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> </span><span style="margin:0px;font-size:12pt">QUERY PLAN</span><span style="margin:0px;font-size:12pt"> </span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">-------------------------------------------------------------------------------------------------------------------------------------------------------</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> </span><span style="margin:0px;font-size:12pt">Limit</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">(cost=85730.04..85730.06 rows=1
width=44) (actual time=10420.754..10420.756 rows=1 loops=1)</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">-></span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Nested
Loop</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">(cost=85730.04..87158.43 rows=63483 width=44) (actual time=10420.751..10420.752 rows=1 loops=1)</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">-></span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Aggregate</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">(cost=412.39..412.41
rows=1 width=32) (actual time=231.318..231.319 rows=1 loops=1)</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">-></span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Seq</span><span style="margin:0px;font-size:12pt"><span> </span>Scan
on g2_job_done g1</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">(cost=0.00..52.77 rows=2677 width=120) (actual time=0.016..0.624 rows=2677 loops=1)</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">-></span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Sort</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">(cost=85317.65..85476.35
rows=63483 width=44) (actual time=10189.429..10189.429 rows=1 loops=1)</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Sort Key: ((((st_union(g1.</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt">))::geometry(MultiPolygon,4258))
<-> g2.</span><span style="margin:0px;font-size:12pt">geo</span><span style="margin:0px;font-size:12pt">)) DESC</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Sort Method:<span> </span></span><span style="margin:0px;font-size:12pt">quicksort</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Memory:
6496kB</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">-></span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">Seq</span><span style="margin:0px;font-size:12pt"><span> </span>Scan
on g2_job_ready_to_start_p g2</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">(cost=0.00..80253.58 rows=63483 width=44) (actual time=0.182..10159.231 rows=63483 loops=1)</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> </span><span style="margin:0px;font-size:12pt">Planning Time: 0.526<span> </span></span><span style="margin:0px;font-size:12pt">ms</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt"> </span><span style="margin:0px;font-size:12pt">Execution Time: 10420.818<span> </span></span><span style="margin:0px;font-size:12pt">ms</span></div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">(10 rows)</span></div>
<div style="margin:0px"><br>
</div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">Then we use the g2_job_ready_to_start_p and that reduced the time down 10 seconds but that cases another problem as you see in the image.</span></div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">In the attached image the above SQL returns the dark red point.</span></div>
<div style="margin:0px"><br>
</div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">Because the cells have different sizes because we use content based grids (smaller<span> </span></span><span style="margin:0px;font-size:12pt">celle</span><span style="margin:0px;font-size:12pt"><span> </span>where
have density of data) so may then get cells that share a border with another cell ready for work.</span></div>
<div style="margin:0px"><br>
</div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">Since indexes only seems work when we need find close cell a not far away cells as I was hoping. If this is expected behavior, we can not use this index straight forward</span><span style="margin:0px;font-size:12pt"> <span> </span></span><span style="margin:0px;font-size:12pt">.</span></div>
<div style="margin:0px"><br>
</div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">In some cases it now take more time find out where to work than actually doing the work :) and I was hoping to speed up this a lot by using a simple index.</span></div>
<div class="x_elementToProof" style="margin:0px"><br>
</div>
<div class="x_elementToProof" style="margin:0px"><span style="margin:0px;font-size:12pt">Thanks</span></div>
<div class="x_elementToProof" style="margin:0px"><br>
</div>
<div style="margin:0px"><span style="margin:0px;font-size:12pt">Lars</span><span style="margin:0px;font-size:12pt"> </span></div>
<div style="margin:0px"><br>
</div>
<span style="margin:0px"></span><br>
</div>
<div class="elementToProof">
<div dir="ltr" class="x_gmail_signature"></div>
</div>
</body>
</html>