<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Take a look into the execution plans (EXPLAIN ANALYZE), if your index is really used.</div>
<div>In the plan you will see that when using ST_DWithin(a.geom::geography,b.geom,1000), b.geom should be casted to geography as well, so a simple GIST index on the geom column won't be used.</div>
<div>Therefore, having a functional index with ::geography should solve it.</div>
<div>What happens if you narrow down your query to one gid on one side? Is the index used then?</div>
<div>I'd suggest you find the optimal query for a single gid, then try a few more, then more, then all etc.</div>
<div> </div>
<div>
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Gesendet:</b> Donnerstag, 17. Januar 2019 um 10:11 Uhr<br/>
<b>Von:</b> "David M. Kaplan" <david.kaplan@ird.fr><br/>
<b>An:</b> postgis-users@lists.osgeo.org<br/>
<b>Betreff:</b> Re: [postgis-users] postgis-users Digest, Vol 203, Issue 11</div>
<div name="quoted-content">
<div style="background-color: rgb(255,255,255);">Hi,<br/>
<br/>
I tried adding a gist index to geom::geography and oddly enough it made the query a lot slower... Not really sure why, but the best option in my case still seems to be to first doing a bounding box reduction in lon-lat space and then apply ST_DWithin after casting to geography...<br/>
<br/>
Cheers,<br/>
David<br/>
<br/>
<div class="moz-cite-prefix">On 15/01/2019 21:00, <a class="moz-txt-link-abbreviated" href="mailto:postgis-users-request@lists.osgeo.org" onclick="parent.window.location.href='mailto:postgis-users-request@lists.osgeo.org'; return false;" target="_blank">postgis-users-request@lists.osgeo.org</a> wrote:</div>
<blockquote>
<pre class="moz-quote-pre"> </pre>
<div class="moz-txt-sig">
<div class="moz-txt-sig">From: Devdatta Tengshe <a class="moz-txt-link-rfc2396E" href="mailto:devdatta@tengshe.in" onclick="parent.window.location.href='mailto:devdatta@tengshe.in'; return false;" target="_blank"><devdatta@tengshe.in></a> If you are running a query on the Geography, you should create the index on Geography, using something like this: Create Index g_geog_idx on mySchema.myTable using GIST(geom::geography); Using this increased the speed of my query 1000 times. Source: <a class="moz-txt-link-freetext" href="https://twitter.com/postgis/status/675001071505383424" target="_blank">https://twitter.com/postgis/status/675001071505383424</a> Regards, Devdatta On Tue, Jan 15, 2019 at 4:07 PM David M. Kaplan <a class="moz-txt-link-rfc2396E" href="mailto:david.kaplan@ird.fr" onclick="parent.window.location.href='mailto:david.kaplan@ird.fr'; return false;" target="_blank"><david.kaplan@ird.fr></a> wrote:</div>
</div>
<blockquote style="color: rgb(0,0,0);">
<pre class="moz-quote-pre">Hi,
For a while it has not been clear in my head if and when GIST indexes are
used when doing geography based calculations. If the data is stored in a
table with a geometry column that has a GIST index on it, will that index
be used if one does something like
ST_DWithin(a.geom::geography,b.geom,1000)? If not, then if the data was
stored in geography format instead of geometry, would this make the index
more useful?
To give a specific context, I have a table of point geometries with SRID
4326 and a GIST index, and I want to find the number of points in that
table that are within a certain distance of each other point in that table,
but I am not sure what is the most efficient way to do so. To do this, I
have a query of the form:
SELECT a.gid, a.geom, count(*) AS num_points
FROM mytable a
JOIN mytable b
ON a.gid<>b.gid
AND ST_DWithin(a.geom::geography,b.geom,1000,FALSE)
GROUP BY a.gid,a.geom
;
This is quite slow, so I presume the GIST index is not being used (and
EXPLAIN didn't show anything that made it clear that it was being used
though bounding box comparisons are included in the join filter). Also
adding a lonlat bounding box comparison does speed the calculation up
significantly:
SELECT a.gid, a.geom, count(*) AS num_points
FROM mytable a
JOIN mytable b
ON a.gid<>b.gid
AND a.geom <#> b.geom < 0.02 -- in region where 0.02 degrees is > 1000 m
AND ST_DWithin(a.geom::geography,b.geom,1000,FALSE)
GROUP BY a.gid,a.geom
;
Is this the best approach or am I missing something? Would using a LATERAL
join improve things?
Thanks,
David
</pre>
</blockquote>
</blockquote>
<pre class="moz-signature">--
**********************************
David M. Kaplan
Charge de Recherche 1
Institut de Recherche pour le Developpement (IRD)
UMR MARBEC (IRD/Ifremer/CNRS/UMII)
av. Jean Monnet
CS 30171
34203 Sete cedex
France
Email: <a class="moz-txt-link-abbreviated" href="mailto:david.kaplan@ird.fr" onclick="parent.window.location.href='mailto:david.kaplan@ird.fr'; return false;" target="_blank">david.kaplan@ird.fr</a>
Phone: +33 (0)4 99 57 32 25
Fax: +33 (0)4 99 57 32 95
<a class="moz-txt-link-freetext" href="http://www.umr-marbec.fr/kaplan-david.html" target="_blank">http://www.umr-marbec.fr/kaplan-david.html</a>
<a class="moz-txt-link-freetext" href="http://www.davidmkaplan.fr/" target="_blank">http://www.davidmkaplan.fr/</a>
**********************************
</pre>
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></div>
</div>
</div>
</div></div></body></html>