Re: Failures with installcheck and low work_mem value in 13~

On Tue, Jun 16, 2020 at 02:39:47PM +0900, Michael Paquier wrote:
>On Mon, Jun 15, 2020 at 10:29:41PM +0900, Michael Paquier wrote:
>> Attempting to run installcheck with 13~ and a value of work_mem lower
>> than the default causes two failures, both related to incremental
>> sorts (here work_mem = 1MB):
>> 1) Test incremental_sort:
>> @@ -4,12 +4,13 @@
>> select * from (select * from tenk1 order by four) t order by four, ten;
>> QUERY PLAN
>> -----------------------------------
>> - Sort
>> + Incremental Sort
>> Sort Key: tenk1.four, tenk1.ten
>> + Presorted Key: tenk1.four
>> -> Sort
>> Sort Key: tenk1.four
>> -> Seq Scan on tenk1
>> -(5 rows)
>> +(6 rows)
>
>Looking at this one, it happens that this is the first test in
>incremental_sort.sql, and we have the following comment:
>-- When we have to sort the entire table, incremental sort will
>-- be slower than plain sort, so it should not be used.
>explain (costs off)
>select * from (select * from tenk1 order by four) t order by four, ten;
>
>When using such a low value of work_mem, why do we switch to an
>incremental sort if we know that it is going to be slower than a plain
>sort? Something looks wrong in the planner choice here.

I don't think it's particularly wrong. The "full sort" can't be done in
memory with such low work_mem value, while the incremental sort can. So
I think the planner choice is sane, it's more than the comment does not
explicitly state this depends on work_mem too.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services