Re: sqlsmith crash incremental sort

On Wed, Apr 15, 2020 at 10:47 PM Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote:

>
> Well, yeah. The problem is the Unique simply compares the columns in the
> order it sees them, and it does not match the column order desired by
> incremental sort. But we don't push down this information at all :-(
>

This is a nice optimization better to have. Since the 'Sort and Unique'
would unique-ify the result of a UNION by sorting on all columns, why
not we adjust the sort order trying to match parse->sortClause so that
we can avoid the final sort node?

Doing that we can transform plan from:

# explain (costs off) select * from foo union select * from foo order by
1,3;
                  QUERY PLAN
-----------------------------------------------
 Incremental Sort
   Sort Key: foo.a, foo.c
   Presorted Key: foo.a
   ->  Unique
         ->  Sort
               Sort Key: foo.a, foo.b, foo.c
               ->  Append
                     ->  Seq Scan on foo
                     ->  Seq Scan on foo foo_1
(9 rows)

To:

# explain (costs off) select * from foo union select * from foo order by
1,3;
               QUERY PLAN
-----------------------------------------
 Unique
   ->  Sort
         Sort Key: foo.a, foo.c, foo.b
         ->  Append
               ->  Seq Scan on foo
               ->  Seq Scan on foo foo_1
(6 rows)

Thanks
Richard