[postgis-users] Very long query time

[Manchon Pierre]

Ha yes it’s because it’s a screenshot from a SGBD modeler not a graph view 
of the actual database (which is the same)

I did not created index on my geometry …

(from database manager on qgis it look more like this):





De : postgis-users <postgis-users-bounces at lists.osgeo.org> De la part de 
Darafei "Kom?pa" Praliaskouski
Envoyé : jeudi 6 décembre 2018 17:43
À : PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Objet : Re: [postgis-users] Very long query time



Hi,



did you create spatial index on your geometry?

your screenshot also does not show any geometry field.



On Thu, Dec 6, 2018 at 7:29 PM Manchon Pierre <pierre.manchon at irstea.fr 
<mailto:pierre.manchon at irstea.fr> > wrote:

Hello,

The indexes are code_zoneinteret and code_tuile on recouvrement







De : postgis-users <postgis-users-bounces at lists.osgeo.org 
<mailto:postgis-users-bounces at lists.osgeo.org> > De la part de Darafei 
"Kom?pa" Praliaskouski
Envoyé : jeudi 6 décembre 2018 17:18
À : PostGIS Users Discussion <postgis-users at lists.osgeo.org 
<mailto:postgis-users at lists.osgeo.org> >
Objet : Re: [postgis-users] Very long query time



Hello,



What are the indexes on the table?



On Thu, Dec 6, 2018 at 7:16 PM Manchon Pierre < 
<mailto:pierre.manchon at irstea.fr> pierre.manchon at irstea.fr> wrote:

Yes the request took about ~100ms to be executed before but now it never 
ends (I left it 1h)

(thx for the tip with where clause)



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



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
 <mailto:postgis-users at lists.osgeo.org> postgis-users at lists.osgeo.org
 <https://lists.osgeo.org/mailman/listinfo/postgis-users> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
 <mailto:postgis-users at lists.osgeo.org> postgis-users at lists.osgeo.org
 <https://lists.osgeo.org/mailman/listinfo/postgis-users> 
https://lists.osgeo.org/mailman/listinfo/postgis-users






-- 

Darafei Praliaskouski

Support me: http://patreon.com/komzpa

_______________________________________________
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






-- 

Darafei Praliaskouski

Support me: http://patreon.com/komzpa

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20181206/e635ca49/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 35423 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20181206/e635ca49/attachment.png>