POSTGIS cursor performance

[jpalmer]

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

[jef]

with r9095 cursors are back to the old behaviour. transactional updates are now down through a separate database connection.

[(none)]

Milestone Version 1.0.0 deleted