PostgreSQL performance issues

Hi all, I'm working for a brazillian company developing and maintaining a ERP sw that uses PostgreSQL as it main OLTP
databasesystem. We're just to start the migration to IBM DB2 because of many performance issues. I searched the
solutionfor these performance problems, and can't find anything on the other web foruns. I'll put them in this post as
topics,but first I'll describe how's the ERP database's schema: - More than 200 tables; - About 10 tables with about
10,000transactions and 15,000 new rows per day; - These 10 tables has at least 12 table indexes and 3 triggers; - Many
ofour customer servers uses AMD64 processors running Red Hat Enterprise (with EXT3), but we have some using Xeon
processorsand Windows 2003 Server (NTFS), and the issues still the same; - All servers have at least 1 Gb of dedicated
RAM,with no virtualization; - All servers uses at least 2 disks on RAID 0 (Ultra-SCSI disks); - Database encoding:
LATIN1. <b>The issue topics:</b> 1) As the database grows on our customers, lower performance occurs. After one week of
use,the I/O on database is extremely high. It appears that VACUUM FULL and/or VACUUM ANALYZE doesn't work on this
databases.2) We have a very complex view mount on other views. When we cancel a simple SELECT on this top-level view
(expectingreturn a max. of 100 rows for example) the PostgreSQL process starts a infinite loop (we left more than 4
daysand the loop doesn't stops), using 100% of all processors on the server. 3) On these servers, the disk usage grows
verysmall than the records loaded into database. For example, after restoring a backup, the database DIR have about 40
Gb(with all indexes created). After one week of use, and about 500,000 new records on tables, the database size grows
toabout 42 Gb, but on Windows 2003 Server we can see the high fragmentation of disk (maybe on linux this occurs too).
4)VACUUM FULL and/or VACUUM ANALYZE appears to doesn't work perfectly. <b>The temporary (but extensive) solution:</b> I
haveseem that one action could solve this problems for a short time. It is as follows: 1) Create a database dump into a
.SQLplain text file; 2) Drop the OTLP original database; 3) Create a new database using the original name; 4) Restores
the.SQL file with psql. The cost of use PostgreSQL database on our sw came to a very high levels, so we're evaluating
thesame database schema and data on other databases as IBM DB2 9, and these issues doesn't work. However, we need solve
thisproblems on PgSQL, as exists many customers who will not migrate to DB2. Anyone can help me? <br /><hr align="left"
width="300"/> View this message in context: <a
href="http://www.nabble.com/PostgreSQL-performance-issues-tf4670379.html#a13341797">PostgreSQLperformance issues</a><br
/>Sent from the <a href="http://www.nabble.com/PostgreSQL---hackers-f780.html">PostgreSQL - hackers mailing list
archive</a>at Nabble.com.<br />