Feature request #8216

When slow, "Add PostGIS Tables" dialog needs to tell me which tables/views are taking too long to process

Added by Aren Cambre over 11 years ago. Updated over 5 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/PostGIS
Pull Request or Patch supplied:No Resolution:
Easy fix?:No Copied to github as #:17030

Description

The Add PostGIS Tables dialog takes far too long to be ready. Part of my problem is that I have several views, so it is apparently having to process each view.

In my case, it is taking over 4 minutes to process.

I had to dig around to figure out which of my several views is too slow. QGIS should give me some clear indicator, if I need it, of slow-loading views.

History

#1 Updated by Sandro Santilli about 11 years ago

  • Operating System changed from Windows 7 x64 to Windows 7 x64 Linux

I've a similar case and found out that dropping the "WHERE <geomcol> IS NOT NULL" portion from the query retriving estimated metadata (those with LIMIT 100 at the end) makes some query time go down from 5 seconds to 0.05 seconds (2 order of magnitude faster!).

Especially when "Use estimated metadata" is checked in the options (Do you have that, Aren?) I think the "not null" condition should be dropped. It helps the estimator finds a much faster path.

#2 Updated by Sandro Santilli about 11 years ago

Jurgen committed a fix for the "NOT NULL" condition (thank you!):
#4ea85fe50a0e2390917347b7218de13e6c57ed26

Aren: does that help you ?

See also #8730, which may help

#3 Updated by Paolo Cavallini almost 11 years ago

  • Target version changed from Version 2.0.0 to Future Release - High Priority

#4 Updated by Giovanni Manghi over 7 years ago

  • Easy fix? set to No

#5 Updated by Regis Haubourg over 5 years ago

  • Status changed from Open to Closed
  • Description updated (diff)

Closing, please reopen if necessary. For the record, if checkbox "use geometry columns table" and "use estimated metadata" are not checked, this will actually query the features of each layer to compute unique id, geometrytype and extent... which can be a database killer. Don't forget to use typemod in postgis view to explicitly declare those geometry types and projections. Something like geom::Geometry(Polygon, 2154) for instance..

Also available in: Atom PDF