@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 use a off-the-shelf module and as no choice over architecture. Cheers, Rémi-C 2016-12-16 18:59 GMT+01:00 Paul Ramsey <pram...@cleverelephant.ca>: > 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 <s...@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@lists.osgeo.org >> http://lists.osgeo.org/mailman/listinfo/postgis-users >> > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users