Make nodeSort.c use Datum sorts for single column sorts

Datum sorts can be significantly faster than tuple sorts, especially when
the data type being sorted is a pass-by-value type.  Something in the
region of 50-70% performance improvements appear to be possible.

Just in case there's any confusion; the Datum sort is only used when the
targetlist of the Sort node contains a single column, not when there's a
single column in the sort key and multiple items in the target list.

Author: Ronan Dunklau
Reviewed-by: James Coleman, David Rowley, Ranier Vilela, Hou Zhijie
Tested-by: John Naylor
Discussion: https://postgr.es/m/3177670.itZtoPt7T5@aivenronan

diff --git a/src/backend/executor/nodeSort.c b/src/backend/executor/nodeSort.c
index b99027e0d7f4e26e7b6bdacc6faf6eeeb0df5a1d..c68d9e41c2ed1b55c1d553dc27cd7df27f3f6601 100644 (file)
--- a/src/backend/executor/nodeSort.c
+++ b/src/backend/executor/nodeSort.c
@@ -29,6 +29,16 @@
  *     which saves the results in a temporary file or memory. After the
  *     initial call, returns a tuple from the file with each call.
  *
+ *     There are two distinct ways that this sort can be performed:
+ *
+ *     1) When the result is a single column we perform a Datum sort.
+ *
+ *     2) When the result contains multiple columns we perform a tuple sort.
+ *
+ *     We could do this by always performing a tuple sort, however sorting
+ *     Datums only can be significantly faster than sorting tuples,
+ *     especially when the Datums are of a pass-by-value type.
+ *
  *     Conditions:
  *       -- none.
  *
@@ -86,31 +96,56 @@ ExecSort(PlanState *pstate)
        outerNode = outerPlanState(node);
        tupDesc = ExecGetResultType(outerNode);
 
-       tuplesortstate = tuplesort_begin_heap(tupDesc,
-                                             plannode->numCols,
-                                             plannode->sortColIdx,
-                                             plannode->sortOperators,
-                                             plannode->collations,
-                                             plannode->nullsFirst,
-                                             work_mem,
-                                             NULL,
-                                             node->randomAccess);
+       if (node->datumSort)
+           tuplesortstate = tuplesort_begin_datum(TupleDescAttr(tupDesc, 0)->atttypid,
+                                                  plannode->sortOperators[0],
+                                                  plannode->collations[0],
+                                                  plannode->nullsFirst[0],
+                                                  work_mem,
+                                                  NULL,
+                                                  node->randomAccess);
+       else
+           tuplesortstate = tuplesort_begin_heap(tupDesc,
+                                                 plannode->numCols,
+                                                 plannode->sortColIdx,
+                                                 plannode->sortOperators,
+                                                 plannode->collations,
+                                                 plannode->nullsFirst,
+                                                 work_mem,
+                                                 NULL,
+                                                 node->randomAccess);
        if (node->bounded)
            tuplesort_set_bound(tuplesortstate, node->bound);
        node->tuplesortstate = (void *) tuplesortstate;
 
        /*
-        * Scan the subplan and feed all the tuples to tuplesort.
+        * Scan the subplan and feed all the tuples to tuplesort using the
+        * appropriate method based on the type of sort we're doing.
         */
-
-       for (;;)
+       if (node->datumSort)
        {
-           slot = ExecProcNode(outerNode);
-
-           if (TupIsNull(slot))
-               break;
-
-           tuplesort_puttupleslot(tuplesortstate, slot);
+           for (;;)
+           {
+               slot = ExecProcNode(outerNode);
+
+               if (TupIsNull(slot))
+                   break;
+               slot_getsomeattrs(slot, 1);
+               tuplesort_putdatum(tuplesortstate,
+                                  slot->tts_values[0],
+                                  slot->tts_isnull[0]);
+           }
+       }
+       else
+       {
+           for (;;)
+           {
+               slot = ExecProcNode(outerNode);
+
+               if (TupIsNull(slot))
+                   break;
+               tuplesort_puttupleslot(tuplesortstate, slot);
+           }
        }
 
        /*
@@ -144,15 +179,27 @@ ExecSort(PlanState *pstate)
    SO1_printf("ExecSort: %s\n",
               "retrieving tuple from tuplesort");
 
+   slot = node->ss.ps.ps_ResultTupleSlot;
+
    /*
-    * Get the first or next tuple from tuplesort. Returns NULL if no more
-    * tuples.  Note that we only rely on slot tuple remaining valid until the
-    * next fetch from the tuplesort.
+    * Fetch the next sorted item from the appropriate tuplesort function. For
+    * datum sorts we must manage the slot ourselves and leave it clear when
+    * tuplesort_getdatum returns false to indicate there are no more datums.
+    * For tuple sorts, tuplesort_gettupleslot manages the slot for us and
+    * empties the slot when it runs out of tuples.
     */
-   slot = node->ss.ps.ps_ResultTupleSlot;
-   (void) tuplesort_gettupleslot(tuplesortstate,
-                                 ScanDirectionIsForward(dir),
-                                 false, slot, NULL);
+   if (node->datumSort)
+   {
+       ExecClearTuple(slot);
+       if (tuplesort_getdatum(tuplesortstate, ScanDirectionIsForward(dir),
+                              &(slot->tts_values[0]), &(slot->tts_isnull[0]), NULL))
+           ExecStoreVirtualTuple(slot);
+   }
+   else
+       (void) tuplesort_gettupleslot(tuplesortstate,
+                                     ScanDirectionIsForward(dir),
+                                     false, slot, NULL);
+
    return slot;
 }
 
@@ -221,6 +268,15 @@ ExecInitSort(Sort *node, EState *estate, int eflags)
    ExecInitResultTupleSlotTL(&sortstate->ss.ps, &TTSOpsMinimalTuple);
    sortstate->ss.ps.ps_ProjInfo = NULL;
 
+   /*
+    * We perform a Datum sort when we're sorting just a single column,
+    * otherwise we perform a tuple sort.
+    */
+   if (ExecGetResultType(outerPlanState(sortstate))->natts == 1)
+       sortstate->datumSort = true;
+   else
+       sortstate->datumSort = false;
+
    SO1_printf("ExecInitSort: %s\n",
               "sort node initialized");
 

diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index ffc78447563e2f1423f8a7d07c50b719a51e5e8b..37cb4f3d59a56c6c2fed0bf8bebf06e8fbb52ab6 100644 (file)
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2151,6 +2151,7 @@ typedef struct SortState
    int64       bound_Done;     /* value of bound we did the sort with */
    void       *tuplesortstate; /* private state of tuplesort.c */
    bool        am_worker;      /* are we a worker? */
+   bool        datumSort;      /* Datum sort instead of tuple sort? */
    SharedSortInfo *shared_info;    /* one entry per worker */
 } SortState;