Re: MergeAppend could consider sorting cheapest child path

Hi!

I've checked this thread and examples in it, and do not see stable
improvements
in base tests. Sometimes base tests are considerably slower with patch,
like:

explain analyze
select t1.* from matest0 t1, matest0 t2
where t1.b = t2.b and t2.c = t2.d
order by t1.b limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.46..19.90 rows=10 width=16) (actual time=0.007..0.008
rows=0 loops=1)
-> Merge Join (cost=0.46..181.24 rows=93 width=16) (actual
time=0.007..0.007 rows=0 loops=1)
Merge Cond: (t1.b = t2.b)
-> Merge Append (cost=0.17..90.44 rows=1851 width=16) (actual
time=0.006..0.007 rows=0 loops=1)
Sort Key: t1.b
-> Sort (cost=0.01..0.02 rows=1 width=16) (actual
time=0.004..0.004 rows=0 loops=1)
Sort Key: t1_1.b
Sort Method: quicksort Memory: 25kB
-> Seq Scan on matest0 t1_1 (cost=0.00..0.00 rows=1
width=16) (actual time=0.002..0.002 rows=0 loops=1)
-> Index Scan using matest1i on matest1 t1_2
(cost=0.15..71.90 rows=1850 width=16) (actual time=0.002..0.002 rows=0
loops=1)
-> Materialize (cost=0.29..84.81 rows=10 width=4) (never
executed)
-> Merge Append (cost=0.29..84.78 rows=10 width=4) (never
executed)
Sort Key: t2.b
-> Index Scan using matest0i on matest0 t2_1
(cost=0.12..8.14 rows=1 width=4) (never executed)
Filter: (c = d)
-> Index Scan using matest1i on matest1 t2_2
(cost=0.15..76.53 rows=9 width=4) (never executed)
Filter: (c = d)
Planning Time: 0.252 ms
Execution Time: 0.048 ms
(19 rows)

explain analyze
select t1.* from matest0 t1, matest0 t2
where t1.b = t2.b and t2.c = t2.d
order by t1.b limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.57..20.88 rows=10 width=16) (actual time=0.004..0.004
rows=0 loops=1)
-> Merge Join (cost=0.57..189.37 rows=93 width=16) (actual
time=0.003..0.004 rows=0 loops=1)
Merge Cond: (t1.b = t2.b)
-> Merge Append (cost=0.29..98.56 rows=1851 width=16) (actual
time=0.002..0.003 rows=0 loops=1)
Sort Key: t1.b
-> Index Scan using matest0i on matest0 t1_1
(cost=0.12..8.14 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
-> Index Scan using matest1i on matest1 t1_2
(cost=0.15..71.90 rows=1850 width=16) (actual time=0.001..0.001 rows=0
loops=1)
-> Materialize (cost=0.29..84.81 rows=10 width=4) (never
executed)
-> Merge Append (cost=0.29..84.78 rows=10 width=4) (never
executed)
Sort Key: t2.b
-> Index Scan using matest0i on matest0 t2_1
(cost=0.12..8.14 rows=1 width=4) (never executed)
Filter: (c = d)
-> Index Scan using matest1i on matest1 t2_2
(cost=0.15..76.53 rows=9 width=4) (never executed)
Filter: (c = d)
Planning Time: 0.278 ms
Execution Time: 0.025 ms
(16 rows)

(patched)
explain analyze
select t1.* from matest0 t1, matest0 t2
where t1.b = t2.b and t2.c = t2.d
order by t1.b limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.46..19.90 rows=10 width=16) (actual time=0.007..0.008
rows=0 loops=1)
-> Merge Join (cost=0.46..181.24 rows=93 width=16) (actual
time=0.007..0.007 rows=0 loops=1)
Merge Cond: (t1.b = t2.b)
-> Merge Append (cost=0.17..90.44 rows=1851 width=16) (actual
time=0.006..0.007 rows=0 loops=1)
Sort Key: t1.b
-> Sort (cost=0.01..0.02 rows=1 width=16) (actual
time=0.004..0.004 rows=0 loops=1)
Sort Key: t1_1.b
Sort Method: quicksort Memory: 25kB
-> Seq Scan on matest0 t1_1 (cost=0.00..0.00 rows=1
width=16) (actual time=0.002..0.002 rows=0 loops=1)
-> Index Scan using matest1i on matest1 t1_2
(cost=0.15..71.90 rows=1850 width=16) (actual time=0.002..0.002 rows=0
loops=1)
-> Materialize (cost=0.29..84.81 rows=10 width=4) (never
executed)
-> Merge Append (cost=0.29..84.78 rows=10 width=4) (never
executed)
Sort Key: t2.b
-> Index Scan using matest0i on matest0 t2_1
(cost=0.12..8.14 rows=1 width=4) (never executed)
Filter: (c = d)
-> Index Scan using matest1i on matest1 t2_2
(cost=0.15..76.53 rows=9 width=4) (never executed)
Filter: (c = d)
Planning Time: 0.252 ms
Execution Time: 0.048 ms
(19 rows)

(vanilla)
explain analyze
select t1.* from matest0 t1, matest0 t2
where t1.b = t2.b and t2.c = t2.d
order by t1.b limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.57..20.88 rows=10 width=16) (actual time=0.004..0.004
rows=0 loops=1)
-> Merge Join (cost=0.57..189.37 rows=93 width=16) (actual
time=0.003..0.004 rows=0 loops=1)
Merge Cond: (t1.b = t2.b)
-> Merge Append (cost=0.29..98.56 rows=1851 width=16) (actual
time=0.002..0.003 rows=0 loops=1)
Sort Key: t1.b
-> Index Scan using matest0i on matest0 t1_1
(cost=0.12..8.14 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
-> Index Scan using matest1i on matest1 t1_2
(cost=0.15..71.90 rows=1850 width=16) (actual time=0.001..0.001 rows=0
loops=1)
-> Materialize (cost=0.29..84.81 rows=10 width=4) (never
executed)
-> Merge Append (cost=0.29..84.78 rows=10 width=4) (never
executed)
Sort Key: t2.b
-> Index Scan using matest0i on matest0 t2_1
(cost=0.12..8.14 rows=1 width=4) (never executed)
Filter: (c = d)
-> Index Scan using matest1i on matest1 t2_2
(cost=0.15..76.53 rows=9 width=4) (never executed)
Filter: (c = d)
Planning Time: 0.278 ms
Execution Time: 0.025 ms
(16 rows)

--
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/