<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-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@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">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 [mailto:postgis-users-bounces@lists.osgeo.org]
<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 <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>
<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-right:0in">
<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@01D180EE.289302F0" 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@01D180EE.289302F0" 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@01D180EE.289302F0" 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 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>
</div>
</body>
</html>