[postgis-users] Query choces on searching too small area

[Paul Ramsey]

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,
  photo.angle
FROM photo
JOIN "photoMetadata" meta
ON photo.id = meta."photoId"
WHERE (key = 'source' AND value = 'KADASTER')
AND (key = 'year' AND value::int BETWEEN 1866 AND 1981)
AND ST_Intersects(photo.geometry, ST_SetSRID(ST_MakePoint(4.5063099203616,
51.923602970634), 4326))
ORDER BY photo.filenam

(b) You should strongly consider changing your metadata table from the
key/value table into a jsonb table, with the metadata in JSON. Then for
things like date, and source, you can build functional indexes to allow
fast filtering on those common metadata fields, while still allowing fully
free-form metadata objects. This would make the whole thing both simpler
and a lot faster.

P.



On Fri, Dec 16, 2016 at 8:48 AM, Sandro Santilli <strk at kbt.io> wrote:

> 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 at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161216/4d643170/attachment.html>