| From: | Radu Radutiu <rradutiu(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | Plan regression from 12 to 13 |
| Date: | 2022-02-23 13:44:24 |
| Message-ID: | CAG4Txrgk+avu5KgRayWSxJdBGGW=bueW81XQrYPb1GSLF+yLnQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Johannes Graën | 2022-02-23 21:15:48 | tsvector string representation and parsing |
| Previous Message | Nikolay Samokhvalov | 2022-02-22 21:38:48 | Re: AWS vs GCP storage |