[Gdal-dev] RE: PostGIS Driver Performance

[Craig Miller]

In case it's useful, here is the SQL I used to do this when I was working on
a PostGIS OLEDB provider proof of concept last fall. 

        char * SQL_SELECT_TABLES = "SELECT a.relname AS
Name,obj_description(oid, 'pg_class') AS Description FROM pg_class a,
pg_user b WHERE ( relkind = 'r') and relname !~ '^pg_' AND relname !~
'^sql_' AND relname !~ '^xin[vx][0-9]+' AND b.usesysid = a.relowner AND NOT
(EXISTS (SELECT viewname FROM pg_views WHERE viewname=a.relname))";

--Craig

-----Original Message-----
From: Oleg Semykin [mailto:oss-dev at rambler.ru] 
Sent: Thursday, July 28, 2005 5:48 AM
To: Craig Miller; gdal-dev
Subject: PostGIS Driver Performance

Hi Craig!

Why the PostgreSQL driver loads all non-system tables from the database 
as layers? When we have a lot of tables this results in too big 
overhead. When we are working with PostGIS all layers are listed in the 
geometry_columns table and we can use it to prepare a list of tables to 
load. I can modify the driver to load only layers from the 
geometry_columns table when we have PostGIS and fallback to the old 
behaviour when we have plain PostgreSQL.

Oleg.