On Thu, 13 Oct 2022 at 01:13, Richard Guo <guofengli...@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
