[postgis-users] confusion regarding use of indexes when doing geography calculations

[Devdatta Tengshe]

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:
https://twitter.com/postgis/status/675001071505383424


Regards,
Devdatta


On Tue, Jan 15, 2019 at 4:07 PM David M. Kaplan <david.kaplan at ird.fr> wrote:

> 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
>
>
> --
> **********************************
> 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: david.kaplan at ird.fr
> Phone: +33 (0)4 99 57 32 25
> Fax: +33 (0)4 99 57 32 95
> http://www.umr-marbec.fr/kaplan-david.htmlhttp://www.davidmkaplan.fr/
> **********************************
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190115/b0fea30a/attachment.html>