Bug report #4788

Add PostGIS layer - connect: Slow performance (>2 minutes) when geometry_columns or geography_columns table has many records

Added by Kirill Mueller over 7 years ago. Updated about 7 years ago.

Status:Closed
Priority:Normal
Assignee:Tim Sutton
Category:-
Affected QGIS version:1.7.3 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed
Crashes QGIS or corrupts data:No Copied to github as #:14643

Description

Connecting to a Postgres database that has many entries in the geometry_columns or geography_columns table is horribly slow. In our case, with more than 10000 tables with a geometry column, it takes slightly less than three minutes.

There are no performance problems when choosing "Only look in the geometry_columns".

The reason is the following query, issued by QgsPostgresConn::getTableInfo(), line 392 ff. (89d22a65b630f80047f96d1c36b5885f26cc88ee):

SELECT pg_class.relname,pg_namespace.nspname,pg_attribute.attname,pg_class.relkind
FROM pg_attribute,pg_class,pg_namespace
WHERE pg_namespace.oid=pg_class.relnamespace AND pg_attribute.attrelid = pg_class.oid
AND ( EXISTS (SELECT * FROM pg_type WHERE pg_type.oid=pg_attribute.atttypid AND pg_type.typname IN ('geometry','geography','topogeometry')) OR pg_attribute.atttypid IN (SELECT oid FROM pg_type a WHERE EXISTS (SELECT * FROM pg_type b WHERE a.typbasetype=b.oid AND b.typname IN ('geometry','geography','topogeometry'))))
AND has_schema_privilege( pg_namespace.nspname, 'usage' )
AND has_table_privilege( '"' || pg_namespace.nspname || '"."' || pg_class.relname || '"', 'select' )
AND NOT EXISTS (SELECT * FROM geometry_columns WHERE pg_namespace.nspname=f_table_schema AND pg_class.relname=f_table_name)
AND pg_class.relkind IN ('v','r');

When leaving out the last "AND NOT EXISTS..." part, the query finishes in no time. I have attached the output of EXPLAIN ANALYZE -- if I understand the execution tree correctly, the time is burnt in repeated sequential scans of the geometry_columns table (line 38).? Rewriting the "AND NOT EXISTS" part using WITH solves the performance issues here, but works only from Postgres 8.4. I have mailed the pqsql-general list for advice on how to speed up this query for older versions, see the discussion at http://archives.postgresql.org/pgsql-general/2012-01/msg00265.php .

Here's the improved version of the problematic query:

WITH forbidden_geometry_columns AS (
SELECT
DISTINCT
pg_class.oid AS id
FROM pg_class,pg_namespace,geometry_columns
WHERE pg_namespace.oid=pg_class.relnamespace
AND pg_namespace.nspname=f_table_schema AND pg_class.relname=f_table_name
)
SELECT pg_class.relname,pg_namespace.nspname,pg_attribute.attname,pg_class.relkind
FROM pg_attribute,pg_class,pg_namespace
WHERE pg_namespace.oid=pg_class.relnamespace AND pg_attribute.attrelid = pg_class.oid
AND ( EXISTS (SELECT * FROM pg_type WHERE pg_type.oid=pg_attribute.atttypid AND pg_type.typname IN ('geometry','geography','topogeometry')) OR pg_attribute.atttypid IN (SELECT oid FROM pg_type a WHERE EXISTS (SELECT * FROM pg_type b WHERE a.typbasetype=b.oid AND b.typname IN ('geometry','geography','topogeometry'))))
AND has_schema_privilege( pg_namespace.nspname, 'usage' )
AND has_table_privilege( '"' || pg_namespace.nspname || '"."' || pg_class.relname || '"', 'select' )
AND NOT EXISTS (SELECT * FROM forbidden_geometry_columns WHERE forbidden_geometry_columns.id=pg_class.oid)
AND pg_class.relkind IN ('v','r');

qgis_list_geometry_columns.txt Magnifier - Execution plan, result of EXPLAIN ANALYZE of the problematic query (5.96 KB) Kirill Mueller, 2012-01-11 03:48 PM

0001-improve-query-that-lists-all-PostGIS-columns-fix-478.patch Magnifier - Patch (1.52 KB) Kirill Mueller, 2012-01-11 04:25 PM

History

#2 Updated by Jürgen Fischer over 7 years ago

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

Kirill Mueller wrote:

See http://archives.postgresql.org/pgsql-general/2012-01/msg00306.php . Patch attached.

Nice. Thanks. Applied in a646ff6f.

#3 Updated by Sandro Santilli over 7 years ago

looks like a good candidate for backporting to 1.7 and 1.8

#4 Updated by Kirill Mueller over 7 years ago

Actually, a backport would be nice :-)

#5 Updated by Sandro Santilli over 7 years ago

  • Status changed from Closed to Reopened
  • Affected QGIS version changed from master to 1.7.3
  • Target version set to Version 1.7.4

Backport in 1.7 is c6eb2364019f0ee2af1949249dcdb79c3cae3b9e (not referencing this ticket but only the commit in master and with a missing letter, ouch).

Still needs to be backported to 1.8

#6 Updated by Sandro Santilli over 7 years ago

  • Assignee set to Tim Sutton

Tim: I've been told you're the one taking care of backports to 1.8 :)

#7 Updated by Paolo Cavallini over 7 years ago

  • Target version changed from Version 1.7.4 to Version 1.8.0

#8 Updated by Paolo Cavallini about 7 years ago

  • Target version changed from Version 1.8.0 to Version 2.0.0

#9 Updated by Jürgen Fischer about 7 years ago

  • Status changed from Reopened to Closed

no backports to 1.8 - closing.

Also available in: Atom PDF