Feature request #581

POSTGIS Layer feature count methodology

Added by Jeremy Palmer over 13 years ago. Updated over 10 years ago.

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

Description

Currently in the win32 0.8 release (and any release that does not define the POSTGRESQL_THREADS macro) the POSTGIS data provider always does a full table count select. This is very annoying to the user who might have to wait some time when added a new layer that has millions of features.

A better solution is to use the pg_catalog information when the layer is first added then complete a full table count if required for later requirement. The layer properties could then just show that the count was estimated.

I believe this is the intended scenario if POSTGRESQL_THREADS is defined. So maybe the win32 version just needs to be compiled with thread support.

Also noted is that in the "thread section" current query is:

"select reltuples from pg_catalog.pg_class where relname = '" +
tableName + "'";

This should be changed to include the use of schemas:

QString sql = ""
"SELECT cls.reltuples "
"FROM pg_catalog.pg_class cls, "
" pg_catalog.pg_namespace nsp "
"WHERE cls.relname = '" + mTableName + "'"
"AND cls.relnamespace = nsp.oid "
"AND nsp.nspname = '" + mSchemaName + "'";

History

#1 Updated by Gavin Macaulay - over 13 years ago

Unfortunately, many parts of qgis require the feature count to be accurate at all times, and if not causes subtle errors in displaying feature and attribute data. For these reasons the POSTGRESQL_THREADS code has not been used since soon after it was written.

#2 Updated by anonymous - over 13 years ago

I think from a user’s point of view it is very annoying having to wait a long period just to load a layer - especially if it is just to view the feature and info features.

I believe the user would be ok with layer initially loading the layer with an estimated count and then having an immediate thread spawned off in the background to complete the full count.

Correct me if I'm wrong but aren’t feature retrieved independent of the layer count i.e. "getNextFeature". So the count should not apply in drawing and feature info functions.

Maybe these subtle areas of the application (i.e feature attribute table) could be explained (locked down?) to the user while the full count is carried out. I know this means more complication to the application and more work for the developers – but it would be very useful to users of large POSTGIS layers 

#3 Updated by Maciej Sieczka - over 12 years ago

How does the issue look in QGIS 0.11? You can obtain it's Windows installer preview at:

http://gigamaildownload.rossoalice.alice.it/download/download.aspx?AttachmentID=2716cab8-980a-45c8-9e9c-8ff538f973e2&MessageID=4602d09e-2a74-48e1-a167-644cb19b13fb

#4 Updated by Jeremy Palmer over 12 years ago

I just tested the latest QGIS 0.11 win32 preview. The count method is still as reported. i.e "select count(*) from table".

#5 Updated by Maciej Sieczka - over 12 years ago

Jürgen,

CCing you a PostGIS expert.

#6 Updated by Jürgen Fischer over 10 years ago

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

fixed in 4852ceb1 (SVN r13045). See also #2460.

Also available in: Atom PDF