Postgres Pro
Downloads
Home   >   mailing lists

Thread: Performance Problems

Performance Problems

From
Héctor Iturre
Date:
Hi,  I have performance problems with a huge database
(there a 2 tables with 40 millions of records) and
many users doing updates and queries on it. I 've
perform severals VACUMM on the database with poor
results.   Each table have an unique index and I added other
indexes to improve the performance. But when there are
inserts (not too many) the performance fall.  What can I do to improve the performace? I hear any
opinion.
                          Hector Iturre



Ahora podés usar Yahoo! Messenger desde tu celular. Aprendé cómo hacerlo en Yahoo! Móvil:
http://ar.mobile.yahoo.com/sms.html

Re: Performance Problems

From
Philip Warner
Date:
At 09:56 AM 12/12/2002 -0300, Héctor Iturre wrote:
>  I 've
>perform severals VACUMM on the database with poor
>results.

Have you done an ANALYZE?

Which version of PG are you using?

Can you send output from VACUUM VERBOSE ?



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: Performance Problems

From
Christoph Haller
Date:
>
>    I have performance problems with a huge database
> (there a 2 tables with 40 millions of records) and
> many users doing updates and queries on it. I 've
> perform severals VACUMM on the database with poor
> results.
>    Each table have an unique index and I added other
> indexes to improve the performance. But when there are
> inserts (not too many) the performance fall.
>    What can I do to improve the performace? I hear any
> opinion.
>
Bear in mind, every index has to be updated when records
are inserted. An unique one makes it even worse, because
a search for duplicates must be performed.
You may give the idea some thought, if this index really
has to be unique. I sometimes use a 'timestamp-insert'
column to allow duplicates and retrieve unique entries by
a SELECT DISTINCT ON construct.

Regards, Christoph