3

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.

Improve this question
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 Commented Oct 29, 2019 at 6:55

2 Answers 2

Reset to default
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 :)

Improve this answer
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.