PostgreSQL pg_upgrade --link mode performance issue after upgrade

Question

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

Answer 1

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.

Answer 2

It depends on where you ran your pg_upgrade, but when it has completed the process it will drop log files as well as two shell scripts in that same current working directory (not the postgres directory).

delete_old_cluster.sh
analyze_new_cluster.sh

You want to start up your new postgres cluster with pg_ctl and then run the analyze_new_cluster.sh. The reason your queries are so slow is that the indices are not the least optimal after the transfer. This process can take many hours to complete if you have a large database. Mine of 6.5 TB took 13 hours. They also suggest a faster mode just to get you going (you can then let autovacuum take care of the cleanup or do some followup analyze operations later):

vacuumdb --all --analyze-only

You will also want to revisit your new postgresql.conf file to make sure that your tuning parameters carry over, at least as many as are applicable. The params do change gradually with each new major version.