Feature request #1175

POSTGIS cursor performance

Added by Jeremy Palmer about 12 years ago. Updated about 11 years ago.

Assignee:Jürgen Fischer
Category:Data Provider
Pull Request or Patch supplied: Resolution:fixed
Easy fix?:No Copied to github as #:11235


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


#1 Updated by Jürgen Fischer about 12 years ago

  • Resolution set to fixed
  • Status changed from Open to Closed

with f2cdcca6 (SVN r9096) cursors are back to the old behaviour. transactional updates are now down through a separate database connection.

#2 Updated by Anonymous about 11 years ago

Milestone Version 1.0.0 deleted

Also available in: Atom PDF