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

On Wed, Oct 12, 2022 at 5:19 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> When all the distinct pathkeys are redundant then there can only be,
> at most, 1 single distinct value. There may be many rows with that
> value, but we can remove those extra ones with a LIMIT 1 rather than
> troubling over needlessly uniquifing them.
>
> This might not be a hugely common case, but; 1) it is very cheap to
> detect and 2) the speedups are likely to be *very* good.
>
> With the attached we get:
>
> regression=# explain (analyze, costs off, timing off) SELECT DISTINCT
> four,1,2,3 FROM tenk1 WHERE four = 0;
> QUERY PLAN
> -------------------------------------------------
> Limit (actual rows=1 loops=1)
> -> Seq Scan on tenk1 (actual rows=1 loops=1)
> Filter: (four = 0)
> Planning Time: 0.215 ms
> Execution Time: 0.071 ms
>
> naturally, if we removed the WHERE four = 0, we can't optimise this
> plan using this method.
>
> I see no reason why this also can't work for DISTINCT ON too.

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
Planning Time: 0.165 ms
Execution Time: 0.458 ms
(6 rows)

Thanks
Richard