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 <[email protected]> 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:[email protected]] *On > Behalf Of *Nordgren, Bryce L -FS > *Sent:* Friday, March 18, 2016 8:59 AM > > *To:* PostGIS Users Discussion <[email protected]> > *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:[email protected] > <[email protected]>] *On Behalf Of *David Robison > *Sent:* Friday, March 18, 2016 6:14 AM > *To:* PostGIS Users Discussion <[email protected]> > *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:[email protected] > <[email protected]>] *On Behalf Of *Paul Ramsey > *Sent:* Thursday, March 17, 2016 9:53 PM > *To:* PostGIS Users Discussion <[email protected]> > *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 <[email protected]> > 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 > > [email protected] > > www.q-free.com/openroads > > [image: cid:[email protected]] > > *Q-Free Open Roads* > > 103 Watson Road > > Chesapeake VA 23320 > > [image: cid:[email protected]] > > [image: cid:[email protected]] > ------------------------------ > > 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 > [email protected] > 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 > [email protected] > http://lists.osgeo.org/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/postgis-users
