Thread: Plan regression from 12 to 13
Hi,
I have a query (generated by RequestTracker) that has regressed after upgrading from postgresql 12 to 13. The strange thing is that decreasing the effective_cache_size causes postgresql 13 to generate an acceptable plan.My setup is: 1 VM, 4 cores, 16 GB RAM (server has around 10 GB for filesystem cache)
shared_buffers 2.4 GB
With effective_cache_size 8G, I have the following plan: https://explain.depesz.com/s/lIcV (the run time is after several tries, with most of the data in memory; the initial run reading from disk is around 10 times slower)
If I decrease the effective_cache_size to '2GB' or less, I get a much better plan: https://explain.depesz.com/s/Z6C7W
The old VM running postgresql 12 (with a somewhat smaller database) always generate a good plan,regardless of the effective_cache_size : https://explain.depesz.com/s/iawn
Are there any defaults that have changed from 12 to 13 that would explain the different plans?
Thanks,
Radu