[gdal-dev] Pg Table Listing

[Paul Ramsey]

With 1000 tables, the disparity is 300ms for the join on geometry_columns and 30ms for the straight system table query.

Still, not something that should be noticeable (really) in the context of a batch query. I guess actually it would be twice as big, since the OGR query runs twice (once for geometry, once for geography) in the UNION. So nearing half a  second. But still… in the context of a batch process… hm.

P.


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


On March 18, 2015 at 10:28:58 AM, Paul Ramsey (pramsey at cleverelephant.ca) wrote:
> 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
> > >
> >
> >
>  
>