[gdal-dev] Pg Table Listing

[Paul Ramsey]

On March 18, 2015 at 9:53:58 AM, Even Rouault (even.rouault at spatialys.com) wrote:
>  
> 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 upside of the current approach is that it’s nicely invariant across the versions of PostGIS. However, having two versions for performance reasons probably would be nice. If we could strip down the columns returned, that would make it even slicker. What’s the minimum columns needed at this stage in the driver setup?

There’s also a couple new relation types, ‘m’ for materialized views and ‘f’ for… gah! I don’t know. I’m not sure whether the OGR driver should consider a materialized view a view or a table… probably a table, since it has the same performance characteristics?

> I
> believe that could be reasonably established as conservative minimum versions.
> Any people using more ancient versions ?

Actually, I think that the system table usage is not so crazy that it wouldn’t go back as far as 8.0, and the introduction of schemas (pg_namespace). I don’t see anything else we’re using that isn’t as old as the stones.

p.