[postgis-users] AddGeometryColumn streamlining?

[Charlton Purvis]

Hi, folks:

I am creating a bunch of geometry tables to represent different points
in time.  Please trust that this is a good approach.  However, I'm
running into severe performance problems w/ the AddGeometryColumn
function.

Per evening, I populate the database w/ about 5 million new records, and
these records are assigned to about 300 tables that are created
on-the-fly.

Well, I've streamlined everything I can think of (using COPY, etc.), and
it screams.  All except the AddGeometryColumn which is getting worse and
worse over time.

The part of the function that is unbearably slow is:

select pg_class.oid AS attrelid from pg_class,pg_attribute where relname
=geometry_columns.f_table_name::name and pg_attribute.attrelid =
pg_class.oid and pg_attribute.attname =
geometry_columns.f_geometry_column::name

I wouldn't mind circumventing the AddGeometryColumn function, entirely,
but I need to get the pg_* #'s -- so I'd be back in the same boat again.

As a foolish first-step, I tried to create my own indexes on those pg_*
tables, but, fortunately, they did *not* work.  I read in some pgsql
archives that creating user indexes on system tables was a bad idea.

Anybody got any ideas?  It's only going to get worse.

The above query returns about 550 rows and takes almost 12 seconds to
run.  Multiply that by the number of tables I create on a given night
(300), and that stinks.

Thanks,

Charlton

 
 
 
Charlton Purvis
(803) 777-8858 : voice
(803) 777-3935 : fax
cpurvis at sc.edu
 
Baruch Institute
University of South Carolina
Columbia, SC 29208