PostgreSQL pg_upgrade –link mode performance issue after upgrade

postgresql

Trying to do PostgreSQL upgrade from 9.0 to 9.3. I'm using pg_upgrade with the --link mode since the downtime is very minimal compared to other options like pg_dumpall and pg_upgrade without using --link.

After upgrading and executing an SQL-statement to check execution-time. It is taking nearly 30 minutes the 1st time and for the 2nd time onwards, it is taking 15 mins. The same query executed in 9.0 is not even taking a full 45 seconds.

No of Rows : 150000 records
Using same postgresql.conf file
vaccumdb all : done after upgrade

Is there any thing else I need to configure, to improve the performance?

It doesn't state anywhere in the documentation that pg_upgrade --link will degrade performance.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID for new cluster                 ok
Deleting files from new pg_multixact/offsets                ok
Setting oldest multixact ID on new cluster                  ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Adding support functions to new cluster                     ok
Restoring database schemas in the new cluster

mydatabase

Setting minmxid counter in new cluster                      ok
Removing support functions from new cluster                 ok
Adding ".old" suffix to old global/pg_control               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from /opt/postgres/9.0/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    delete_old_cluster.sh

I got a message like the above, but I couldn't find such files in the postgres folder to execute.

I am using postgresql 9.3.5 from openscg community

**postgresql.conf changes**

shared_buffers = 4GB
work_mem = 128MB
maintenance_work_mem = 256MB
effective_cache_size = 12GB

Best Answer

After a pg_upgrade all your optimizer statistics are gone. You need to ANALYZE all of your tables. This is true regardless of whether or not you use --link.