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

On Thu, Oct 13, 2022 at 4:50 AM David Rowley <dgrowleyml@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:

--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -4780,11 +4780,24 @@ create_final_distinct_paths(PlannerInfo *root, RelOptInfo *input_rel,

  if (pathkeys_contained_in(needed_pathkeys, path->pathkeys))
  {
-     add_path(distinct_rel, (Path *)
-              create_upper_unique_path(root, distinct_rel,
-                                       path,
-                                       list_length(root->distinct_pathkeys),
-                                       numDistinctRows));
+     if (root->distinct_pathkeys == NIL)
+     {
+         Node       *limitCount = (Node *) makeConst(INT8OID, -1, InvalidOid,
+                                                     sizeof(int64),
+                                                     Int64GetDatum(1), false,
+                                                     FLOAT8PASSBYVAL);
+
+         add_path(distinct_rel, (Path *)
+                  create_limit_path(root, distinct_rel,
+                                    path, NULL, limitCount,
+                                    LIMIT_OPTION_COUNT, 0, 1));
+     }
+     else
+         add_path(distinct_rel, (Path *)
+                  create_upper_unique_path(root, distinct_rel,
+                                           path,
+                                           list_length(root->distinct_pathkeys),
+                                           numDistinctRows));

This again makes the code less 'straightforward', just to cover a more
uncommon case. I'm also not sure if it's worth doing.

Thanks
Richard