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

On Mon, Jun 15, 2020 at 10:29:41PM +0900, Michael Paquier wrote:
>Hi all,
>
>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)
>
>2) Test join:
>@@ -2368,12 +2368,13 @@
>    ->  Merge Left Join
>          Merge Cond: (x.thousand = y.unique2)
>          Join Filter: ((x.twothousand = y.hundred) AND (x.fivethous = y.unique2))
>-         ->  Sort
>+         ->  Incremental Sort
>                Sort Key: x.thousand, x.twothousand, x.fivethous
>-               ->  Seq Scan on tenk1 x
>+               Presorted Key: x.thousand
>+               ->  Index Scan using tenk1_thous_tenthous on tenk1 x
>          ->  Materialize
>                ->  Index Scan using tenk1_unique2 on tenk1 y
>-(9 rows)
>+(10 rows)
>
>There are of course regression failures when changing the relation
>page size or such, but we should have tests more portable when it
>comes to work_mem (this issue does not exist in ~12) or people running
>installcheck on a new instance would be surprised.  Please note that I
>have not looked at the problem in details, but a simple solution would
>be to enforce work_mem in those code paths to keep the two plans
>stable.
>

I don't think the tests can be made not to depend on work_mem, because
it costing of sort / incremental sort depends on the value. I agree
setting the work_mem at the beginning of the test script is the right
solution.

regards

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