{"thread":{"id":"qzWUZdBwIT4X","title":"Everything slow after pg_upgrade from 16 to 17","content":"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.\n\nOne 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.","category":"help","status":"resolved","author":"nils","createdAt":"2026-10-02T10:03:50.234Z","updatedAt":"2026-11-04T21:06:50.234Z","expiresAt":null,"replyCount":13},"replies":[{"id":"CaSHtevWdRA3","content":"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.\n\nvacuumdb --all --analyze-in-stages","author":"marta_k","createdAt":"2026-10-02T10:13:50.234Z"},{"id":"w2dACLYQ6pPC","content":"Oh.","author":"nils","createdAt":"2026-10-02T10:21:50.234Z"},{"id":"y8DvxNafZ-JH","content":"Running. There's a line about it in the output I had scrolled past.","author":"nils","createdAt":"2026-10-02T10:25:50.234Z"},{"id":"vRbjtyiTbhDc","content":"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.","author":"marta_k","createdAt":"2026-10-02T10:35:50.234Z"},{"id":"2AQ_wJzyyy3Z","content":"Back to 20ms on most of it. Thanks. Still one report query that's slower than before, 2s where it was 900ms.","author":"nils","createdAt":"2026-10-02T11:05:50.234Z"},{"id":"dfUlgVweMr82","content":"EXPLAIN (ANALYZE, BUFFERS) it and paste it. Change the table names if you need to.","author":"marta_k","createdAt":"2026-10-02T11:27:50.234Z"},{"id":"p5NY_TWD_rHj","content":"Top of it is:\n\nJIT:\n  Functions: 42\n  Options: Inlining true, Optimization true, Expressions true, Deforming true\n  Timing: Generation 8.1 ms, Inlining 310.2 ms, Optimization 612.4 ms, Emission 201.9 ms, Total 1132.6 ms\n\nExecution Time: 2041.8 ms","author":"nils","createdAt":"2026-10-02T12:51:50.234Z"},{"id":"U8i19TE2dw-6","content":"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.","author":"marta_k","createdAt":"2026-10-02T13:02:50.234Z"},{"id":"3gI0Xqga0gs3","content":"880ms. Old package was built without LLVM support, turns out. Debian's has it.","author":"nils","createdAt":"2026-10-02T13:12:50.234Z"},{"id":"mh6ORzNFvU-a","content":"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.","author":"marta_k","createdAt":"2026-10-02T13:37:50.234Z"},{"id":"BjQINokoGEUb","content":"Off. All good now, marking this one resolved.","author":"nils","createdAt":"2026-10-02T13:54:50.234Z"},{"id":"WZRTYZMVPiX1","content":"Just did this at work and this thread saved me a bad hour. thank you whoever you are","author":"Anonymous","createdAt":"2026-10-12T04:36:50.234Z"},{"id":"6Q6KtFgo4sJ1","content":"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.","author":"marta_k","createdAt":"2026-11-04T21:06:50.234Z"}]}