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 ([email protected]) 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 ([email protected]) > 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 ([email protected]) 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 > > > > > > > > > _______________________________________________ gdal-dev mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/gdal-dev
