[postgis-users] Very long query time

[Manchon Pierre]

It is pretty simple :

The query take data from the orange tables and insert it into the purple one





De : postgis-users <postgis-users-bounces at lists.osgeo.org> De la part de 
Tumasgiu Rossini
Envoyé : jeudi 6 décembre 2018 17:19
À : PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Objet : Re: [postgis-users] Very long query time



And I am not really sure that what you are willing to achieve with the GROUP 
BY

I am not aware of you data model, though.



Maybe you have upgraded recently to postgis 2.4 which change GROUP BY 
behavior

when it comes to geometries [1]?

Geometries in 2.4 are no more grouped using bbox comparison but using strict 
equality (st_equals)



[1]http://blog.cleverelephant.ca/2017/09/postgis-operators.html





Le jeu. 6 déc. 2018 à 17:09, Tumasgiu Rossini <rossini.t at gmail.com 
<mailto:rossini.t at gmail.com> > a écrit :

I do not really understand.

Is it a query which used to be faster ?



A little remark on your where clause :

it is unnecesseraly complicated and could be shortened to :

    WHERE st_intersects(zi.geom, tile.geom)



True and false value doesn't need to be surronded by quote,

here you are implicitly casting the string value 'true' to its boolean 
representation





Le jeu. 6 déc. 2018 à 16:53, Manchon Pierre <pierre.manchon at irstea.fr 
<mailto:pierre.manchon at irstea.fr> > a écrit :

Hello there,

I'm new on the list and for a premiere I would like to know if some of you 
have a very long runtime for some of their queries (even with a LIMIT 5), 
whereas those queries were working well before (nothing changed from that). 
I'm using Postgre10, pgAdmin4 and postgis 2.4.4 on windows …



The infinite query I’m trying to execute: (only ~500 rows are stored on each 
of the two tables)




SELECT code_tile, code_zi FROM data.tile, data.zi

WHERE ST_Intersects(zi.geom, tile.geom) = 'true'

GROUP BY code_zi, code_tile, zi.geom, tile.geom

LIMIT 5



Thank you for your time

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto: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/20181206/ce96c5e9/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 24818 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20181206/ce96c5e9/attachment.png>