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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
