11.05.2023 01:27, David Rowley wrote:
> On Thu, 11 May 2023 at 01:00, Alexander Lakhin <exclusion@gmail.com> wrote:
>> This time `git bisect` pointed at 3c6fc5820. Having compared execution plans
>> (both attached), I see the following differences (3c6fc5820~1 vs 3c6fc5820):
> Based on what you've sent, I'm uninspired to want to try to do
> anything about it. The patched version finds a plan that's cheaper.
> The row estimates are miles off with both plans.
I've made sure that s64da-benchmark performs analyze before running the
queries (pg_class.reltuples fields for tables in question contain actual
counts), so it seems that nothing can be done on the benchmark side to
improve those estimates.
> ... It's pretty hard to make changes to the
> planner's path generation without risking that a bad plan is chosen
> when it wasn't beforehand with bad row estimates.
Yeah, I see. It's also interesting to me, which tests perform better after
that commit. It takes several hours to run all tests, so I can't present
results quickly, but I'll try to collect this information next week.
> Is the new plan still slower if you increase work_mem so that the sort
> no longer goes to disk? Maybe the planner would have picked Hash
> Aggregate if the row estimates had been such that cost_tuplesort()
> knew that the sort would have gone to disk.
Yes, increasing work_mem to 50MB doesn't affect the plans (new plans
attached), though the sort method changed to quicksort. The former plan is
still executed slightly faster.
Best regards,
Alexander