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

[Rémi Cura]

@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 <pramsey at 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 <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
>>
>
>
> _______________________________________________
> 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/20161219/02821794/attachment.html>