QGIS » QGIS Application

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');