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

Commitd4c3a15

Browse files
committed
Remove GROUP BY columns that are functionally dependent on other columns.
If a GROUP BY clause includes all columns of a non-deferred primary key,as well as other columns of the same relation, those other columns areredundant and can be dropped from the grouping; the pkey is enough toensure that each row of the table corresponds to a separate group.Getting rid of the excess columns will reduce the cost of the sorting orhashing needed to implement GROUP BY, and can indeed remove the need fora sort step altogether.This seems worth testing for since many query authors are not aware ofthe GROUP-BY-primary-key exception to the rule about queries not beingallowed to reference non-grouped-by columns in their targetlists orHAVING clauses. Thus, redundant GROUP BY items are not uncommon. Also,we can make the test pretty cheap in most queries where it won't helpby not looking up a rel's primary key until we've found that at leasttwo of its columns are in GROUP BY.David Rowley, reviewed by Julien Rouhaud
1 parent72eee41 commitd4c3a15

File tree

7 files changed

+360
-5
lines changed

7 files changed

+360
-5
lines changed

‎src/backend/catalog/pg_constraint.c

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,7 @@
1717
#include"access/genam.h"
1818
#include"access/heapam.h"
1919
#include"access/htup_details.h"
20+
#include"access/sysattr.h"
2021
#include"catalog/dependency.h"
2122
#include"catalog/indexing.h"
2223
#include"catalog/objectaccess.h"
@@ -871,6 +872,99 @@ get_domain_constraint_oid(Oid typid, const char *conname, bool missing_ok)
871872
returnconOid;
872873
}
873874

