[gdal-dev] Pg Table Listing

[Paul Ramsey]

OK, yeah, even w/o populating my database with 1000 tables, I can see the timing difference between joining with geometry_columns, and just hitting system tables directly


-- joined with geometry columns (14ms)
SELECT 
c.relname, n.nspname, c.relkind, 
g.f_geometry_column, g.type, g.coord_dimension, g.srid, 
1, a.attnotnull, c.oid as oid, a.attnum as attnum 
FROM geometry_columns g 
JOIN pg_class c ON g.f_table_name = c.relname
JOIN pg_namespace n ON g.f_table_schema = n.nspname AND c.relnamespace = n.oid 
JOIN pg_attribute a ON g.f_geometry_column = a.attname AND c.oid = a.attrelid
WHERE c.relkind in ('r','v','m','f') 
AND n.nspname != 'pg_catalog' ;


-- without geometry columns (2ms)

SELECT 
c.relname, n.nspname, c.relkind, a.attname, t.typname
FROM pg_attribute a
JOIN pg_type t ON a.atttypid = t.oid
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid 
WHERE t.typname = 'geometry'::name
AND c.relkind in ('r'::"char", 'v'::"char", 'm'::"char", 'f'::"char") 
AND n.nspname != 'pg_catalog' ;


So, seems a worthwhile effort,
P 

--  
http://postgis.net  
http://cleverelephant.ca


On March 18, 2015 at 10:19:16 AM, Paul Ramsey (pramsey at cleverelephant.ca) wrote:
> Yes, that’s what ‘f’ is.
> It could just be that on a database with N-hundred tables the query would be slow regardless.  
> Something I’ll do a quick test of.
> I’m writing up a few variants to go into those slots for different versions.
> P
>  
>  
> --
> http://postgis.net
> http://cleverelephant.ca
>  
>  
> On March 18, 2015 at 10:09:26 AM, Sandro Santilli (strk at keybit.net) wrote:
> > On Wed, Mar 18, 2015 at 09:58:20AM -0700, Paul Ramsey wrote:
> >
> > > There’s also a couple new relation types, ‘m’ for materialized
> > > views and ‘f’ for… gah! I don’t know.
> >
> > I think 'f' is for foreign data tables.
> >
> > It's surprising that joining a view on catalogue tables considerably
> > slows down. Shouldn't the optimizer be able to rewrite the query
> > to skip redundancies ?
> >
> > --strk;
> >
> > () Free GIS & Flash consultant/developer
> > /\ http://strk.keybit.net/services.html
> >
>  
>