Re: sqlsmith crash incremental sort

On Thu, Apr 16, 2020 at 6:35 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:

>
> On Wed, Apr 15, 2020 at 10:47 PM Tomas Vondra <
> tomas(dot)vondra(at)2ndquadrant(dot)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)
>
>
Attached is what I'm thinking about this optimization. Does it make any
sense?

Thanks
Richard