From: "Andrus" <eetasoft@online.ee>
Subject: Re: Query runs too long for indexed tables
Date: Wed, 5 Apr 2006 12:39:46 +0300
Organization: Hub.Org Networking Services
Lines: 53
Message-ID: <e1039l$15ie$1@news.hub.org>
References: <e0ui0o$2v0m$2@news.hub.org>
	<1144180128.32266.56.camel@state.g2switchworks.com>
To: pgsql-performance@postgresql.org

> 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.