I have a database db1 which works very slow. It executes hard queries with joins few minutes. I created a copy of that database: db2. Then I created and tuned some indexes and that db2 began to work much faster! After that my goal was to tune db1 working fast as db2. I made copy of db2 (pg_dump -Fc db2 > db2.dump) and restore it to new db1. Then I tested it's speed. But the speed of work not the same! The new db1 much slower (as it was before). What's the problem here? Does pg_dump dumps everything inside db? Data, indexes and so on? Please advice.
Asked
Active
Viewed 1,260 times
2 Answers
0
After loading,the statictics tables are still empty (they are not part of the dump). You need to run analyze manually.as explained in the postgres manual
wildplasser
- 43,142
- 8
- 66
- 109
0
Yes pg_dump dump all object definitions, table, view, indexes, type and do on. You have reset the cache and destroy all staistics, so when running the same query in a fresh restore you may encounter bad query times, you can do an ANALYZE to refresh all the stats, but consider that you have now in your cluster db2 + db1 instead of only db1 as before, so the cache is less available for db1, you have to destroy db2 before doing your analyze.
Rodolphe
- 848
- 4
- 15