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/
