Bug report #4788
Add PostGIS layer - connect: Slow performance (>2 minutes) when geometry_columns or geography_columns table has many records
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');
History
#1 Updated by Kirill Mueller over 12 years ago
See http://archives.postgresql.org/pgsql-general/2012-01/msg00306.php . Patch attached.
#2 Updated by Jürgen Fischer over 12 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 12 years ago
looks like a good candidate for backporting to 1.7 and 1.8
#4 Updated by Kirill Mueller over 12 years ago
Actually, a backport would be nice :-)
#5 Updated by Sandro Santilli over 12 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 12 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 12 years ago
- Target version changed from Version 1.7.4 to Version 1.8.0
#8 Updated by Paolo Cavallini about 12 years ago
- Target version changed from Version 1.8.0 to Version 2.0.0
#9 Updated by Jürgen Fischer about 12 years ago
- Status changed from Reopened to Closed
no backports to 1.8 - closing.