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

On Thu, 13 Oct 2022 at 16:47, Richard Guo <guofenglinux@gmail.com> wrote:
> Currently in the patch the optimization is done before we check for
> presorted paths or do the explicit sort of the cheapest path. How about
> we move this optimization into the branch where we've found any
> presorted paths?  Maybe something like:

I've attached a patch to that effect, but it turns out a bit more
complex than what you imagined.  We still need to handle the case for
when there's no path that has the required pathkeys and we must add a
SortPath to the cheapest path. That requires adding some similar code
to add the LimitPath after the foreach loop over the pathlist is over.

I was also getting some weird plans like:

regression=# explain select distinct on (four) four,hundred from tenk1
where four=0 order by 1,2;
                              QUERY PLAN
----------------------------------------------------------------------
 Sort  (cost=0.20..0.20 rows=1 width=8)
   Sort Key: hundred
   ->  Limit  (cost=0.00..0.19 rows=1 width=8)
         ->  Seq Scan on tenk1  (cost=0.00..470.00 rows=2500 width=8)
               Filter: (four = 0)

To stop the planner from adding that final sort, I opted to hack the
LimitPath's pathkeys to say that it's already sorted by the
PlannerInfo's sort_pathkeys.  That feels slightly icky, but it does
seem a little wasteful to initialise a sort node on every execution of
the plan to sort a single tuple.