[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Query choces on searching too small area
From: Rémi_Cura <remi.cura () gmail ! com>
Date: 2016-12-19 10:06:43
Message-ID: CAJvUf_sFi19PGMJN0_aUTJVrwe3JjEEhE1aAYfm_O9wZaadfjQ () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
@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@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@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
>
[Attachment #5 (text/html)]
<div dir="ltr"><div class="gmail_default" \
style="font-family:monospace,monospace">@Paul, that's what I thought, but it \
seems querry can't be un-rolled like this,<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">because one row will have only one \
key.<br></div><div class="gmail_default" style="font-family:monospace,monospace">(no \
one row will match all the conditions, you have to get several rows and perform \
intersection of results).<br><br></div><div class="gmail_default" \
style="font-family:monospace,monospace">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. <br><br></div><div class="gmail_default" \
style="font-family:monospace,monospace">Cheers,<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">Rémi-C<br></div></div><div \
class="gmail_extra"><br><div class="gmail_quote">2016-12-16 18:59 GMT+01:00 Paul \
Ramsey <span dir="ltr"><<a href="mailto:pramsey@cleverelephant.ca" \
target="_blank">pramsey@cleverelephant.ca</a>></span>:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr">Two things:<div><br></div><div>(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:</div><div><br></div><div><div>SELECT DISTINCT ON (<a href="http://photo.id" \
target="_blank">photo.id</a>)</div><div> <a href="http://photo.id" \
target="_blank">photo.id</a>, </div><div> photo.filename, </div><div> \
ST_AsText(photo.geometry) AS geometry, </div><div> ST_AsText(photo.center) AS \
center, </div><div> photo.angle</div><div>FROM photo</div><div>JOIN \
"photoMetadata" meta</div><div>ON <a href="http://photo.id" \
target="_blank">photo.id</a> = meta."photoId"</div><div>WHERE (key = \
'source' AND value = 'KADASTER')</div><div>AND (key = 'year' \
AND value::int BETWEEN 1866 AND 1981)</div><div>AND ST_Intersects(photo.geometry, \
ST_SetSRID(ST_MakePoint(4.<wbr>5063099203616, 51.923602970634), 4326)) \
</div><div>ORDER BY photo.filenam</div></div><div><br></div><div>(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.</div><span class="HOEnZb"><font \
color="#888888"><div><br></div><div>P.</div><div><br></div><div><br></div></font></span></div><div \
class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div \
class="gmail_quote">On Fri, Dec 16, 2016 at 8:48 AM, Sandro Santilli <span \
dir="ltr"><<a href="mailto:strk@kbt.io" target="_blank">strk@kbt.io</a>></span> \
wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex">On Fri, Dec 16, 2016 at 03:33:32PM +0100, Arjen Haayman \
wrote:<br> > QUERY PLAN<br>
<br>
[...]<br>
<span><br>
> What does this tell you?<br>
<br>
</span>That your query is too complex ?<br>
Check out <a href="http://explain.depesz.com" rel="noreferrer" \
target="_blank">http://explain.depesz.com</a><br> <div \
class="m_1514573588445157480HOEnZb"><div \
class="m_1514573588445157480h5"><br>
--strk;<br>
______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br> <a \
href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" \
target="_blank">http://lists.osgeo.org/mailman<wbr>/listinfo/postgis-users</a></div></div></blockquote></div><br></div>
</div></div><br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" \
target="_blank">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic