Re: 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/