[gdal-dev] Pg Table Listing

[Even Rouault]

Hi Paul,

> We noticed that in databases with lots of tables, OGR throws some pretty
> crazy SQL at the database, and things get slow and the source of the problem
> seems to be here:
>
>
http://trac.osgeo.org/gdal/browser/trunk/gdal/ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp#L851
>
> Which in addition to being a big query on system tables joins back to
> geometry_columns, which is, itself now a big query on system tables. I think
> that’s the main problem.

It certainly is. I think the performance issue appeared with PostGIS 2.0 when
geometry_columns has become a view.

> Probably either the query should just use the
> system tables alone, and take advantage of the PostGIS geometry_columns view
> code as a guide
>
> http://trac.osgeo.org/postgis/browser/trunk/postgis/postgis.sql.in#L4980

The query also tries to get geospatial information (geometry column name, SRID,
geometry type), but we don't necessarily need it when establishing the layer
list, as it can also be resolved lazily later in
OGRPGTableLayer::ReadTableDefinition(), so if retrieving only table and schema
name of views and tables with geometry/geography columns is faster than
retrieving them + geometry column name, SRID and geometry type, then it might be
a potential optimization.

>
> The only trick is, I guess, figuring out which versions of PgSQL particular
> system table code will work against. The main dividing line for PostGIS is
> between versions 1 and 2.  What’s the minimum supported version of PgSQL
> for OGR?

That is a good question. In theory the code should be able to support PostgreSQL
7.4 and prior... but I doubt it has been tested in a while, so it is likely
broken and we could probably do some spring cleanup. I personnaly have an old
Ubuntu 10.04 with PostgreSQL 8.4 + PostGIS 1.5 that works with current OGR. I
believe that could be reasonably established as conservative minimum versions.
Any people using more ancient versions ?

Even

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