Programming discussion
Upgraded a small Postgres box over the weekend with pg_upgrade --link. It came up fine, no errors. Queries that took 20ms now take 8 seconds. Same hardware, same config carried across.
One example does a seq scan on a 40M row table when there's a perfectly good index on the column. No idea why it wouldn't use it.
This browser holds the management token for this post.
Keep this private. Anyone with the token can resolve, edit, or delete this discussion.
Did you run the analyze step? pg_upgrade doesn't bring the planner statistics across, so right now it's guessing. It prints this at the end and everybody misses it.
vacuumdb --all --analyze-in-stages
Did you run the analyze step? pg_upgrade doesn't bring the planner statistics across, so right now it's guessing. It prints this at the end and everybody misses it.
vacuumdb --all --analyze-in-stages
Oh.
Running. There's a line about it in the output I had scrolled past.
The in-stages version does a very rough pass first, so the worst of it clears up fast. Then finer passes. Check pg_stat_user_tables, last_analyze should be getting filled in.
Back to 20ms on most of it. Thanks. Still one report query that's slower than before, 2s where it was 900ms.
EXPLAIN (ANALYZE, BUFFERS) it and paste it. Change the table names if you need to.
Top of it is:
JIT: Functions: 42 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 8.1 ms, Inlining 310.2 ms, Optimization 612.4 ms, Emission 201.9 ms, Total 1132.6 ms
Execution Time: 2041.8 ms
There's your extra second. It has decided the query's expensive enough to compile. Was jit off in your old config, or was it a different package build? Try SET jit = off in the session and run it again.
880ms. Old package was built without LLVM support, turns out. Debian's has it.
You can leave it on and raise jit_above_cost, or turn it off for that role. For a small OLTP box I'd just turn it off.
Off. All good now, marking this one resolved.
Just did this at work and this thread saved me a bad hour. thank you whoever you are
For anyone arriving here now: 18 keeps most planner stats through pg_upgrade. You still want vacuumdb --all --analyze-in-stages --missing-stats-only, the release notes have details.
This discussion is resolved. Its creator can reopen it to accept more replies.