ogr2ogr slow query for large number of schemas

[ktraff]

We use ogr2ogr to upload shapefiles to our PostgreSQL database using the following command:

Error: Failed to load processor bash

No macro or processor named 'bash' found

Running this command generates a query (from ogrpgdatasource.cpp):

DECLARE mycursor CURSOR for
SELECT c.relname, n.nspname, c.relkind, g.f_geometry_column, g.type, g.coord_dimension, g.srid, 1, c.oid as oid, a.attnum as attnum
FROM pg_class c, pg_namespace n, geometry_columns g, pg_attribute a
WHERE (c.relkind in ('r','v') AND c.relname !~ '^pg_'
    AND c.relnamespace=n.oid AND c.relname::TEXT = g.f_table_name::TEXT
    AND n.nspname = g.f_table_schema AND a.attname = g.f_geometry_column
    AND a.attrelid = c.oid)
UNION SELECT c.relname, n.nspname, c.relkind, g.f_geography_column, g.type, g.coord_dimension, g.srid, 2, c.oid as oid, a.attnum as attnum
FROM pg_class c, pg_namespace n, geography_columns g, pg_attribute a
WHERE (c.relkind in ('r','v') AND c.relname !~ '^pg_'
    AND c.relnamespace=n.oid AND c.relname::TEXT = g.f_table_name::TEXT
    AND n.nspname = g.f_table_schema AND a.attname = g.f_geography_column
    AND a.attrelid = c.oid)
ORDER BY oid, attnum;
FETCH ALL in mycursor;

On development servers, where the number of schemas and tables are low, this query runs in less than 1 second. However, our beta and production databases have 925 users and each user has their own schema with about 5-15 tables in each. The query takes about 34 seconds and generates 13,331 rows.

Are there some simple optimizations that could be made to improve the performance of this query?

[Even Rouault]

Questions should rather be directed to the gdal-dev mailing list. You can either restrict to specific schemas with the schemas= option, or to specific tables with the tables= option. See ​http://gdal.org/drv_pg_advanced.html