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(at)gmail(dot)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.

David