Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit20af53d

Browse files
committed
Show sort ordering options in EXPLAIN output.
Up to now, EXPLAIN has contented itself with printing the sort expressionsin a Sort or Merge Append plan node. This patch improves that byannotating the sort keys with COLLATE, DESC, USING, and/or NULLS FIRST/LASTwhenever nondefault sort ordering options are used. The output is now areasonably close approximation of an ORDER BY clause equivalent to theplan's ordering.Marius Timmer, Lukas Kreft, and Arne Scheffer; reviewed by Mike Blackwell.Some additional hacking by me.
1 parent9402869 commit20af53d

File tree

5 files changed

+120
-8
lines changed

5 files changed

+120
-8
lines changed

‎src/backend/commands/explain.c

Lines changed: 87 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -14,12 +14,14 @@
1414
#include"postgres.h"
1515

1616
#include"access/xact.h"
17+
#include"catalog/pg_collation.h"
1718
#include"catalog/pg_type.h"
1819
#include"commands/createas.h"
1920
#include"commands/defrem.h"
2021
#include"commands/prepare.h"
2122
#include"executor/hashjoin.h"
2223
#include"foreign/fdwapi.h"
24+
#include"nodes/nodeFuncs.h"
2325
#include"optimizer/clauses.h"
2426
#include"parser/parsetree.h"
2527
#include"rewrite/rewriteHandler.h"
@@ -31,6 +33,7 @@
3133
#include"utils/ruleutils.h"
3234
#include"utils/snapmgr.h"
3335
#include"utils/tuplesort.h"
36+
#include"utils/typcache.h"
3437
#include"utils/xml.h"
3538

3639

@@ -83,7 +86,10 @@ static void show_group_keys(GroupState *gstate, List *ancestors,
8386
ExplainState*es);
8487
staticvoidshow_sort_group_keys(PlanState*planstate,constchar*qlabel,
8588
intnkeys,AttrNumber*keycols,
89+
Oid*sortOperators,Oid*collations,bool*nullsFirst,
8690
List*ancestors,ExplainState*es);
91+
staticvoidshow_sortorder_options(StringInfobuf,Node*sortexpr,
92+
OidsortOperator,Oidcollation,boolnullsFirst);
8793
staticvoidshow_sort_info(SortState*sortstate,ExplainState*es);
8894
staticvoidshow_hash_info(HashState*hashstate,ExplainState*es);
8995
staticvoidshow_tidbitmap_info(BitmapHeapScanState*planstate,
@@ -1781,6 +1787,8 @@ show_sort_keys(SortState *sortstate, List *ancestors, ExplainState *es)
17811787

17821788
show_sort_group_keys((PlanState*)sortstate,"Sort Key",
17831789
plan->numCols,plan->sortColIdx,
1790+
plan->sortOperators,plan->collations,
1791+
plan->nullsFirst,
17841792
ancestors,es);
17851793
}
17861794

@@ -1795,6 +1803,8 @@ show_merge_append_keys(MergeAppendState *mstate, List *ancestors,
17951803

17961804
show_sort_group_keys((PlanState*)mstate,"Sort Key",
17971805
plan->numCols,plan->sortColIdx,
1806+
plan->sortOperators,plan->collations,
1807+
plan->nullsFirst,
17981808
ancestors,es);
17991809
}
18001810

@@ -1813,6 +1823,7 @@ show_agg_keys(AggState *astate, List *ancestors,
18131823
ancestors=lcons(astate,ancestors);
18141824
show_sort_group_keys(outerPlanState(astate),"Group Key",
18151825
plan->numCols,plan->grpColIdx,
1826+
NULL,NULL,NULL,
18161827
ancestors,es);
18171828
ancestors=list_delete_first(ancestors);
18181829
}
@@ -1831,29 +1842,34 @@ show_group_keys(GroupState *gstate, List *ancestors,
18311842
ancestors=lcons(gstate,ancestors);
18321843
show_sort_group_keys(outerPlanState(gstate),"Group Key",
18331844
plan->numCols,plan->grpColIdx,
1845+
NULL,NULL,NULL,
18341846
ancestors,es);
18351847
ancestors=list_delete_first(ancestors);
18361848
}
18371849

