Re: MergeAppend could consider sorting cheapest child path - Mailing list pgsql-hackers

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)

--