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

Commita5be406

Browse files
committed
Don't remove surplus columns from GROUP BY for inheritance parents
d4c3a15 added code to remove columns that were not part of a table'sPRIMARY KEY constraint from the GROUP BY clause when all the primary keycolumns were present in the group by. This is fine to do since we knowthat there will only be one row per group coming from this relation.However, the logic failed to consider inheritance parent relations. Thesecan have child relations without a primary key, but even if they did, theycould duplicate one of the parent's rows or one from another childrelation. In this case, those additional GROUP BY columns are required.Fix this by disabling the optimization for inheritance parent tables.In v11 and beyond, partitioned tables are fine since partitions cannotoverlap and before v11 partitioned tables could not have a primary key.Reported-by: Manuel RiggerDiscussion:http://postgr.es/m/CA+u7OA7VLKf_vEr6kLF3MnWSA9LToJYncgpNX2tQ-oWzYCBQAw@mail.gmail.comBackpatch-through: 9.6
1 parent2a16121 commita5be406

File tree

3 files changed

+75
-2
lines changed

3 files changed

+75
-2
lines changed

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

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3124,6 +3124,14 @@ remove_useless_groupby_columns(PlannerInfo *root)
31243124
if (rte->rtekind!=RTE_RELATION)
31253125
continue;
31263126

3127+
/*
3128+
* We must skip inheritance parent tables as some of the child rels
3129+
* may cause duplicate rows. This cannot happen with partitioned
3130+
* tables, however.
3131+
*/
3132+
if (rte->inh&&rte->relkind!=RELKIND_PARTITIONED_TABLE)
3133+
continue;
3134+
31273135
/* Nothing to do unless this rel has multiple Vars in GROUP BY */
31283136
relattnos=groupbyattnos[relid];
31293137
if (bms_membership(relattnos)!=BMS_MULTIPLE)

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

Lines changed: 44 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1147,9 +1147,52 @@ explain (costs off) select * from t3 group by a,b,c;
11471147
-> Seq Scan on t3
11481148
(3 rows)
11491149

1150-
drop table t1;
1150+
create temp table t1c () inherits (t1);
1151+
-- Ensure we don't remove any columns when t1 has a child table
1152+
explain (costs off) select * from t1 group by a,b,c,d;
1153+
QUERY PLAN
1154+
-------------------------------------
1155+
HashAggregate
1156+
Group Key: t1.a, t1.b, t1.c, t1.d
1157+
-> Append
1158+
-> Seq Scan on t1
1159+
-> Seq Scan on t1c
1160+
(5 rows)
1161+
1162+
-- Okay to remove columns if we're only querying the parent.
1163+
explain (costs off) select * from only t1 group by a,b,c,d;
1164+
QUERY PLAN
1165+
----------------------
1166+
HashAggregate
1167+
Group Key: a, b
1168+
-> Seq Scan on t1
1169+
(3 rows)
1170+
1171+
create temp table p_t1 (
1172+
a int,
1173+
b int,
1174+
c int,
1175+
d int,
1176+
primary key(a,b)
1177+
) partition by list(a);
1178+
create temp table p_t1_1 partition of p_t1 for values in(1);
1179+
create temp table p_t1_2 partition of p_t1 for values in(2);
1180+
-- Ensure we can remove non-PK columns for partitioned tables.
1181+
explain (costs off) select * from p_t1 group by a,b,c,d;
1182+
QUERY PLAN
1183+
---------------------------------
1184+
HashAggregate
1185+
Group Key: p_t1_1.a, p_t1_1.b
1186+
-> Append
1187+
-> Seq Scan on p_t1_1
1188+
-> Seq Scan on p_t1_2
1189+
(5 rows)
1190+
1191+
drop table t1 cascade;
1192+
NOTICE: drop cascades to table t1c
11511193
drop table t2;
11521194
drop table t3;
1195+
drop table p_t1;
11531196
--
11541197
-- Test combinations of DISTINCT and/or ORDER BY
11551198
--

‎src/test/regress/sql/aggregates.sql

Lines changed: 23 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -406,9 +406,31 @@ group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.z;
406406
-- Cannot optimize when PK is deferrable
407407
explain (costs off)select*from t3group by a,b,c;
408408

409-
droptable t1;
409+
create temp table t1c () inherits (t1);
410+
411+
-- Ensure we don't remove any columns when t1 has a child table
412+
explain (costs off)select*from t1group by a,b,c,d;
413+
414+
-- Okay to remove columns if we're only querying the parent.
415+
explain (costs off)select*from only t1group by a,b,c,d;
416+
417+
create temp table p_t1 (
418+
aint,
419+
bint,
420+
cint,
421+
dint,
422+
primary key(a,b)
423+
) partition by list(a);
424+
create temp table p_t1_1 partition of p_t1 forvaluesin(1);
425+
create temp table p_t1_2 partition of p_t1 forvaluesin(2);
426+
427+
-- Ensure we can remove non-PK columns for partitioned tables.
428+
explain (costs off)select*from p_t1group by a,b,c,d;
429+
430+
droptable t1 cascade;
410431
droptable t2;
411432
droptable t3;
433+
droptable p_t1;
412434

413435
--
414436
-- Test combinations of DISTINCT and/or ORDER BY

0 commit comments

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