[postgis-users] OT: moving my access data into postgis

[Dave Blasby]

Tyler Mitchell wrote:
> So far so good.  It's crunching away right now.  I'm getting about 100
> records per second during the migration it seems. Do you have a gut feel
> for speed differences between the migration utility and other methods?  I
> assume the postgres COPY FROM would be the quickest, ...


100 records/second seems very slow - I would expect speeds in the
1000s/second.

The fastest way is to use the "COPY" syntax, the 2nd fastest would be a 
prepared statement in a transaction block, 3rd would be simple inserts
in a transaction
block, and 4th would be simple insert statements.

Putting transactions blocks around blocks of inserts makes things *WAY*
faster.

BEGIN;
s1;
s2;
s3;
...
s10000;
COMMIT;

Also, dont put an index on a table until *after* its been populated -
otherwise you'll be updating the index everytime you do an insert. 
That'll make something that's O(N) to something thats O(N) + O(N log N).

When I'm sticking arbitrary data in postgresql I usually make a
mini-script that makes 'INSERT ...;' statements.  I use a text editor
and put "BEGIN;" at the top and "COMMIT;" at the bottom, then :

cat <file.sql> | psql <db>

Then I'll build the indexes.

dave