@Paul, that's what I thought, but it seems querry can't be un-rolled like
this,
because one row will have only one key.
(no one row will match all the conditions, you have to get several rows and
perform intersection of results).
I fully agree this is totally bad for postgres, I would guess Arjen
Query choces on searching too small area
Hey, there is a dedicated "slow query" protocol on postgres user list, and its
quite sane.
For instance, it would suggest you to give the version number you use, your
hardware, etc etc.
About you query, I guess your photo are geotagged (i.e. each
Two things:
(a) I'm curious what happens when you unroll all those embedded subqueries
and let the planner try to do what it does best, something like this:
SELECT DISTINCT ON (photo.id)
photo.id,
photo.filename,
ST_AsText(photo.geometry) AS geometry,
ST_AsText(photo.center) AS center,
Hey, there is a dedicated "slow query" protocol on postgres user list, and
its quite sane.
For instance, it would suggest you to give the version number you use, your
hardware, etc etc.
About you query, I guess your photo are geotagged (i.e. each photo is a
point, and maybe you have a precision
On Fri, Dec 16, 2016 at 03:33:32PM +0100, Arjen Haayman wrote:
> QUERY PLAN
[...]
> What does this tell you?
That your query is too complex ?
Check out http://explain.depesz.com
--strk;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
QUERY PLAN
Limit (cost=27753.88..27753.89 rows=1 width=819)
-> Sort (cost=27753.88..27753.89 rows=1 width=819)
Sort Key: photo."photoDirId", photo.foto_nr
-> Nested Loop Semi Join (cost=23473.88..27753.87 rows=1 width=819)
Join Filter: (photo.id =
On Fri, Dec 16, 2016 at 02:09:01PM +0100, Arjen Haayman wrote:
> What I really don't understand why it would fail when the query gets too
> specific, I mean searching on a point should be easier than searching a large
> bounding box. It usually is the other way around??
It looks like a bug in
[mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of
Martijn Meijers
Sent: vrijdag 16 december 2016 15:01
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] Query choces on searching too small area
What happens if you simplify the query. E.g. just usin
What happens if you simplify the query. E.g. just using only geometry in
the where clause, or when you execute separately the subqueries inside
the where clause? Do these already take long to execute, do they use the
indexes defined (tables are recently vacuum'ed?) and do they give back a
Hi,
I've got this database that has photos that have a location and a table with
key/value metadata. There are almost 450,000 photos and 5 million rows of
metadata.
Searching on metadata alone always works, but when I add a spatial search to it
the query freezes if the spatial component is too
10 matches
Mail list logo
