Strange behaviour - performance decreases after each TRUNCATE

IVO GELOV wrote:

Hallow.
My name is IVO GELOV. Please excuse my English, it is not my primary language.
I've started to learn PostgreSQL with version 8.1, doing 2 upgrades - to 8.2.4 and now 8.3.1
I consider myself still a novice. Currently I'm porting our small warehouse application from MySQL to Postgre. After
designing tables, indexes and triggers, I wrote several PHP scripts to transfer data from MySQL to Postgre. Since there
are much more checks in my new schema in Postgre, I've able to find many discrepancies inside the old MySQL data.
This "trial and error" process resulted in many restarts of PHP scripts for data transfer. Each of my scripts transfers one
table at a time. It always begins with TRUNCATE CASCADE, and then all the other SQL is between BEGIN/COMMIT,
forming a transaction.
Most of my tables are small - the biggest one is BALANCE with about 840 000 records (all the others are under 150 000).
Each script shows its execution time at the end. I've noticed something strange - first time the construction of
table BALANCE took 419 seconds, and after then each time I run the script - it shows more and more time. I read in documentation,
that indexes may "bloat" - I saw that indexes of several tables (including BALANCE) were 90% fragmented, so I did
REINDEX for the whole database. But the running time of the script for BALANCE still increases - it is now 1190 seconds !
I'm really confused why is this happening. Am I misunderstanding something ? Can anybody help me or just point me ?

You don't need to re-index the indexes, you need to vacuum the tables.

http://www.postgresql.org/docs/current/static/sql-vacuum.html

--
Postgresql & php tutorials
http://www.designmagick.com/

You don't need to re-index the indexes, you need to vacuum the tables.

http://www.postgresql.org/docs/current/static/sql-vacuum.html

My first idea was to throw away table BALANCE and work without it.
I even created a PL/SQL function for real-time calculation of article
balances.
But after some time of thinking I realized that this design will constantly
slow down, year after year - it will have to work with more and more records.
So I rewrote my script for data transfer, using INSERTs with multiple VALUES
and now running time suddenly shortened - it is now 496 seconds for about
837 000 records.
But it is still interesting for me why running time was increasing with each run (it may be still increasing even with the new script).

-----------------------------------------------------------------
пїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅ пїЅпїЅпїЅпїЅ 2008 !!!
http://sportni.bg/euro2008/