Plan regression from 12 to 13

Поиск
Список
Период
Сортировка
От Radu Radutiu
Тема Plan regression from 12 to 13
Дата
Msg-id CAG4Txrgk+avu5KgRayWSxJdBGGW=bueW81XQrYPb1GSLF+yLnQ@mail.gmail.com
обсуждение исходный текст
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Nikolay Samokhvalov
Дата:
Сообщение: Re: AWS vs GCP storage
Следующее
От: Johannes Graën
Дата:
Сообщение: tsvector string representation and parsing