> Let me guess, you've updated it a lot and aren't familiar with Vacuum?
>
> run a vacuum full on your database. schedule a vacuum (plain one) to
> run every so often (hours or days are a good interval for most folks)
>
> If that's NOT your problem, then please, let us know.
Scot, thank you. Excellent. If database is created and VACUUM ANALYZE is
issued, this query runs fast.
However, I need to speed up it during running script.
This is a database creation script. Script does the following:
1. CREATE DATABASE foo;
2. START TRANSACTION;
3. Create 145 tables with primary keys. Add data to those tables.
4. Create some additional indexes
5. ANALYZE
6. Clear bad bad foreign keys fields using commands like
UPDATE firma1.rid SET toode=NULL
WHERE toode IS NOT NULL AND
toode NOT IN (SELECT TOODE FROM firma1.TOODE);
7. Create foreign key references
8. COMMIT
This script runs about 1 hour in modern server with fsync off.
Largest table has 100000 records, few other tables have 15000 records and
remaining have fewer records.
How to speed this up ?
Is'nt running ANALYZE sufficient to speed up foreign key clearing ?
It seems that ANALYZE does'nt work. Should I isse COMMIT before running
ANALYZE or issue more commits?
Server has 4 GB RAM
postgres.conf file is default from 8.1.3 window zip file except the
following settings are added to end:
fsync=off
shared_buffers = 30000
redirect_stderr = on
log_min_error_statement = error
autovacuum = on
... also 2 stats settings from aurtovacuur
max_fsm_pages = 30000
Andrus.