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