From: Andrus <eetasoft@online.ee>
To: pgsql-performance@postgresql.org
Subject: Re: Query runs too long for indexed tables
Date: Wed, 5 Apr 2006 12:39:46 +0300
Message-ID: <e1039l$15ie$1@news.hub.org> (raw)
References: <e0ui0o$2v0m$2@news.hub.org>
<1144180128.32266.56.camel@state.g2switchworks.com>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: eetasoft@online.ee
Subject: Re: Query runs too long for indexed tables
In-Reply-To: <e1039l$15ie$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox