From 20af53d7191f84d0f5b86da4362e481b7e85d52a Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 16 Jan 2015 18:18:52 -0500
Subject: [PATCH] Show sort ordering options in EXPLAIN output.
Up to now, EXPLAIN has contented itself with printing the sort expressions
in a Sort or Merge Append plan node. This patch improves that by
annotating the sort keys with COLLATE, DESC, USING, and/or NULLS FIRST/LAST
whenever nondefault sort ordering options are used. The output is now a
reasonably close approximation of an ORDER BY clause equivalent to the
plan's ordering.
Marius Timmer, Lukas Kreft, and Arne Scheffer; reviewed by Mike Blackwell.
Some additional hacking by me.
---
src/backend/commands/explain.c | 90 +++++++++++++++++++++++-
src/test/regress/expected/aggregates.out | 6 +-
src/test/regress/expected/collate.out | 19 +++++
src/test/regress/expected/equivclass.out | 4 +-
src/test/regress/sql/collate.sql | 9 +++
5 files changed, 120 insertions(+), 8 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 2b84ac8af3..7cfc9bb612 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -14,12 +14,14 @@
#include "postgres.h"
#include "access/xact.h"
+#include "catalog/pg_collation.h"
#include "catalog/pg_type.h"
#include "commands/createas.h"
#include "commands/defrem.h"
#include "commands/prepare.h"
#include "executor/hashjoin.h"
#include "foreign/fdwapi.h"
+#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteHandler.h"
@@ -31,6 +33,7 @@
#include "utils/ruleutils.h"
#include "utils/snapmgr.h"
#include "utils/tuplesort.h"
+#include "utils/typcache.h"
#include "utils/xml.h"
@@ -83,7 +86,10 @@ static void show_group_keys(GroupState *gstate, List *ancestors,
ExplainState *es);
static void show_sort_group_keys(PlanState *planstate, const char *qlabel,
int nkeys, AttrNumber *keycols,
+ Oid *sortOperators, Oid *collations, bool *nullsFirst,
List *ancestors, ExplainState *es);
+static void show_sortorder_options(StringInfo buf, Node *sortexpr,
+ Oid sortOperator, Oid collation, bool nullsFirst);
static void show_sort_info(SortState *sortstate, ExplainState *es);
static void show_hash_info(HashState *hashstate, ExplainState *es);
static void show_tidbitmap_info(BitmapHeapScanState *planstate,
@@ -1781,6 +1787,8 @@ show_sort_keys(SortState *sortstate, List *ancestors, ExplainState *es)
show_sort_group_keys((PlanState *) sortstate, "Sort Key",
plan->numCols, plan->sortColIdx,
+ plan->sortOperators, plan->collations,
+ plan->nullsFirst,
ancestors, es);
}
@@ -1795,6 +1803,8 @@ show_merge_append_keys(MergeAppendState *mstate, List *ancestors,
show_sort_group_keys((PlanState *) mstate, "Sort Key",
plan->numCols, plan->sortColIdx,
+ plan->sortOperators, plan->collations,
+ plan->nullsFirst,
ancestors, es);
}
@@ -1813,6 +1823,7 @@ show_agg_keys(AggState *astate, List *ancestors,
ancestors = lcons(astate, ancestors);
show_sort_group_keys(outerPlanState(astate), "Group Key",
plan->numCols, plan->grpColIdx,
+ NULL, NULL, NULL,
ancestors, es);
ancestors = list_delete_first(ancestors);
}
@@ -1831,29 +1842,34 @@ show_group_keys(GroupState *gstate, List *ancestors,
ancestors = lcons(gstate, ancestors);
show_sort_group_keys(outerPlanState(gstate), "Group Key",
plan->numCols, plan->grpColIdx,
+ NULL, NULL, NULL,
ancestors, es);
ancestors = list_delete_first(ancestors);
}
/*
* Common code to show sort/group keys, which are represented in plan nodes
- * as arrays of targetlist indexes
+ * as arrays of targetlist indexes. If it's a sort key rather than a group
+ * key, also pass sort operators/collations/nullsFirst arrays.
*/
static void
show_sort_group_keys(PlanState *planstate, const char *qlabel,
int nkeys, AttrNumber *keycols,
+ Oid *sortOperators, Oid *collations, bool *nullsFirst,
List *ancestors, ExplainState *es)
{
Plan *plan = planstate->plan;
List *context;
List *result = NIL;
+ StringInfoData sortkeybuf;
bool useprefix;
int keyno;
- char *exprstr;
if (nkeys <= 0)
return;
+ initStringInfo(&sortkeybuf);
+
/* Set up deparsing context */
context = set_deparse_context_planstate(es->deparse_cxt,
(Node *) planstate,
@@ -1866,18 +1882,86 @@ show_sort_group_keys(PlanState *planstate, const char *qlabel,
AttrNumber keyresno = keycols[keyno];
TargetEntry *target = get_tle_by_resno(plan->targetlist,
keyresno);
+ char *exprstr;
if (!target)
elog(ERROR, "no tlist entry for key %d", keyresno);
/* Deparse the expression, showing any top-level cast */
exprstr = deparse_expression((Node *) target->expr, context,
useprefix, true);
- result = lappend(result, exprstr);
+ resetStringInfo(&sortkeybuf);
+ appendStringInfoString(&sortkeybuf, exprstr);
+ /* Append sort order information, if relevant */
+ if (sortOperators != NULL)
+ show_sortorder_options(&sortkeybuf,
+ (Node *) target->expr,
+ sortOperators[keyno],
+ collations[keyno],
+ nullsFirst[keyno]);
+ /* Emit one property-list item per sort key */
+ result = lappend(result, pstrdup(sortkeybuf.data));
}
ExplainPropertyList(qlabel, result, es);
}
+/*
+ * Append nondefault characteristics of the sort ordering of a column to buf
+ * (collation, direction, NULLS FIRST/LAST)
+ */
+static void
+show_sortorder_options(StringInfo buf, Node *sortexpr,
+ Oid sortOperator, Oid collation, bool nullsFirst)
+{
+ Oid sortcoltype = exprType(sortexpr);
+ bool reverse = false;
+ TypeCacheEntry *typentry;
+
+ typentry = lookup_type_cache(sortcoltype,
+ TYPECACHE_LT_OPR | TYPECACHE_GT_OPR);
+
+ /*
+ * Print COLLATE if it's not default. There are some cases where this is
+ * redundant, eg if expression is a column whose declared collation is
+ * that collation, but it's hard to distinguish that here.
+ */
+ if (OidIsValid(collation) && collation != DEFAULT_COLLATION_OID)
+ {
+ char *collname = get_collation_name(collation);
+
+ if (collname == NULL)
+ elog(ERROR, "cache lookup failed for collation %u", collation);
+ appendStringInfo(buf, " COLLATE %s", quote_identifier(collname));
+ }
+
+ /* Print direction if not ASC, or USING if non-default sort operator */
+ if (sortOperator == typentry->gt_opr)
+ {
+ appendStringInfoString(buf, " DESC");
+ reverse = true;
+ }
+ else if (sortOperator != typentry->lt_opr)
+ {
+ char *opname = get_opname(sortOperator);
+
+ if (opname == NULL)
+ elog(ERROR, "cache lookup failed for operator %u", sortOperator);
+ appendStringInfo(buf, " USING %s", opname);
+ /* Determine whether operator would be considered ASC or DESC */
+ (void) get_equality_op_for_ordering_op(sortOperator, &reverse);
+ }
+
+ /* Add NULLS FIRST/LAST only if it wouldn't be default */
+ if (nullsFirst && !reverse)
+ {
+ appendStringInfoString(buf, " NULLS FIRST");
+ }
+ else if (!nullsFirst && reverse)
+ {
+ appendStringInfoString(buf, " NULLS LAST");
+ }
+}
+
/*
* If it's EXPLAIN ANALYZE, show tuplesort stats for a sort node
*/
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 40f5398b72..8852051e93 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -735,7 +735,7 @@ explain (costs off)
QUERY PLAN
---------------------------------------------------------------------
Sort
- Sort Key: (generate_series(1, 3))
+ Sort Key: (generate_series(1, 3)) DESC
InitPlan 1 (returns $0)
-> Limit
-> Index Only Scan Backward using tenk1_unique2 on tenk1
@@ -784,7 +784,7 @@ explain (costs off)
InitPlan 2 (returns $1)
-> Limit
-> Merge Append
- Sort Key: minmaxtest_1.f1
+ Sort Key: minmaxtest_1.f1 DESC
-> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1
@@ -823,7 +823,7 @@ explain (costs off)
InitPlan 2 (returns $1)
-> Limit
-> Merge Append
- Sort Key: minmaxtest_1.f1
+ Sort Key: minmaxtest_1.f1 DESC
-> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1
diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out
index 91d574dbe4..f076a4dbae 100644
--- a/src/test/regress/expected/collate.out
+++ b/src/test/regress/expected/collate.out
@@ -603,6 +603,25 @@ ALTER TABLE collate_test22 ADD FOREIGN KEY (f2) REFERENCES collate_test20;
RESET enable_seqscan;
RESET enable_hashjoin;
RESET enable_nestloop;
+-- EXPLAIN
+EXPLAIN (COSTS OFF)
+ SELECT * FROM collate_test10 ORDER BY x, y;
+ QUERY PLAN
+----------------------------------------------
+ Sort
+ Sort Key: x COLLATE "C", y COLLATE "POSIX"
+ -> Seq Scan on collate_test10
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+ SELECT * FROM collate_test10 ORDER BY x DESC, y COLLATE "C" ASC NULLS FIRST;
+ QUERY PLAN
+-----------------------------------------------------------
+ Sort
+ Sort Key: x COLLATE "C" DESC, y COLLATE "C" NULLS FIRST
+ -> Seq Scan on collate_test10
+(3 rows)
+
-- 9.1 bug with useless COLLATE in an expression subject to length coercion
CREATE TEMP TABLE vctable (f1 varchar(25));
INSERT INTO vctable VALUES ('foo' COLLATE "C");
diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out
index b312292c6f..dfae84e1a5 100644
--- a/src/test/regress/expected/equivclass.out
+++ b/src/test/regress/expected/equivclass.out
@@ -319,7 +319,7 @@ explain (costs off)
-> Index Scan using ec1_expr4 on ec1 ec1_3
-> Materialize
-> Sort
- Sort Key: ec1.f1
+ Sort Key: ec1.f1 USING <
-> Index Scan using ec1_pkey on ec1
Index Cond: (ff = 42::bigint)
(20 rows)
@@ -376,7 +376,7 @@ explain (costs off)
-> Index Scan using ec1_expr4 on ec1 ec1_3
-> Materialize
-> Sort
- Sort Key: ec1.f1
+ Sort Key: ec1.f1 USING <
-> Index Scan using ec1_pkey on ec1
Index Cond: (ff = 42::bigint)
(14 rows)
diff --git a/src/test/regress/sql/collate.sql b/src/test/regress/sql/collate.sql
index 63ab590f3a..e8ca0856e3 100644
--- a/src/test/regress/sql/collate.sql
+++ b/src/test/regress/sql/collate.sql
@@ -220,6 +220,15 @@ RESET enable_seqscan;
RESET enable_hashjoin;
RESET enable_nestloop;
+
+-- EXPLAIN
+
+EXPLAIN (COSTS OFF)
+ SELECT * FROM collate_test10 ORDER BY x, y;
+EXPLAIN (COSTS OFF)
+ SELECT * FROM collate_test10 ORDER BY x DESC, y COLLATE "C" ASC NULLS FIRST;
+
+
-- 9.1 bug with useless COLLATE in an expression subject to length coercion
CREATE TEMP TABLE vctable (f1 varchar(25));
--
2.49.0