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

[Paul Ramsey]

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.Robison at 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-bounces at lists.osgeo.org] *On
> Behalf Of *Nordgren, Bryce L -FS
> *Sent:* Friday, March 18, 2016 8:59 AM
>
> *To:* PostGIS Users Discussion <postgis-users at 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-bounces at lists.osgeo.org
> <postgis-users-bounces at lists.osgeo.org>] *On Behalf Of *David Robison
> *Sent:* Friday, March 18, 2016 6:14 AM
> *To:* PostGIS Users Discussion <postgis-users at 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-bounces at lists.osgeo.org
> <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>
> 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.robison at q-free.com
>
> www.q-free.com/openroads
>
> [image: cid:image001.png at 01D15905.23A1F460]
>
> *Q-Free Open Roads*
>
> 103 Watson Road
>
> Chesapeake VA 23320
>
> [image: cid:image001.png at 01D15905.23A1F460]
>
> [image: 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
> 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 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/4ef1e1b9/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 217 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160318/4ef1e1b9/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 24511 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160318/4ef1e1b9/attachment-0001.png>