CODEWITHSUNDEEP
postgresqlupgrade
sebbarg
sebbarg

Reputation: 131

Massive performance degradation when upgrading PostgreSQL 9.6.5 to 10.1

When I upgrade my database from PostgreSQL 9.6.5 to PostgreSQL 10.1 using pg_upgrade, some of my queries involving multiple tables suffer a massive performance degradation.

Running exactly the same query on exactly the same data with exactly the same indexes in 10.1 takes around 17 seconds compared to around 0.1 second in 9.6.

Looking at the EXPLAIN plan for the query, I can see that the plan between the two versions differs a lot - especially for a couple of sub-selects.

9.6 uses indexes a lot more than 10.1. 10.1 seems to favor full table scans over the indexes - even for tables with close to 1M records.

I have verified that the indexes haven't been "lost" during the upgrade. Also if I query the tables individually, the indexes are used.

Any insights?

Thanks, Sebastian

Upvotes: 1

Views: 1073

Answers (2)

Emmet O'Grady
Emmet O'Grady

Reputation: 269

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 :)

Upvotes: 1

sebbarg
sebbarg

Reputation: 131

Solved by doing pg_dump/pg_restore instead of pg_upgrade. Performance and query plans are back to normal.

Upvotes: 1

Related Questions