[postgis-users] Very long query time

[Tumasgiu Rossini]

actually, yes you have a spatial index : sidx_tuile_geom

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

> 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>
> wrote:
>
> Hello,
>
> The indexes are code_zoneinteret and code_tuile on recouvrement
>
>
>
>
>
> *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:18
> *À :* PostGIS Users Discussion <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 <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 <postgis-users-bounces at lists.osgeo.org> *De la part
> de* Tumasgiu Rossini
> *Envoyé :* jeudi 6 décembre 2018 17:09
> *À :* PostGIS Users Discussion <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> 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
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> 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
> 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
> 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/14f8cb78/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/14f8cb78/attachment.png>
-------------- 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/14f8cb78/attachment-0001.png>