875+
/*
876+
* get_primary_key_attnos
877+
*Identify the columns in a relation's primary key, if any.
878+
*
879+
* Returns a Bitmapset of the column attnos of the primary key's columns,
880+
* with attnos being offset by FirstLowInvalidHeapAttributeNumber so that
881+
* system columns can be represented.
882+
*
883+
* If there is no primary key, return NULL. We also return NULL if the pkey
884+
* constraint is deferrable and deferrableOk is false.
885+
*
886+
* *constraintOid is set to the OID of the pkey constraint, or InvalidOid
887+
* on failure.
888+
*/
889+
Bitmapset*
890+
get_primary_key_attnos(Oidrelid,booldeferrableOk,Oid*constraintOid)
891+
{
892+
Bitmapset*pkattnos=NULL;
893+
Relationpg_constraint;
894+
HeapTupletuple;
895+
SysScanDescscan;
896+
ScanKeyDataskey[1];
897+
898+
/* Set *constraintOid, to avoid complaints about uninitialized vars */
899+
*constraintOid=InvalidOid;
900+
901+
/* Scan pg_constraint for constraints of the target rel */
902+
pg_constraint=heap_open(ConstraintRelationId,AccessShareLock);
903+
904+
ScanKeyInit(&skey[0],
905+
Anum_pg_constraint_conrelid,
906+
BTEqualStrategyNumber,F_OIDEQ,
907+
ObjectIdGetDatum(relid));
908+
909+
scan=systable_beginscan(pg_constraint,ConstraintRelidIndexId, true,
910+
NULL,1,skey);
911+
912+
while (HeapTupleIsValid(tuple=systable_getnext(scan)))
913+
{
914+
Form_pg_constraintcon= (Form_pg_constraint)GETSTRUCT(tuple);
915+
Datumadatum;
916+
boolisNull;
917+
ArrayType*arr;
918+
int16*attnums;
919+
intnumkeys;
920+
inti;
921+
922+
/* Skip constraints that are not PRIMARY KEYs */
923+
if (con->contype!=CONSTRAINT_PRIMARY)
924+
continue;
925+
926+
/*
927+
* If the primary key is deferrable, but we've been instructed to
928+
* ignore deferrable constraints, then we might as well give up
929+
* searching, since there can only be a single primary key on a table.
930+
*/
931+
if (con->condeferrable&& !deferrableOk)
932+
break;
933+
934+
/* Extract the conkey array, ie, attnums of PK's columns */
935+
adatum=heap_getattr(tuple,Anum_pg_constraint_conkey,
936+
RelationGetDescr(pg_constraint),&isNull);
937+
if (isNull)
938+
elog(ERROR,"null conkey for constraint %u",
939+
HeapTupleGetOid(tuple));
940+
arr=DatumGetArrayTypeP(adatum);/* ensure not toasted */
941+
numkeys=ARR_DIMS(arr)[0];
942+
if (ARR_NDIM(arr)!=1||
943+
numkeys<0||
944+
ARR_HASNULL(arr)||
945+
ARR_ELEMTYPE(arr)!=INT2OID)
946+
elog(ERROR,"conkey is not a 1-D smallint array");
947+
attnums= (int16*)ARR_DATA_PTR(arr);
948+
949+
/* Construct the result value */
950+
for (i=0;i<numkeys;i++)
951+
{
952+
pkattnos=bms_add_member(pkattnos,
953+
attnums[i]-FirstLowInvalidHeapAttributeNumber);
954+
}
955+
*constraintOid=HeapTupleGetOid(tuple);
956+
957+
/* No need to search further */
958+
break;
959+
}
960+
961+
systable_endscan(scan);
962+
963+
heap_close(pg_constraint,AccessShareLock);
964+
965+
returnpkattnos;
966+
}
967+
874968
/*
875969
* Determine whether a relation can be proven functionally dependent on
876970
* a set of grouping columns. If so, return TRUE and add the pg_constraint

‎src/backend/optimizer/plan/planner.c

Lines changed: 159 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,9 @@
2020

2121
#include"access/htup_details.h"
2222
#include"access/parallel.h"
23+
#include"access/sysattr.h"
2324
#include"access/xact.h"
25+
#include"catalog/pg_constraint_fn.h"
2426
#include"executor/executor.h"
2527
#include"executor/nodeAgg.h"
2628
#include"foreign/fdwapi.h"
@@ -89,6 +91,7 @@ static double preprocess_limit(PlannerInfo *root,
8991
doubletuple_fraction,
9092
int64*offset_est,int64*count_est);
9193
staticboollimit_needed(Query*parse);
94+
staticvoidremove_useless_groupby_columns(PlannerInfo*root);
9295
staticList*preprocess_groupclause(PlannerInfo*root,List*force);
9396
staticList*extract_rollup_sets(List*groupingSets);
9497
staticList*reorder_grouping_sets(List*groupingSets,List*sortclause);
@@ -710,6 +713,9 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
710713
}
711714
parse->havingQual= (Node*)newHaving;
712715

716+
/* Remove any redundant GROUP BY columns */
717+
remove_useless_groupby_columns(root);
718+
713719
/*
714720
* If we have any outer joins, try to reduce them to plain inner joins.
715721
* This step is most easily done after we've done expression
@@ -3207,6 +3213,159 @@ limit_needed(Query *parse)
32073213
}
32083214

32093215

3216+
/*
3217+
* remove_useless_groupby_columns
3218+
*Remove any columns in the GROUP BY clause that are redundant due to
3219+
*being functionally dependent on other GROUP BY columns.
3220+
*
3221+
* Since some other DBMSes do not allow references to ungrouped columns, it's
3222+
* not unusual to find all columns listed in GROUP BY even though listing the
3223+
* primary-key columns would be sufficient. Deleting such excess columns
3224+
* avoids redundant sorting work, so it's worth doing. When we do this, we
3225+
* must mark the plan as dependent on the pkey constraint (compare the
3226+
* parser's check_ungrouped_columns() and check_functional_grouping()).
3227+
*
3228+
* In principle, we could treat any NOT-NULL columns appearing in a UNIQUE
3229+
* index as the determining columns. But as with check_functional_grouping(),
3230+
* there's currently no way to represent dependency on a NOT NULL constraint,
3231+
* so we consider only the pkey for now.
3232+
*/
3233+
staticvoid
3234+
remove_useless_groupby_columns(PlannerInfo*root)
3235+
{
3236+
Query*parse=root->parse;
3237+
Bitmapset**groupbyattnos;
3238+
Bitmapset**surplusvars;
3239+
ListCell*lc;
3240+
intrelid;
3241+
3242+
/* No chance to do anything if there are less than two GROUP BY items */
3243+
if (list_length(parse->groupClause)<2)
3244+
return;
3245+
3246+
/* Don't fiddle with the GROUP BY clause if the query has grouping sets */
3247+
if (parse->groupingSets)
3248+
return;
3249+
3250+
/*
3251+
* Scan the GROUP BY clause to find GROUP BY items that are simple Vars.
3252+
* Fill groupbyattnos[k] with a bitmapset of the column attnos of RTE k
3253+
* that are GROUP BY items.
3254+
*/
3255+
groupbyattnos= (Bitmapset**)palloc0(sizeof(Bitmapset*)*
3256+
(list_length(parse->rtable)+1));
3257+
foreach(lc,parse->groupClause)
3258+
{
3259+
SortGroupClause*sgc= (SortGroupClause*)lfirst(lc);
3260+
TargetEntry*tle=get_sortgroupclause_tle(sgc,parse->targetList);
3261+
Var*var= (Var*)tle->expr;
3262+
3263+
/*
3264+
* Ignore non-Vars and Vars from other query levels.
3265+
*
3266+
* XXX in principle, stable expressions containing Vars could also be
3267+
* removed, if all the Vars are functionally dependent on other GROUP
3268+
* BY items. But it's not clear that such cases occur often enough to
3269+
* be worth troubling over.
3270+
*/
3271+
if (!IsA(var,Var)||
3272+
var->varlevelsup>0)
3273+
continue;
3274+
3275+
/* OK, remember we have this Var */
3276+
relid=var->varno;
3277+
Assert(relid <=list_length(parse->rtable));
3278+
groupbyattnos[relid]=bms_add_member(groupbyattnos[relid],
3279+
var->varattno-FirstLowInvalidHeapAttributeNumber);
3280+
}
3281+
3282+
/*
3283+
* Consider each relation and see if it is possible to remove some of its
3284+
* Vars from GROUP BY. For simplicity and speed, we do the actual removal
3285+
* in a separate pass. Here, we just fill surplusvars[k] with a bitmapset
3286+
* of the column attnos of RTE k that are removable GROUP BY items.
3287+
*/
3288+
surplusvars=NULL;/* don't allocate array unless required */
3289+
relid=0;
3290+
foreach(lc,parse->rtable)
3291+
{
3292+
RangeTblEntry*rte= (RangeTblEntry*)lfirst(lc);
3293+
Bitmapset*relattnos;
3294+
Bitmapset*pkattnos;
3295+
OidconstraintOid;
3296+
3297+
relid++;
3298+
3299+
/* Only plain relations could have primary-key constraints */
3300+
if (rte->rtekind!=RTE_RELATION)
3301+
continue;
3302+
3303+
/* Nothing to do unless this rel has multiple Vars in GROUP BY */
3304+
relattnos=groupbyattnos[relid];
3305+
if (bms_membership(relattnos)!=BMS_MULTIPLE)
3306+
continue;
3307+
3308+
/*
3309+
* Can't remove any columns for this rel if there is no suitable
3310+
* (i.e., nondeferrable) primary key constraint.
3311+
*/
3312+
pkattnos=get_primary_key_attnos(rte->relid, false,&constraintOid);
3313+
if (pkattnos==NULL)
3314+
continue;
3315+
3316+
/*
3317+
* If the primary key is a proper subset of relattnos then we have
3318+
* some items in the GROUP BY that can be removed.
3319+
*/
3320+
if (bms_subset_compare(pkattnos,relattnos)==BMS_SUBSET1)
3321+
{
3322+
/*
3323+
* To easily remember whether we've found anything to do, we don't
3324+
* allocate the surplusvars[] array until we find something.
3325+
*/
3326+
if (surplusvars==NULL)
3327+
surplusvars= (Bitmapset**)palloc0(sizeof(Bitmapset*)*
3328+
(list_length(parse->rtable)+1));
3329+
3330+
/* Remember the attnos of the removable columns */
3331+
surplusvars[relid]=bms_difference(relattnos,pkattnos);
3332+
3333+
/* Also, mark the resulting plan as dependent on this constraint */
3334+
parse->constraintDeps=lappend_oid(parse->constraintDeps,
3335+
constraintOid);
3336+
}
3337+
}
3338+
3339+
/*
3340+
* If we found any surplus Vars, build a new GROUP BY clause without them.
3341+
* (Note: this may leave some TLEs with unreferenced ressortgroupref
3342+
* markings, but that's harmless.)
3343+
*/
3344+
if (surplusvars!=NULL)
3345+
{
3346+
List*new_groupby=NIL;
3347+
3348+
foreach(lc,parse->groupClause)
3349+
{
3350+
SortGroupClause*sgc= (SortGroupClause*)lfirst(lc);
3351+
TargetEntry*tle=get_sortgroupclause_tle(sgc,parse->targetList);
3352+
Var*var= (Var*)tle->expr;
3353+
3354+
/*
3355+
* New list must include non-Vars, outer Vars, and anything not
3356+
* marked as surplus.
3357+
*/
3358+
if (!IsA(var,Var)||
3359+
var->varlevelsup>0||
3360+
!bms_is_member(var->varattno-FirstLowInvalidHeapAttributeNumber,
3361+
surplusvars[var->varno]))
3362+
new_groupby=lappend(new_groupby,sgc);
3363+
}
3364+
3365+
parse->groupClause=new_groupby;
3366+
}
3367+
}
3368+
32103369
/*
32113370
* preprocess_groupclause - do preparatory work on GROUP BY clause
32123371
*

‎src/include/catalog/pg_constraint_fn.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -71,6 +71,9 @@ extern void AlterConstraintNamespaces(Oid ownerId, Oid oldNspId,
7171
externOidget_relation_constraint_oid(Oidrelid,constchar*conname,boolmissing_ok);
7272
externOidget_domain_constraint_oid(Oidtypid,constchar*conname,boolmissing_ok);
7373

74+
externBitmapset*get_primary_key_attnos(Oidrelid,booldeferrableOk,
75+
Oid*constraintOid);
76+
7477
externboolcheck_functional_grouping(Oidrelid,
7578
Indexvarno,Indexvarlevelsup,
7679
List*grouping_columns,

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

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -856,6 +856,70 @@ ERROR: aggregate function calls cannot be nested
856856
LINE 1: select (select max(min(unique1)) from int8_tbl) from tenk1;
857857
^
858858
--
859+
-- Test removal of redundant GROUP BY columns
860+
--
861+
create temp table t1 (a int, b int, c int, d int, primary key (a, b));
862+
create temp table t2 (x int, y int, z int, primary key (x, y));
863+
create temp table t3 (a int, b int, c int, primary key(a, b) deferrable);
864+
-- Non-primary-key columns can be removed from GROUP BY
865+
explain (costs off) select * from t1 group by a,b,c,d;
866+
QUERY PLAN
867+
----------------------
868+
HashAggregate
869+
Group Key: a, b
870+
-> Seq Scan on t1
871+
(3 rows)
872+
873+
-- No removal can happen if the complete PK is not present in GROUP BY
874+
explain (costs off) select a,c from t1 group by a,c,d;
875+
QUERY PLAN
876+
----------------------
877+
HashAggregate
878+
Group Key: a, c, d
879+
-> Seq Scan on t1
880+
(3 rows)
881+
882+
-- Test removal across multiple relations
883+
explain (costs off) select *
884+
from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
885+
group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.y,t2.z;
886+
QUERY PLAN
887+
-------------------------------------------------------
888+
Group
889+
Group Key: t1.a, t1.b, t2.x, t2.y
890+
-> Merge Join
891+
Merge Cond: ((t1.a = t2.x) AND (t1.b = t2.y))
892+
-> Index Scan using t1_pkey on t1
893+
-> Index Scan using t2_pkey on t2
894+
(6 rows)
895+
896+
-- Test case where t1 can be optimized but not t2
897+
explain (costs off) select t1.*,t2.x,t2.z
898+
from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
899+
group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.z;
900+
QUERY PLAN
901+
-------------------------------------------------------
902+
HashAggregate
903+
Group Key: t1.a, t1.b, t2.x, t2.z
904+
-> Merge Join
905+
Merge Cond: ((t1.a = t2.x) AND (t1.b = t2.y))
906+
-> Index Scan using t1_pkey on t1
907+
-> Index Scan using t2_pkey on t2
908+
(6 rows)
909+
910+
-- Cannot optimize when PK is deferrable
911+
explain (costs off) select * from t3 group by a,b,c;
912+
QUERY PLAN
913+
----------------------
914+
HashAggregate
915+
Group Key: a, b, c
916+
-> Seq Scan on t3
917+
(3 rows)
918+
919+
drop table t1;
920+
drop table t2;
921+
drop table t3;
922+
--
859923
-- Test combinations of DISTINCT and/or ORDER BY
860924
--
861925
select array_agg(a order by b)

0 commit comments

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