18381850
/*
18391851
* Common code to show sort/group keys, which are represented in plan nodes
1840-
* as arrays of targetlist indexes
1852+
* as arrays of targetlist indexes. If it's a sort key rather than a group
1853+
* key, also pass sort operators/collations/nullsFirst arrays.
18411854
*/
18421855
staticvoid
18431856
show_sort_group_keys(PlanState*planstate,constchar*qlabel,
18441857
intnkeys,AttrNumber*keycols,
1858+
Oid*sortOperators,Oid*collations,bool*nullsFirst,
18451859
List*ancestors,ExplainState*es)
18461860
{
18471861
Plan*plan=planstate->plan;
18481862
List*context;
18491863
List*result=NIL;
1864+
StringInfoDatasortkeybuf;
18501865
booluseprefix;
18511866
intkeyno;
1852-
char*exprstr;
18531867

18541868
if (nkeys <=0)
18551869
return;
18561870

1871+
initStringInfo(&sortkeybuf);
1872+
18571873
/* Set up deparsing context */
18581874
context=set_deparse_context_planstate(es->deparse_cxt,
18591875
(Node*)planstate,
@@ -1866,18 +1882,86 @@ show_sort_group_keys(PlanState *planstate, const char *qlabel,
18661882
AttrNumberkeyresno=keycols[keyno];
18671883
TargetEntry*target=get_tle_by_resno(plan->targetlist,
18681884
keyresno);
1885+
char*exprstr;
18691886

18701887
if (!target)
18711888
elog(ERROR,"no tlist entry for key %d",keyresno);
18721889
/* Deparse the expression, showing any top-level cast */
18731890
exprstr=deparse_expression((Node*)target->expr,context,
18741891
useprefix, true);
1875-
result=lappend(result,exprstr);
1892+
resetStringInfo(&sortkeybuf);
1893+
appendStringInfoString(&sortkeybuf,exprstr);
1894+
/* Append sort order information, if relevant */
1895+
if (sortOperators!=NULL)
1896+
show_sortorder_options(&sortkeybuf,
1897+
(Node*)target->expr,
1898+
sortOperators[keyno],
1899+
collations[keyno],
1900+
nullsFirst[keyno]);
1901+
/* Emit one property-list item per sort key */
1902+
result=lappend(result,pstrdup(sortkeybuf.data));
18761903
}
18771904

18781905
ExplainPropertyList(qlabel,result,es);
18791906
}
18801907

1908+
/*
1909+
* Append nondefault characteristics of the sort ordering of a column to buf
1910+
* (collation, direction, NULLS FIRST/LAST)
1911+
*/
1912+
staticvoid
1913+
show_sortorder_options(StringInfobuf,Node*sortexpr,
1914+
OidsortOperator,Oidcollation,boolnullsFirst)
1915+
{
1916+
Oidsortcoltype=exprType(sortexpr);
1917+
boolreverse= false;
1918+
TypeCacheEntry*typentry;
1919+
1920+
typentry=lookup_type_cache(sortcoltype,
1921+
TYPECACHE_LT_OPR |TYPECACHE_GT_OPR);
1922+
1923+
/*
1924+
* Print COLLATE if it's not default. There are some cases where this is
1925+
* redundant, eg if expression is a column whose declared collation is
1926+
* that collation, but it's hard to distinguish that here.
1927+
*/
1928+
if (OidIsValid(collation)&&collation!=DEFAULT_COLLATION_OID)
1929+
{
1930+
char*collname=get_collation_name(collation);
1931+
1932+
if (collname==NULL)
1933+
elog(ERROR,"cache lookup failed for collation %u",collation);
1934+
appendStringInfo(buf," COLLATE %s",quote_identifier(collname));
1935+
}
1936+
1937+
/* Print direction if not ASC, or USING if non-default sort operator */
1938+
if (sortOperator==typentry->gt_opr)
1939+
{
1940+
appendStringInfoString(buf," DESC");
1941+
reverse= true;
1942+
}
1943+
elseif (sortOperator!=typentry->lt_opr)
1944+
{
1945+
char*opname=get_opname(sortOperator);
1946+
1947+
if (opname==NULL)
1948+
elog(ERROR,"cache lookup failed for operator %u",sortOperator);
1949+
appendStringInfo(buf," USING %s",opname);
1950+
/* Determine whether operator would be considered ASC or DESC */
1951+
(void)get_equality_op_for_ordering_op(sortOperator,&reverse);
1952+
}
1953+
1954+
/* Add NULLS FIRST/LAST only if it wouldn't be default */
1955+
if (nullsFirst&& !reverse)
1956+
{
1957+
appendStringInfoString(buf," NULLS FIRST");
1958+
}
1959+
elseif (!nullsFirst&&reverse)
1960+
{
1961+
appendStringInfoString(buf," NULLS LAST");
1962+
}
1963+
}
1964+
18811965
/*
18821966
* If it's EXPLAIN ANALYZE, show tuplesort stats for a sort node
18831967
*/

‎src/test/regress/expected/aggregates.out

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -735,7 +735,7 @@ explain (costs off)
735735
QUERY PLAN
736736
---------------------------------------------------------------------
737737
Sort
738-
Sort Key: (generate_series(1, 3))
738+
Sort Key: (generate_series(1, 3)) DESC
739739
InitPlan 1 (returns $0)
740740
-> Limit
741741
-> Index Only Scan Backward using tenk1_unique2 on tenk1
@@ -784,7 +784,7 @@ explain (costs off)
784784
InitPlan 2 (returns $1)
785785
-> Limit
786786
-> Merge Append
787-
Sort Key: minmaxtest_1.f1
787+
Sort Key: minmaxtest_1.f1 DESC
788788
-> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1
789789
Index Cond: (f1 IS NOT NULL)
790790
-> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1
@@ -823,7 +823,7 @@ explain (costs off)
823823
InitPlan 2 (returns $1)
824824
-> Limit
825825
-> Merge Append
826-
Sort Key: minmaxtest_1.f1
826+
Sort Key: minmaxtest_1.f1 DESC
827827
-> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1
828828
Index Cond: (f1 IS NOT NULL)
829829
-> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1

‎src/test/regress/expected/collate.out

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -603,6 +603,25 @@ ALTER TABLE collate_test22 ADD FOREIGN KEY (f2) REFERENCES collate_test20;
603603
RESET enable_seqscan;
604604
RESET enable_hashjoin;
605605
RESET enable_nestloop;
606+
-- EXPLAIN
607+
EXPLAIN (COSTS OFF)
608+
SELECT * FROM collate_test10 ORDER BY x, y;
609+
QUERY PLAN
610+
----------------------------------------------
611+
Sort
612+
Sort Key: x COLLATE "C", y COLLATE "POSIX"
613+
-> Seq Scan on collate_test10
614+
(3 rows)
615+
616+
EXPLAIN (COSTS OFF)
617+
SELECT * FROM collate_test10 ORDER BY x DESC, y COLLATE "C" ASC NULLS FIRST;
618+
QUERY PLAN
619+
-----------------------------------------------------------
620+
Sort
621+
Sort Key: x COLLATE "C" DESC, y COLLATE "C" NULLS FIRST
622+
-> Seq Scan on collate_test10
623+
(3 rows)
624+
606625
-- 9.1 bug with useless COLLATE in an expression subject to length coercion
607626
CREATE TEMP TABLE vctable (f1 varchar(25));
608627
INSERT INTO vctable VALUES ('foo' COLLATE "C");

‎src/test/regress/expected/equivclass.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -319,7 +319,7 @@ explain (costs off)
319319
-> Index Scan using ec1_expr4 on ec1 ec1_3
320320
-> Materialize
321321
-> Sort
322-
Sort Key: ec1.f1
322+
Sort Key: ec1.f1 USING <
323323
-> Index Scan using ec1_pkey on ec1
324324
Index Cond: (ff = 42::bigint)
325325
(20 rows)
@@ -376,7 +376,7 @@ explain (costs off)
376376
-> Index Scan using ec1_expr4 on ec1 ec1_3
377377
-> Materialize
378378
-> Sort
379-
Sort Key: ec1.f1
379+
Sort Key: ec1.f1 USING <
380380
-> Index Scan using ec1_pkey on ec1
381381
Index Cond: (ff = 42::bigint)
382382
(14 rows)

‎src/test/regress/sql/collate.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -220,6 +220,15 @@ RESET enable_seqscan;
220220
RESET enable_hashjoin;
221221
RESET enable_nestloop;
222222

223+
224+
-- EXPLAIN
225+
226+
EXPLAIN (COSTS OFF)
227+
SELECT*FROM collate_test10ORDER BY x, y;
228+
EXPLAIN (COSTS OFF)
229+
SELECT*FROM collate_test10ORDER BY xDESC, y COLLATE"C"ASC NULLS FIRST;
230+
231+
223232
-- 9.1 bug with useless COLLATE in an expression subject to length coercion
224233

225234
CREATE TEMP TABLE vctable (f1varchar(25));

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp