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

Commit1ceb103

Browse files
author
Amit Kapila
committed
Don't allow LIMIT/OFFSET clause within sub-selects to be pushed to workers.
Allowing sub-select containing LIMIT/OFFSET in workers can lead toinconsistent results at the top-level as there is no guarantee that therow order will be fully deterministic. The fix is to prohibit pushingLIMIT/OFFSET within sub-selects to workers.Reported-by: Andrew FletcherBug: 15324Author: Amit KapilaReviewed-by: Dilip KumarBackpatch-through: 9.6Discussion:https://postgr.es/m/153417684333.10284.11356259990921828616@wrigleys.postgresql.org
1 parentede7d81 commit1ceb103

File tree

5 files changed

+40
-2
lines changed

5 files changed

+40
-2
lines changed

‎src/backend/optimizer/path/allpaths.c

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -612,7 +612,20 @@ set_rel_consider_parallel(PlannerInfo *root, RelOptInfo *rel,
612612
* the SubqueryScanPath as not parallel-safe. (Note that
613613
* set_subquery_pathlist() might push some of these quals down
614614
* into the subquery itself, but that doesn't change anything.)
615+
*
616+
* We can't push sub-select containing LIMIT/OFFSET to workers as
617+
* there is no guarantee that the row order will be fully
618+
* deterministic, and applying LIMIT/OFFSET will lead to
619+
* inconsistent results at the top-level. (In some cases, where
620+
* the result is ordered, we could relax this restriction. But it
621+
* doesn't currently seem worth expending extra effort to do so.)
615622
*/
623+
{
624+
Query*subquery=castNode(Query,rte->subquery);
625+
626+
if (limit_needed(subquery))
627+
return;
628+
}
616629
break;
617630

618631
caseRTE_JOIN:

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

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -121,7 +121,6 @@ static void preprocess_rowmarks(PlannerInfo *root);
121121
staticdoublepreprocess_limit(PlannerInfo*root,
122122
doubletuple_fraction,
123123
int64*offset_est,int64*count_est);
124-
staticboollimit_needed(Query*parse);
125124
staticvoidremove_useless_groupby_columns(PlannerInfo*root);
126125
staticList*preprocess_groupclause(PlannerInfo*root,List*force);
127126
staticList*extract_rollup_sets(List*groupingSets);
@@ -2697,7 +2696,7 @@ preprocess_limit(PlannerInfo *root, double tuple_fraction,
26972696
* a key distinction: here we need hard constants in OFFSET/LIMIT, whereas
26982697
* in preprocess_limit it's good enough to consider estimated values.
26992698
*/
2700-
staticbool
2699+
bool
27012700
limit_needed(Query*parse)
27022701
{
27032702
Node*node;

‎src/include/optimizer/planner.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,8 @@ extern bool is_dummy_plan(Plan *plan);
4646
externRowMarkTypeselect_rowmark_type(RangeTblEntry*rte,
4747
LockClauseStrengthstrength);
4848

49+
externboollimit_needed(Query*parse);
50+
4951
externvoidmark_partial_aggref(Aggref*agg,AggSplitaggsplit);
5052

5153
externPath*get_cheapest_fractional_path(RelOptInfo*rel,

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

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -599,6 +599,25 @@ explain (costs off, verbose)
599599
Output: b.unique1
600600
(18 rows)
601601

602+
-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
603+
explain (costs off)
604+
select * from tenk1 a where two in
605+
(select two from tenk1 b where stringu1 like '%AAAA' limit 3);
606+
QUERY PLAN
607+
---------------------------------------------------------------
608+
Hash Semi Join
609+
Hash Cond: (a.two = b.two)
610+
-> Gather
611+
Workers Planned: 4
612+
-> Parallel Seq Scan on tenk1 a
613+
-> Hash
614+
-> Limit
615+
-> Gather
616+
Workers Planned: 4
617+
-> Parallel Seq Scan on tenk1 b
618+
Filter: (stringu1 ~~ '%AAAA'::text)
619+
(11 rows)
620+
602621
-- to increase the parallel query test coverage
603622
EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;
604623
QUERY PLAN

‎src/test/regress/sql/select_parallel.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -210,6 +210,11 @@ explain (costs off, verbose)
210210
(select unique1, row_number() over()from tenk1 b);
211211

212212

213+
-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
214+
explain (costs off)
215+
select*from tenk1 awhere twoin
216+
(select twofrom tenk1 bwhere stringu1like'%AAAA'limit3);
217+
213218
-- to increase the parallel query test coverage
214219
EXPLAIN (analyze, timing off, summary off, costs off)SELECT*FROM tenk1;
215220

0 commit comments

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