Progrider

Programming discussion

Board index
Help · ✓ Resolved

Everything slow after pg_upgrade from 16 to 17

Posted by nils (22 posts) #1

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.

13 replies

marta_k (16 posts) #2

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

nils (22 posts) #3

Oh.

nils (22 posts) #4

Running. There's a line about it in the output I had scrolled past.

marta_k (16 posts) #5

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.

nils (22 posts) #6

Back to 20ms on most of it. Thanks. Still one report query that's slower than before, 2s where it was 900ms.

marta_k (16 posts) #7

EXPLAIN (ANALYZE, BUFFERS) it and paste it. Change the table names if you need to.

nils (22 posts) #8

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

marta_k (16 posts) #9

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.

nils (22 posts) #10

880ms. Old package was built without LLVM support, turns out. Debian's has it.

marta_k (16 posts) #11

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.

nils (22 posts) #12

Off. All good now, marking this one resolved.

Anonymous #13

Just did this at work and this thread saved me a bad hour. thank you whoever you are

marta_k (16 posts) #14

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.

Discussion resolved

This discussion is resolved. Its creator can reopen it to accept more replies.