Re: Use LIMIT instead of Unique for DISTINCT when all distinct pathkeys are redundant

On Thu, 13 Oct 2022 at 01:13, Richard Guo <guofenglinux@gmail.com> wrote:
> For DISTINCT ON, if all the distinct pathkeys are redundant but there
> are available sort pathkeys, then for adequately-presorted paths I think
> we can also apply this optimization, using a Limit 1 rather than Unique.
>
> regression=# explain (analyze, costs off, timing off) select distinct on (four) * from tenk1 where four = 0 order by
four,hundred desc;
 
>                                    QUERY PLAN
> --------------------------------------------------------------------------------
>  Limit (actual rows=1 loops=1)
>    ->  Index Scan Backward using tenk1_hundred on tenk1 (actual rows=1 loops=1)
>          Filter: (four = 0)
>          Rows Removed by Filter: 300

I don't think we can optimise this case, at least not the same way I'm
doing it in the patch I attached.

The problem is that I'm only added the LimitPath to the
cheapest_total_path.  I think to make your case work we'd need to add
the LimitPath only in cases where the distinct_pathkeys are empty but
the sort_pathkeys are not and hasDistinctOn is true and the path has
pathkeys_contained_in(root->sort_pathkeys, path->pathkeys).  I think
that's doable, but it's become quite a bit more complex than the patch
I proposed. Maybe it's worth a 2nd effort for that part?

David