Re: Consider the number of columns in the sort cost model

Hi!

On 14.10.2024 22:59, Kirill Reshke wrote:
>> The patch in the attachment is a simplistic attempt to differentiate
>> these sortings by the number of columns. It is not ideal because
>> comparisons depend on the duplicates in each column, but it may be the
>> subject of further work.
>>
>> Even such a trivial model allows us to resolve the case like the below:
>> CREATE INDEX ON test (x1,x2,x3,x8);
>> EXPLAIN (ANALYZE) SELECT * FROM test WHERE x1<9E4 ORDER BY x1,x2,x3,x8;
>>
>> The current master will choose SeqScan for four columns as well as for a
>> single column. With the model, the optimiser will understand that
>> sorting four columns costs more than sorting a single column and switch
>> to IndexScan.
>>
>> --
>> regards, Andrei Lepikhov
>
> Also, this patch needs to be rebased, again.
>
I also noticed that the code needs to be updated, so I rebased the code
to master during the review and attached a patch.

I have a question about estimating the cost of an Append node with your
sort cost model.

I see that if pathkeys is not a subset of subpath pathkeys, then we
calculate the cost taking pathkeys into account. However, I didn't
notice the estimation like that for the opposite situation.
I see that the cost of Append is the sum of the subpath costs, but we
don't take pathkeys into account for subpaths, right?

--
Regards,
Alena Rybakina
Postgres Professional