2

Related to this:PostgreSQL extremely slow after upgrade from 9.3 to 9.4 but trying to understand better...

My queries was slow after upgrading to Postgres Version 11.5 from 10.7 I first ran ANALYZE on the DB but still was slow! so I can VACUUM ANALYZE on the DB, and was still slow...

as a last resort, I am running VACUUM FULL ANALYZE on a table by table basis using psycopg2 with autocommit on (http://initd.org/psycopg/docs/connection.html#connection.autocommit) to be able to run it in psycopg2

one table is taking hours to do ... while I wait, I can't help but wonder what went wrong. The reduction of the query performance was mainly from UPSERT.

1
  • 2
    "one table is taking hours to do ..." - maybe it's waiting for a lock? Did you check pg_stat_activity to see if your vacuum command is active? Do you have connections that are idle in transaction? Those will prevent vacuum from doing its job Oct 29 '19 at 6:55
4

The output of pg_upgrade includes this:

...

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

...

We found horrible performance straight after the upgrade until we ran this, lesson learned to read the output :)

1

The culprit was even with VACUUM FULL ANALYZE, query planner was still not using indexes in many cases.

Solved by forcing SET enable_seqscan = OFF;, after few autovacuums, it re-adjusted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.