[Qgis-user] Postgis speed with QGIS

[Chris Crook]

Hi John

The polygon/multipolygon thing won't be a problem.  Qgis just needs to work out whether it is dealing with points, lines, or polygons.  Polygons and multipolygons are equivalent in that sense.

If it is doing a fetch forward ... thing then it doesn't sound like the setup queries are causing the problem.  If you are viewing the entire layer then I guess it will be quite slow.  However you can stop the rendering at any point with the "stop map rendering" button on the bottom right.  What we usually do is set up the scale dependent rendering for layers like this, so that they are not displayed at all until we are zoomed far enough in for them to be useful.  

Main thing is that the "use estimated metadata" won't make much difference if it is mainly fetching data that is slowing things down.  Perhaps there is some resource contention on Postgres .. generally we are not using the same box for the server and qgis (though like you, I expect, I do that from time to time for development work).

I'll be interested in anything else you learn that improves performance.

Cheers
Chris
________________________________________
From: John Abraham [jabraham at ucalgary.ca]
Sent: 10 June 2010 16:28
To: Chris Crook
Cc: qgis-user at lists.osgeo.org
Subject: Re: [Qgis-user] Postgis speed with QGIS

Mine are parcel databases (cadastral) for urban regions.  The one for San Diego, for instance, has 800,000 rows.

Geometries are mostly polygons but some multipolygons.  Will this cause a problem with the "use estimated metadata" option?

If I look at the server while I'm waiting for QGIS to respond, it's usually doing a "fetch forward 200" at the time.

--
John


On 2010-06-09, at 7:09 PM, Chris Crook wrote:

>
> Hi John
>
> What size of database are you working with.  Assuming that you have built the indexes and analyzed the tables so that they are being used for the queries PostgreSQL is fine for small databases.
>
> For larger ones (say > 10,000 records) there are some Qgis implementation issues that can make the response very slow, at least for initially loading the layers.  These result from queries Qgis issues which result in full table scans (for example counting the number of records, determining that all the geometries are of the same type, determining the geographic extents of the layer). If this is your problem you can use the pgadmin server status tool to see these queries executing.
>
> With version 1.5 there is an option to "Use estimated metadata" when you are creating the Postgres connection.  This avoids some of these time consuming queries, at the expense of not having accurate information on extents - they are taken from the statistics generated when the table is analyzed.  With this option we are getting good response even on a national scale database of millions of records.
>
> Cheers
> Chris
>
> -----Original Message-----
> From: qgis-user-bounces at lists.osgeo.org [mailto:qgis-user-bounces at lists.osgeo.org] On Behalf Of John Abraham
> Sent: Thursday, 10 June 2010 10:56 a.m.
> To: qgis-user at lists.osgeo.org
> Subject: [Qgis-user] Postgis speed with QGIS
>
> Loading and manipulating a PostGIS layer in QGIS is quite slow right now.  I have GIST indexes.  I'm running both QGIS and PostGIS on the same machine.  I'm currently blaming PostgreSQL and am currently exploring configuration and hardware options to make my PostgreSQL installation faster.
>
> Any suggestions on PostgreSQL configuration?  Anything else I should try?
>
> What are others' experiences?  Is there any hope for making my PostGIS layers as fast as my shape file layers?
>
> --
> John Abraham
> jabraham at ucalgary.ca_______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user
> ______________________________________________________________________________________________________
>
> This message contains information, which is confidential and may be subject to legal privilege.
> If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message.
> If you have received this message in error, please notify us immediately (Phone 0800 665 463 or info at linz.govt.nz) and destroy the original message.
> LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.
>
> Thank you.
> ______________________________________________________________________________________________________
>
>

______________________________________________________________________________________________________

This message contains information, which is confidential and may be subject to legal privilege. 
If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message.
If you have received this message in error, please notify us immediately (Phone 0800 665 463 or info at linz.govt.nz) and destroy the original message.
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.

Thank you.
______________________________________________________________________________________________________