Re: Incorrect cost for MergeAppend

On Wed, Jan 31, 2024 at 4:33 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Wed, 31 Jan 2024 at 02:23, Alexander Kuzmenkov
> <akuzmenkov(at)timescale(dot)com> wrote:
> >
> > On Tue, Jan 30, 2024 at 1:20 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> > > You should likely focus on trying to find a test that does not require
> > > making 2 tables with 10k rows.
> >
> > Is 1k smallint OK? It should fit in one page. Still reproduces the
> > error, and the entire test case runs in under 10 ms.
>
> I had a go at making it a bit smaller without going dangerously close
> to where the plan might change. The following seems to work.
>
> create table ma0(a int primary key);
> create table ma1() inherits (ma0);
> insert into ma0 select generate_series(1, 400);
> insert into ma1 select generate_series(1, 200);
> analyze ma0;
> analyze ma1;
>
> explain (costs off) select * from ma0 where a < 100 order by a;
>
> drop table ma0 cascade;

On my laptop with all default settings

with patch
postgres(at)231714=#explain select * from ma0 where a < 100 order by a;
QUERY PLAN
--------------------------------------------------------------------------------------
Merge Append (cost=6.94..18.90 rows=198 width=4)
Sort Key: ma0.a
-> Index Only Scan using ma0_pkey on ma0 ma0_1 (cost=0.15..9.88
rows=99 width=4)
Index Cond: (a < 100)
-> Sort (cost=6.78..7.03 rows=99 width=4)
Sort Key: ma0_2.a
-> Seq Scan on ma1 ma0_2 (cost=0.00..3.50 rows=99 width=4)
Filter: (a < 100)
(8 rows)

without patch
#explain select * from ma0 where a < 100 order by a;
QUERY PLAN
----------------------------------------------------------------------
Sort (cost=19.04..19.54 rows=198 width=4)
Sort Key: ma0.a
-> Append (cost=0.00..11.49 rows=198 width=4)
-> Seq Scan on ma0 ma0_1 (cost=0.00..7.00 rows=99 width=4)
Filter: (a < 100)
-> Seq Scan on ma1 ma0_2 (cost=0.00..3.50 rows=99 width=4)
Filter: (a < 100)
(7 rows)

postgres(at)233864=#select (19.54 - 18.90)/19.54, (19.04 - 18.09)/19.04;
?column? | ?column?
------------------------+------------------------
0.03275332650972364381 | 0.04989495798319327731
(1 row)

Those numbers are higher than 1% (#define STD_FUZZ_FACTOR 1.01) but
slight variation in all the GUCs that affect cost, might bring the
difference closer to STD_FUZZ_FACTOR.

Given how close they are, maybe it's not such a good idea to
backpatch. If the plans change for better, users won't complain but
otherwise they will complain and will have no way to go back to their
good plans in production.

--
Best Wishes,
Ashutosh Bapat