[gdal-dev] Pg Table Listing

[Even Rouault]

Le mercredi 18 mars 2015 18:59:28, Paul Ramsey a écrit :
> With 1000 tables, the disparity is 300ms for the join on geometry_columns
> and 30ms for the straight system table query.

On my system, just "select * from geometry_columns" with 1000 tables takes ~ 
800 ms. Digging more into the definition of the view, what is super costly in 
fact is the evaluation of postgis_constraint_dims(), postgis_constraint_srid() 
and postgis_constraint_type(), because each causes a SELECT to be executed for 
each row if some typmod constraints are missing. I guess this could have, at 
worst, quadratic performance performance in case no/little index are 
available.

The following request gives similar information but in ~ 80 ms:

select c.relname, 
       a.attname,
       t.typname,
       postgis_typmod_dims(a.atttypmod) dim,
       postgis_typmod_srid(a.atttypmod) srid,
       postgis_typmod_type(a.atttypmod)::text typ,
       array_agg(s.consrc)::text
FROM pg_class c join pg_attribute a on a.attrelid=c.oid
                join pg_namespace n on c.relnamespace = n.oid
                AND (c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" OR 
c.relkind = 'm'::"char" OR c.relkind = 'f'::"char")
                AND NOT ( n.nspname = 'public' AND c.relname = 
'raster_columns' )
                join pg_type t on a.atttypid = t.oid and (t.typname = 
'geometry'::name OR t.typname = 'geography'::name)
                left join pg_constraint s on s.connamespace = n.oid AND 
s.conrelid = c.oid
                          AND a.attnum = ANY (s.conkey)
                          AND (s.consrc LIKE '%geometrytype(% = %' OR s.consrc 
LIKE '%ndims(% = %' OR s.consrc LIKE '%srid(% = %')
GROUP BY c.relname, a.attname, t.typname, dim, srid, typ, c.oid
ORDER BY c.oid

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com