[QGIS-trac] [Quantum GIS] #1175: POSTGIS cursor performance

[Quantum GIS]

#1175: POSTGIS cursor performance
-----------------------------------------------+----------------------------
   Reporter:  jpalmer                          |              Owner:  jef          
       Type:  enhancement                      |             Status:  new          
   Priority:  minor: annoyance or enhancement  |          Milestone:  Version 1.0.0
  Component:  Data Provider                    |            Version:  0.11.0       
   Keywords:  POSTGIS performance              |   Platform_version:               
   Platform:  All                              |           Must_fix:  Yes          
Status_info:  0                                |  
-----------------------------------------------+----------------------------
 One of the great features that has returned to QGIS is incremental
 rendering of layers and the ability to cancel this rendering. This is
 particularly useful when dealing with large POSTGIS vector layers (i.e.
 millions of features).

 However in the latest version of QGIS I have noticed that there is a large
 delay before features are returned from the POSTGIS provider after each
 viewport change. I think I've tracked this down to way cursors are
 declared for the selection of rows from the database.  Currently in 0.11
 the "with hold" cursor functionality is used. Looking at the postgres
 documentation this means that the backend executes the whole query before
 allowing any rows to be fetched from the cursor. I've noticed that the
 full execution of large layers (or layers with large geometries) in
 POSTGIS can take quite a long time under certain circumstances. These
 circumstances tend to be due to the:
 •       conversion of lots of complex geometries to WKB (i.e. asbinary)
 •       spatial windowing of a layer if the backend planner decides not to
 use the available spatial index.

 I’ve run a few postgres backend tests with cursors using "without hold"
 functionality and declaring a cursor and fetching features in batches of
 10000 is very quick on my system. The only problem with using this
 functionality is that the cursor must be used within a transaction. See
 [http://www.postgresql.org/docs/8.3/interactive/sql-declare.html]. I have
 not looked at the POSTGIS provider code, so there may be implications to
 using this method.


 QGIS version: 0.11 WinXP SP2
 Postgresql version: PostgreSQL 8.1.2 on i686-pc-linux-gnu
 POSTGIS version: POSTGIS="1.1.2" GEOS="2.2.1-CAPI-1.0.1" PROJ="Rel. 4.4.9,
 29 Oct 2004" USE_STATS

-- 
Ticket URL: <http://trac.osgeo.org/qgis/ticket/1175>
Quantum GIS <http://qgis.org>
Quantum GIS is an Open Source GIS viewer/editor supporting OGR, PostGIS, and GRASS formats