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

[Rémi Cura]

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 <pramsey at 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.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
>>
>
>
> _______________________________________________
> 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/20160321/110e4027/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/20160321/110e4027/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/20160321/110e4027/attachment-0001.png>