[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Problems with an update query
From: Åsmund_Tokheim <asmundto () gmail ! com>
Date: 2014-05-09 17:26:18
Message-ID: CAFDjuaDrB9hY2R1n7_ZKkYRT6n0om5Cv7enHvtePMcUxesbTsA () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Hi
Try your queries using sql explain statements (
http://www.postgresql.org/docs/current/static/sql-explain.html). You should
then be able to see if the postgresql query planner chooses a different
strategy to process your queries. I suspect that postgresql isn't able to
prefilter on gid when you use gid < 10, and instead computes all rows and
at the very end filters out rows with gid less than 10. Use ST_DWITHIN with
a reasonable distance (to stop cross joining the entire table) together
with a gist index on your geometries if you want increased performance.
Åsmund
On Fri, May 9, 2014 at 6:56 PM, Humberto Cereser Ibanez <
humberto@pastoraldacrianca.org.br> wrote:
> On Fri, 2014-05-09 at 17:17 +0100, Alexandre Neto wrote:
> > Hello all,
> >
> >
> > I'm trying to update a table of polygons with the distance to the
> > nearest polygon in the same table and of the same class. I suspect
> > that there is something fishy about the way my query is build.
> >
> >
> > I was using something like this:
> >
> >
> > UPDATE cosc.cosn1
> > SET enn = c.ENN
> > FROM (SELECT DISTINCT ON(g1.gid) g1.gid As ref_gid,
> > ST_Distance(g1.geom,g2.geom) As ENN
> > FROM "cosc"."cosn1" As g1, "cosc"."cosn1" As g2
> > WHERE g1.gid < g2.gid AND g1.class = g2.class
> > ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom)) as c
> > WHERE gid = c.ref_gid;
>
> May be it is better to make a buffer on g1.geom and filter the g2.geom
> that touch this buffer. For reduce your computation on ST_Distance.
> >
> >
> > It's was taking a long time, but that wasn't surprising. I got my
> > results in 150000ms.
> >
> >
> > While doing some tests I tried no narrow the query a bit by specifying
> > the gid of a feature in the bottom WHERE statement (of the update).
> >
> >
> > UPDATE cosc.cosn1
> > SET enn = c.ENN
> > FROM (SELECT DISTINCT ON(g1.gid) g1.gid As ref_gid,
> > ST_Distance(g1.geom,g2.geom) As ENN
> > FROM "cosc"."cosn1" As g1, "cosc"."cosn1" As g2
> > WHERE g1.gid < g2.gid AND g1.class = g2.class
> > ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom)) as c
> > WHERE gid = 2 AND gid = c.ref_gid;
> >
> >
> > Also not surprising, this took a lot less, around 300ms
> >
> >
> > What started to bug me was the fact that using a different condition
> > to narrow the query by only updating 10 features...
> >
> >
> > UPDATE cosc.cosn1
> > SET enn = c.ENN
> > FROM (SELECT DISTINCT ON(g1.gid) g1.gid As ref_gid,
> > ST_Distance(g1.geom,g2.geom) As ENN
> > FROM "cosc"."cosn1" As g1, "cosc"."cosn1" As g2
> > WHERE g1.gid < g2.gid AND g1.class = g2.class
> > ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom)) as c
> > WHERE gid < 10 AND gid = c.ref_gid;
> >
> >
> > It needed 147510ms to show the results, almost the same as updating
> > the all table!
> >
> >
> > Even more weird was the fact that if I manual set gid = x from [1,10]
> > none of the queries toke more that 400ms to perform.
> >
> Did you create a index for gid (btree) and geom (gist)?
> >
> > Therefore, I must do something wrong for sure!
> >
> >
> > Thank you for your help,
> >
> >
> > Alexandre Neto
>
> Humberto Cereser Ibanez
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
[Attachment #5 (text/html)]
<div dir="ltr">Hi<div><br></div><div>Try your queries using sql explain statements \
(<a href="http://www.postgresql.org/docs/current/static/sql-explain.html">http://www.postgresql.org/docs/current/static/sql-explain.html</a>). \
You should then be able to see if the postgresql query planner chooses a different \
strategy to process your queries. I suspect that postgresql isn't able to \
prefilter on gid when you use gid < 10, and instead computes all rows and at the \
very end filters out rows with gid less than 10. Use ST_DWITHIN with a reasonable \
distance (to stop cross joining the entire table) together with a gist index on your \
geometries if you want increased performance.</div> \
<div><br></div><div>Åsmund</div></div><div class="gmail_extra"><br><br><div \
class="gmail_quote">On Fri, May 9, 2014 at 6:56 PM, Humberto Cereser Ibanez <span \
dir="ltr"><<a href="mailto:humberto@pastoraldacrianca.org.br" \
target="_blank">humberto@pastoraldacrianca.org.br</a>></span> wrote:<br> \
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div class="">On Fri, 2014-05-09 at 17:17 +0100, Alexandre \
Neto wrote:<br> > Hello all,<br>
><br>
><br>
> I'm trying to update a table of polygons with the distance to the<br>
> nearest polygon in the same table and of the same class. I suspect<br>
> that there is something fishy about the way my query is build.<br>
><br>
><br>
> I was using something like this:<br>
><br>
><br>
> UPDATE cosc.cosn1<br>
> SET enn = c.ENN<br>
> FROM (SELECT DISTINCT ON(g1.gid) g1.gid As ref_gid,<br>
> ST_Distance(g1.geom,g2.geom) As ENN<br>
> FROM "cosc"."cosn1" As g1, \
"cosc"."cosn1" As g2<br> > WHERE g1.gid < g2.gid AND \
g1.class = g2.class<br> > ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom)) as c<br>
> WHERE gid = c.ref_gid;<br>
<br>
</div>May be it is better to make a buffer on g1.geom and filter the g2.geom<br>
that touch this buffer. For reduce your computation on ST_Distance.<br>
<div><div class="h5">><br>
><br>
> It's was taking a long time, but that wasn't surprising. I got my<br>
> results in 150000ms.<br>
><br>
><br>
> While doing some tests I tried no narrow the query a bit by specifying<br>
> the gid of a feature in the bottom WHERE statement (of the update).<br>
><br>
><br>
> UPDATE cosc.cosn1<br>
> SET enn = c.ENN<br>
> FROM (SELECT DISTINCT ON(g1.gid) g1.gid As ref_gid,<br>
> ST_Distance(g1.geom,g2.geom) As ENN<br>
> FROM "cosc"."cosn1" As g1, \
"cosc"."cosn1" As g2<br> > WHERE g1.gid < g2.gid AND \
g1.class = g2.class<br> > ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom)) as c<br>
> WHERE gid = 2 AND gid = c.ref_gid;<br>
><br>
><br>
> Also not surprising, this took a lot less, around 300ms<br>
><br>
><br>
> What started to bug me was the fact that using a different condition<br>
> to narrow the query by only updating 10 features...<br>
><br>
><br>
> UPDATE cosc.cosn1<br>
> SET enn = c.ENN<br>
> FROM (SELECT DISTINCT ON(g1.gid) g1.gid As ref_gid,<br>
> ST_Distance(g1.geom,g2.geom) As ENN<br>
> FROM "cosc"."cosn1" As g1, \
"cosc"."cosn1" As g2<br> > WHERE g1.gid < g2.gid AND \
g1.class = g2.class<br> > ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom)) as c<br>
> WHERE gid < 10 AND gid = c.ref_gid;<br>
><br>
><br>
> It needed 147510ms to show the results, almost the same as updating<br>
> the all table!<br>
><br>
><br>
> Even more weird was the fact that if I manual set gid = x from [1,10]<br>
> none of the queries toke more that 400ms to perform.<br>
><br>
</div></div>Did you create a index for gid (btree) and geom (gist)?<br>
<div class="">><br>
> Therefore, I must do something wrong for sure!<br>
><br>
><br>
> Thank you for your help,<br>
><br>
><br>
> Alexandre Neto<br>
<br>
</div>Humberto Cereser Ibanez<br>
<br>
_______________________________________________<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/cgi-bin/mailman/listinfo/postgis-users" \
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br> \
</blockquote></div><br></div>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/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