[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: David Robison <David.Robison () q-free ! com>
Date: 2016-03-18 13:07:09
Message-ID: HE1PR02MB10503CED0859F0E0BFB8F944C98C0 () HE1PR02MB1050 ! eurprd02 ! prod ! outlook ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
[Attachment #4 (text/plain)]
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] On Behalf Of David \
Robison
Sent: Friday, March 18, 2016 6:14 AM
To: PostGIS Users Discussion \
<postgis-users@lists.osgeo.org<mailto: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] On Behalf Of Paul \
Ramsey
Sent: Thursday, March 17, 2016 9:53 PM
To: PostGIS Users Discussion \
<postgis-users@lists.osgeo.org<mailto: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<mailto: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<tel:%2B1%20757%20546%203401>
M. +1 757 286 0022<tel:%2B1%20757%20286%200022>
david.robison@q-free.com<mailto:david.robison@q-free.com>
www.q-free.com/openroads<http://www.q-free.com/openroads>
Q-Free Open Roads
103 Watson Road
Chesapeake VA 23320
[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<mailto: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.
[Attachment #5 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"> <head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<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.<o:p></o:p></span></p> <p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">David<o:p></o:p></span></p>
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></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:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Nordgren, Bryce L \
-FS<br> <b>Sent:</b> Friday, March 18, 2016 8:59 AM<br>
<b>To:</b> PostGIS Users Discussion <postgis-users@lists.osgeo.org><br>
<b>Subject:</b> Re: [postgis-users] Slow performance when selecting a geometry \
column<o:p></o:p></span></p> </div>
</div>
<p class="MsoNormal"><o:p> </o:p></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.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></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. <o:p></o:p></span></p>
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></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. \
<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Bryce<o:p></o:p></span></p>
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></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">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">postgis-users@lists.osgeo.org</a>><br> \
<b>Subject:</b> Re: [postgis-users] Slow performance when selecting a geometry \
column<o:p></o:p></span></p> </div>
</div>
<p class="MsoNormal"><o:p> </o:p></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<o:p></o:p></span></p> <p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></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">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">postgis-users@lists.osgeo.org</a>><br> \
<b>Subject:</b> Re: [postgis-users] Slow performance when selecting a geometry \
column<o:p></o:p></span></p> <p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">SELECT Sum(ST_NPoints(geom)) FROM thetable;<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></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:<o:p></o:p></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" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">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.<o:p></o:p></p>
<p class="MsoNormal" \
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Thanks, David \
<o:p></o:p></p> <p class="MsoNormal" \
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p> <p \
class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:2.0pt"><b><span \
style="font-family:"Arial",sans-serif;color:#EE3524">David \
Robison</span></b><o:p></o:p></p> <p class="MsoNormal" \
style="mso-margin-top-alt:auto;margin-bottom:2.0pt"><i><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black">Principal \
System Engineer</span></i><o:p></o:p></p> <p class="MsoNormal" \
style="mso-margin-top-alt:auto;margin-bottom:2.0pt"><span lang="EN-GB" \
style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black">O. <a \
href="tel:%2B1%20757%20546%203401" target="_blank">+1 757 546 \
3401</a></span><o:p></o:p></p> <p class="MsoNormal" \
style="mso-margin-top-alt:auto;margin-bottom:2.0pt"><span lang="EN-GB" \
style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black">M. <a \
href="tel:%2B1%20757%20286%200022" target="_blank">+1 757 286 \
0022</a></span><o:p></o:p></p> <p class="MsoNormal" \
style="mso-margin-top-alt:auto;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><o:p></o:p></p> <p class="MsoNormal" \
style="mso-margin-top-alt:auto;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><o:p></o:p></p> <p class="MsoNormal" \
style="mso-margin-top-alt:auto;margin-bottom:6.0pt"><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif"><img border="0" \
width="626" height="2" id="_x0000_i1025" src="cid:image001.png@01D180F5.8B5DACD0" \
alt="cid:image001.png@01D15905.23A1F460"></span><o:p></o:p></p> <p class="MsoNormal" \
style="mso-margin-top-alt:auto;margin-bottom:2.0pt"><b><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif;color:#EE3524">Q-Free \
Open Roads</span></b><o:p></o:p></p> <p class="MsoNormal" \
style="mso-margin-top-alt:auto;margin-bottom:2.0pt"><span lang="EN-GB" \
style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black">103 \
Watson Road </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:2.0pt"><span \
lang="EN-GB" style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black">Chesapeake \
VA 23320</span><o:p></o:p></p> <p class="MsoNormal" \
style="mso-margin-top-alt:auto;margin-bottom:6.0pt"><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif"><img border="0" \
width="626" height="2" id="_x0000_i1026" src="cid:image001.png@01D180F5.8B5DACD0" \
alt="cid:image001.png@01D15905.23A1F460"></span><o:p></o:p></p> <p class="MsoNormal" \
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span \
style="font-size:10.0pt;font-family:"Arial",sans-serif"><img border="0" \
width="226" height="288" id="_x0000_i1027" src="cid:image002.png@01D180F5.8B5DACD0" \
alt="cid:image002.png@01D15905.23A1F460"></span><o:p></o:p></p> <div \
class="MsoNormal" align="center" style="text-align:center"><span \
style="font-size:10.0pt;font-family:"Tahoma",sans-serif"> <hr size="3" \
width="100%" align="center"> </span></div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span \
style="font-size:10.0pt;font-family:"Tahoma",sans-serif">This email&nbs \
p;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><o:p></o:p></p>
<p class="MsoNormal" \
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p> <p \
class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><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" \
target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p>
</blockquote>
</div>
<p class="MsoNormal"><o:p> </o:p></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. \
<o:p></o:p></p> </div>
</body>
</html>
["image001.png" (image/png)]
["image002.png" (image/png)]
[Attachment #8 (unknown)]
_______________________________________________
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