[postgis-users] Slow performance when selecting a geometry column

[David Robison]

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 at lists.osgeo.org] On Behalf Of Paul Ramsey
Sent: Thursday, March 17, 2016 9:53 PM
To: PostGIS Users Discussion <postgis-users at 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 at q-free.com<mailto:David.Robison at 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 at q-free.com<mailto:david.robison at 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 at 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 at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160318/c3063669/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 217 bytes
Desc: image001.png
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160318/c3063669/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 24511 bytes
Desc: image002.png
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160318/c3063669/attachment-0001.png>