[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Slow performance when selecting a geometry column
From: Rémi_Cura <remi.cura () gmail ! com>
Date: 2016-03-21 10:47:39
Message-ID: CAJvUf_sVoY8CycwnQ17rP3ZCy3-gmJ4FG17bagVemZUf3C+Xng () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/related)]
[Attachment #4 (multipart/alternative)]
Hey
pgadmin takes a long time to __display__ the result of any query.
If you want a somehow better timing,
user EXPLAIN ANALYSE, that is Maj+F7.
It will perform all the real computing, plus some time for measure, but no
time for output.
Cheers,
2016-03-18 15:43 GMT+01:00 Paul Ramsey <pramsey@cleverelephant.ca>:
> Aah! PgAdminIII. Watch your CPU meter and see how busy PgAdmin is vs
> PostgreSQL. See how long this runs in:
>
> SELECT ST_Area(geom) FROM mytable;
>
> Still has to rip every geometry off disk, and has to do a *calculation* on
> it, before returning the result to the client.
>
> P
>
> On Fri, Mar 18, 2016 at 6:07 AM, David Robison <David.Robison@q-free.com>
> wrote:
>
> > Actually the timing test was done on the same machine using PGAdmin-III.
> > What is interesting is that if I return the geometry using something like
> > ST_Simplify(the_geom, 0.1, false) then it returns in just a few hundred
> > milliseconds.
> >
> > David
> >
> >
> >
> > *From:* postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] *On
> > Behalf Of *Nordgren, Bryce L -FS
> > *Sent:* Friday, March 18, 2016 8:59 AM
> >
> > *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> > *Subject:* Re: [postgis-users] Slow performance when selecting a
> > geometry column
> >
> >
> >
> > What kind of network separates client and server? Conservatively assuming
> > that each point is only two 64-bit binary floats, your 560000 points equals
> > 9MB of additional payload.
> >
> >
> >
> > If you did something like "ST_AsText(geom)", the additional payload is
> > much, MUCH larger.
> >
> >
> >
> > I'd also be interested to know if there's some kind of data
> > manipulation/packing/compression going on between client and server.
> >
> >
> >
> > Bryce
> >
> >
> >
> > *From:* postgis-users [mailto:postgis-users-bounces@lists.osgeo.org
> > <postgis-users-bounces@lists.osgeo.org>] *On Behalf Of *David Robison
> > *Sent:* Friday, March 18, 2016 6:14 AM
> > *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> > *Subject:* Re: [postgis-users] Slow performance when selecting a
> > geometry column
> >
> >
> >
> > So there is a total of over 560,000 points in the 8000 records returned.
> > I would have assumed that it was simply returning the contents of the
> > geometry column and that the number of points (albeit they take up space)
> > would not affect the select time. Is PostGIS doing something with the
> > geometries as it is fetching them from the DB other than just returning
> > them? Thanks, David
> >
> >
> >
> > *From:* postgis-users [mailto:postgis-users-bounces@lists.osgeo.org
> > <postgis-users-bounces@lists.osgeo.org>] *On Behalf Of *Paul Ramsey
> > *Sent:* Thursday, March 17, 2016 9:53 PM
> > *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> > *Subject:* Re: [postgis-users] Slow performance when selecting a
> > geometry column
> >
> >
> >
> > SELECT Sum(ST_NPoints(geom)) FROM thetable;
> >
> >
> >
> > On Thu, Mar 17, 2016 at 5:57 PM, David Robison <David.Robison@q-free.com>
> > wrote:
> >
> > I am having an issue with a postgis database with the time it takes to
> > query the geometry column. The query selects 8000 records. The time to
> > retrieve the records when not returning the geometry column is about 100ms.
> > However, the time for the same query when requesting the geometry column
> > takes about 9 seconds. Any thoughts on how I can improve the performance
> > reading the geometry column from a postgis database? I am using PostgreSQL
> > 9.4 and Postgis 22.1.
> >
> > Thanks, David
> >
> >
> >
> > *David Robison*
> >
> > *Principal System Engineer*
> >
> > O. +1 757 546 3401
> >
> > M. +1 757 286 0022
> >
> > david.robison@q-free.com
> >
> > www.q-free.com/openroads
> >
> > [image: cid:image001.png@01D15905.23A1F460]
> >
> > *Q-Free Open Roads*
> >
> > 103 Watson Road
> >
> > Chesapeake VA 23320
> >
> > [image: cid:image001.png@01D15905.23A1F460]
> >
> > [image: cid:image002.png@01D15905.23A1F460]
> > ------------------------------
> >
> > This email communication (including any attachments) may contain confidential \
> > and/or privileged material intended solely for the individual or entity to which \
> > it is addressed. If you are not the intended recipient, please delete this email \
> > immediately.
> >
> >
> >
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/postgis-users
> >
> >
> >
> >
> >
> >
> >
> > This electronic message contains information generated by the USDA solely
> > for the intended recipients. Any unauthorized interception of this message
> > or the use or disclosure of the information it contains may violate the law
> > and subject the violator to civil or criminal penalties. If you believe you
> > have received this message in error, please notify the sender and delete
> > the email immediately.
> >
> > _______________________________________________
> > 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 #7 (text/html)]
<div dir="ltr"><div class="gmail_default" \
style="font-family:monospace,monospace">Hey<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">pgadmin takes a long time to __display__ the \
result of any query.<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">If you want a somehow better \
timing,<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">user EXPLAIN ANALYSE, that is \
Maj+F7.<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">It will perform all the real computing, plus \
some time for measure, but no time for output.<br><br>Cheers,<br><br></div></div><div \
class="gmail_extra"><br><div class="gmail_quote">2016-03-18 15:43 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">Aah! PgAdminIII. Watch your CPU meter and see \
how busy PgAdmin is vs PostgreSQL. See how long this runs \
in:<div><br></div><div>SELECT ST_Area(geom) FROM \
mytable;</div><div><br></div><div>Still has to rip every geometry off disk, and has \
to do a *calculation* on it, before returning the result to the client.</div><span \
class="HOEnZb"><font \
color="#888888"><div><br></div><div>P</div></font></span></div><div \
class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div \
class="gmail_quote">On Fri, Mar 18, 2016 at 6:07 AM, David Robison <span \
dir="ltr"><<a href="mailto:David.Robison@q-free.com" \
target="_blank">David.Robison@q-free.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">
<div link="blue" vlink="purple" lang="EN-US">
<div>
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Actually \
the timing test was done on the same machine using PGAdmin-III. What is interesting \
is that if I return the geometry using something like ST_Simplify(the_geom, 0.1, \
false) then it returns in just a few hundred milliseconds.<u></u><u></u></span></p> \
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">David<u></u><u></u></span></p>
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> \
<u></u></span></p> <div>
<div style="border:none;border-top:solid #e1e1e1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users \
[mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" \
target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of \
</b>Nordgren, Bryce L -FS<br> <b>Sent:</b> Friday, March 18, 2016 8:59 \
AM</span></p><div><div><br> <b>To:</b> PostGIS Users Discussion <<a \
href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a>><br> <b>Subject:</b> Re: \
[postgis-users] Slow performance when selecting a geometry \
column<u></u><u></u></div></div><p></p> </div>
</div><div><div>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">What \
kind of network separates client and server? Conservatively assuming that each point \
is only two 64-bit binary floats, your 560000 points equals 9MB of additional \
payload.<u></u><u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">If \
you did something like "ST_AsText(geom)", the additional payload is much, MUCH \
larger. <u></u><u></u></span></p>
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">I'd \
also be interested to know if there's some kind of data \
manipulation/packing/compression going on between client and server. \
<u></u><u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Bryce<u></u><u></u></span></p>
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> \
<u></u></span></p> <div>
<div style="border:none;border-top:solid #e1e1e1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users \
[<a href="mailto:postgis-users-bounces@lists.osgeo.org" \
target="_blank">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of \
</b>David Robison<br> <b>Sent:</b> Friday, March 18, 2016 6:14 AM<br>
<b>To:</b> PostGIS Users Discussion <<a \
href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a>><br> <b>Subject:</b> Re: \
[postgis-users] Slow performance when selecting a geometry \
column<u></u><u></u></span></p> </div>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">So \
there is a total of over 560,000 points in the 8000 records returned. I would have \
assumed that it was simply returning the contents of the geometry column and that \
the number of points (albeit they take up space) would not affect the select time. Is \
PostGIS doing something with the geometries as it is fetching them from the DB other \
than just returning them? Thanks, David<u></u><u></u></span></p> <p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> \
<u></u></span></p> <p class="MsoNormal"><b><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users \
[<a href="mailto:postgis-users-bounces@lists.osgeo.org" \
target="_blank">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of \
</b>Paul Ramsey<br> <b>Sent:</b> Thursday, March 17, 2016 9:53 PM<br>
<b>To:</b> PostGIS Users Discussion <<a \
href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a>><br> <b>Subject:</b> Re: \
[postgis-users] Slow performance when selecting a geometry \
column<u></u><u></u></span></p> <p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">SELECT Sum(ST_NPoints(geom)) FROM thetable;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">On Thu, Mar 17, 2016 at 5:57 PM, David Robison <<a \
href="mailto:David.Robison@q-free.com" \
target="_blank">David.Robison@q-free.com</a>> wrote:<u></u><u></u></p> <blockquote \
style="border:none;border-left:solid #cccccc 1.0pt;padding:0in 0in 0in \
6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt"> <div>
<div>
<p class="MsoNormal">I am having an issue with a postgis database with the time it \
takes to query the geometry column. The query selects 8000 records. The time to \
retrieve the records when not returning the geometry column is about 100ms. However, \
the time for the same query when requesting the geometry column takes about 9 \
seconds. Any thoughts on how I can improve the performance reading the geometry \
column from a postgis database? I am using PostgreSQL 9.4 and Postgis \
22.1.<u></u><u></u></p> <p class="MsoNormal">Thanks, David
<u></u><u></u></p>
<p class="MsoNormal"> <u></u><u></u></p>
<p class="MsoNormal" style="margin-bottom:2.0pt"><b><span \
style="font-family:"Arial",sans-serif;color:#ee3524">David \
Robison</span></b><u></u><u></u></p> <p class="MsoNormal" \
style="margin-bottom:2.0pt"><i><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black">Principal \
System Engineer</span></i><u></u><u></u></p> <p class="MsoNormal" \
style="margin-bottom:2.0pt"><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black" \
lang="EN-GB">O. <a href="tel:%2B1%20757%20546%203401" target="_blank">+1 757 546 \
3401</a></span><u></u><u></u></p> <p class="MsoNormal" \
style="margin-bottom:2.0pt"><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black" \
lang="EN-GB">M. <a href="tel:%2B1%20757%20286%200022" target="_blank">+1 757 286 \
0022</a></span><u></u><u></u></p> <p class="MsoNormal" \
style="margin-bottom:2.0pt"><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif"><a \
href="mailto:david.robison@q-free.com" \
target="_blank">david.robison@q-free.com</a><span style="color:black"> \
</span></span><u></u><u></u></p> <p class="MsoNormal" \
style="margin-bottom:2.0pt"><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif"><a \
href="http://www.q-free.com/openroads" target="_blank"><span \
style="color:black">www.q-free.com/openroads</span></a><span style="color:black"> \
</span></span><u></u><u></u></p> <p class="MsoNormal" \
style="margin-bottom:6.0pt"><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif"><img \
src="cid:image001.png@01D180F5.8B5DACD0" alt="cid:image001.png@01D15905.23A1F460" \
height="2" border="0" width="626"></span><u></u><u></u></p> <p class="MsoNormal" \
style="margin-bottom:2.0pt"><b><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif;color:#ee3524">Q-Free \
Open Roads</span></b><u></u><u></u></p> <p class="MsoNormal" \
style="margin-bottom:2.0pt"><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black" \
lang="EN-GB">103 Watson Road </span><u></u><u></u></p>
<p class="MsoNormal" style="margin-bottom:2.0pt"><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black" \
lang="EN-GB">Chesapeake VA 23320</span><u></u><u></u></p> <p class="MsoNormal" \
style="margin-bottom:6.0pt"><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif"><img \
src="cid:image001.png@01D180F5.8B5DACD0" alt="cid:image001.png@01D15905.23A1F460" \
height="2" border="0" width="626"></span><u></u><u></u></p> <p \
class="MsoNormal"><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif"><img \
src="cid:image002.png@01D180F5.8B5DACD0" alt="cid:image002.png@01D15905.23A1F460" \
height="288" border="0" width="226"></span><u></u><u></u></p> <div class="MsoNormal" \
style="text-align:center" align="center"><span \
style="font-size:10.0pt;font-family:"Tahoma",sans-serif"> <hr \
align="center" size="3" width="100%"> </span></div>
<p class="MsoNormal"><span \
style="font-size:10.0pt;font-family:"Tahoma",sans-serif">This email \
communication (including any attachments) may contain confidential and/or \
privileged material intended solely for the individual or entity to which \
it is addressed. If you are not the intended recipient, please delete \
this email immediately.</span><u></u><u></u></p> <p class="MsoNormal"> \
<u></u><u></u></p> <p class="MsoNormal"> <u></u><u></u></p>
</div>
</div>
<p class="MsoNormal"><br>
_______________________________________________<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" \
target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><u></u><u></u></p>
</blockquote>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<p class="MsoNormal"><br>
<br>
<br>
<br>
This electronic message contains information generated by the USDA solely for the \
intended recipients. Any unauthorized interception of this message or the use or \
disclosure of the information it contains may violate the law and subject the \
violator to civil or criminal penalties. If you believe you have received this \
message in error, please notify the sender and delete the email immediately. \
<u></u><u></u></p> </div></div></div>
</div>
<br>_______________________________________________<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/listinfo/postgis-users</a><br></blockquote></div><br></div>
</div></div><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/mailman/listinfo/postgis-users" rel="noreferrer" \
target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
["image001.png" (image/png)]
["image002.png" (image/png)]
[Attachment #10 (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