Hey pgadmin takes a long time to __display__ the result of any query. If you want a somehow better timing, user EXPLAIN ANALYSE, that is Maj+F7. It will perform all the real computing, plus some time for measure, but no time for output.
Cheers, 2016-03-18 15:43 GMT+01:00 Paul Ramsey <pram...@cleverelephant.ca>: > 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 <david.robi...@q-free.com> > 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: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 >> <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> >> *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 >> <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> >> *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> >> 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 >> >> david.robi...@q-free.com >> >> www.q-free.com/openroads >> >> [image: cid:image001.png@01D15905.23A1F460] >> >> *Q-Free Open Roads* >> >> 103 Watson Road >> >> Chesapeake VA 23320 >> >> [image: cid:image001.png@01D15905.23A1F460] >> >> [image: 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 >> 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 >> > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users