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-boun...@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-boun...@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-boun...@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.robi...@q-free.com<mailto:david.robi...@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.robi...@q-free.com<mailto:david.robi...@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.
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users
