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

Commitd543170

Browse files
committed
Don't split up SRFs when choosing to postpone SELECT output expressions.
In commit9118d03 we taught the planner to postpone evaluation ofset-returning functions in a SELECT's targetlist until after any sort doneto satisfy ORDER BY. However, if we postpone some SRFs this way whileothers do not get postponed (because they're sort or group key columns)we will break the traditional behavior by which all SRFs in the tlist runin-step during ExecTargetList(), so that you get the least common multipleof their periods not the product. Fix make_sort_input_target() so it willnot split up SRF evaluation in such cases.There is still a hazard of similar odd behavior if there's a SRF in agrouping column and another one that isn't, but that was true beforeand we're just trying to preserve bug-compatibility with the traditionalbehavior. This whole area is overdue to be rethought and reimplemented,but we'll try to avoid changing behavior until then.Per report from Regina Obe.
1 parent7caaeaf commitd543170

File tree

3 files changed

+86
-13
lines changed

3 files changed

+86
-13
lines changed

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

Lines changed: 35 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -4615,11 +4615,16 @@ make_pathkeys_for_window(PlannerInfo *root, WindowClause *wc,
46154615
*
46164616
* Our current policy is to postpone volatile expressions till after the sort
46174617
* unconditionally (assuming that that's possible, ie they are in plain tlist
4618-
* columns and not ORDER BY/GROUP BY/DISTINCT columns). We also postpone
4619-
* set-returning expressions unconditionally (if possible), because running
4620-
* them beforehand would bloat the sort dataset, and because it might cause
4621-
* unexpected output order if the sort isn't stable. Expensive expressions
4622-
* are postponed if there is a LIMIT, or if root->tuple_fraction shows that
4618+
* columns and not ORDER BY/GROUP BY/DISTINCT columns). We also prefer to
4619+
* postpone set-returning expressions, because running them beforehand would
4620+
* bloat the sort dataset, and because it might cause unexpected output order
4621+
* if the sort isn't stable. However there's a constraint on that: all SRFs
4622+
* in the tlist should be evaluated at the same plan step, so that they can
4623+
* run in sync in ExecTargetList. So if any SRFs are in sort columns, we
4624+
* mustn't postpone any SRFs. (Note that in principle that policy should
4625+
* probably get applied to the group/window input targetlists too, but we
4626+
* have not done that historically.) Lastly, expensive expressions are
4627+
* postponed if there is a LIMIT, or if root->tuple_fraction shows that
46234628
* partial evaluation of the query is possible (if neither is true, we expect
46244629
* to have to evaluate the expressions for every row anyway), or if there are
46254630
* any volatile or set-returning expressions (since once we've put in a
@@ -4664,10 +4669,13 @@ make_sort_input_target(PlannerInfo *root,
46644669
Query*parse=root->parse;
46654670
PathTarget*input_target;
46664671
intncols;
4672+
bool*col_is_srf;
46674673
bool*postpone_col;
46684674
boolhave_srf;
46694675
boolhave_volatile;
46704676
boolhave_expensive;
4677+
boolhave_srf_sortcols;
4678+
boolpostpone_srfs;
46714679
List*postponable_cols;
46724680
List*postponable_vars;
46734681
inti;
@@ -4680,8 +4688,9 @@ make_sort_input_target(PlannerInfo *root,
46804688

46814689
/* Inspect tlist and collect per-column information */
46824690
ncols=list_length(final_target->exprs);
4691+
col_is_srf= (bool*)palloc0(ncols*sizeof(bool));
46834692
postpone_col= (bool*)palloc0(ncols*sizeof(bool));
4684-
have_srf=have_volatile=have_expensive= false;
4693+
have_srf=have_volatile=have_expensive=have_srf_sortcols=false;
46854694

46864695
i=0;
46874696
foreach(lc,final_target->exprs)
@@ -4699,17 +4708,18 @@ make_sort_input_target(PlannerInfo *root,
46994708
if (final_target->sortgrouprefs[i]==0)
47004709
{
47014710
/*
4702-
* If it returns a set or is volatile, that's an unconditional
4703-
* reason to postpone. Check the SRF case first because we must
4704-
* know whether we have any postponed SRFs.
4711+
* Check for SRF or volatile functions. Check the SRF case first
4712+
* because we must know whether we have any postponed SRFs.
47054713
*/
47064714
if (expression_returns_set((Node*)expr))
47074715
{
4708-
postpone_col[i]= true;
4716+
/* We'll decide below whether these are postponable */
4717+
col_is_srf[i]= true;
47094718
have_srf= true;
47104719
}
47114720
elseif (contain_volatile_functions((Node*)expr))
47124721
{
4722+
/* Unconditionally postpone */
47134723
postpone_col[i]= true;
47144724
have_volatile= true;
47154725
}
@@ -4736,14 +4746,26 @@ make_sort_input_target(PlannerInfo *root,
47364746
}
47374747
}
47384748
}
4749+
else
4750+
{
4751+
/* For sortgroupref cols, just check if any contain SRFs */
4752+
if (!have_srf_sortcols&&
4753+
expression_returns_set((Node*)expr))
4754+
have_srf_sortcols= true;
4755+
}
47394756

47404757
i++;
47414758
}
47424759

4760+
/*
4761+
* We can postpone SRFs if we have some but none are in sortgroupref cols.
4762+
*/
4763+
postpone_srfs= (have_srf&& !have_srf_sortcols);
4764+
47434765
/*
47444766
* If we don't need a post-sort projection, just return final_target.
47454767
*/
4746-
if (!(have_srf||have_volatile||
4768+
if (!(postpone_srfs||have_volatile||
47474769
(have_expensive&&
47484770
(parse->limitCount||root->tuple_fraction>0))))
47494771
returnfinal_target;
@@ -4754,7 +4776,7 @@ make_sort_input_target(PlannerInfo *root,
47544776
* rely on the query's LIMIT (if any) to bound the number of rows it needs
47554777
* to return.
47564778
*/
4757-
*have_postponed_srfs=have_srf;
4779+
*have_postponed_srfs=postpone_srfs;
47584780

47594781
/*
47604782
* Construct the sort-input target, taking all non-postponable columns and
@@ -4769,7 +4791,7 @@ make_sort_input_target(PlannerInfo *root,
47694791
{
47704792
Expr*expr= (Expr*)lfirst(lc);
47714793

4772-
if (postpone_col[i])
4794+
if (postpone_col[i]|| (postpone_srfs&&col_is_srf[i]))
47734795
postponable_cols=lappend(postponable_cols,expr);
47744796
else
47754797
add_column_to_pathtarget(input_target,expr,

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

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -258,3 +258,41 @@ select unique1, unique2, generate_series(1,10)
258258
0 | 9998 | 7
259259
(7 rows)
260260

261+
-- use of random() is to keep planner from folding the expressions together
262+
explain (verbose, costs off)
263+
select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2;
264+
QUERY PLAN
265+
------------------------------------------------------------------------------------------------------
266+
Result
267+
Output: generate_series(0, 2), generate_series(((random() * '0.1'::double precision))::integer, 2)
268+
(2 rows)
269+
270+
select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2;
271+
s1 | s2
272+
----+----
273+
0 | 0
274+
1 | 1
275+
2 | 2
276+
(3 rows)
277+
278+
explain (verbose, costs off)
279+
select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2
280+
order by s2 desc;
281+
QUERY PLAN
282+
------------------------------------------------------------------------------------------------------------
283+
Sort
284+
Output: (generate_series(0, 2)), (generate_series(((random() * '0.1'::double precision))::integer, 2))
285+
Sort Key: (generate_series(((random() * '0.1'::double precision))::integer, 2)) DESC
286+
-> Result
287+
Output: generate_series(0, 2), generate_series(((random() * '0.1'::double precision))::integer, 2)
288+
(5 rows)
289+
290+
select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2
291+
order by s2 desc;
292+
s1 | s2
293+
----+----
294+
2 | 2
295+
1 | 1
296+
0 | 0
297+
(3 rows)
298+

‎src/test/regress/sql/limit.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -78,3 +78,16 @@ select unique1, unique2, generate_series(1,10)
7878

7979
select unique1, unique2, generate_series(1,10)
8080
from tenk1order by tenthouslimit7;
81+
82+
-- use of random() is to keep planner from folding the expressions together
83+
explain (verbose, costs off)
84+
select generate_series(0,2)as s1, generate_series((random()*.1)::int,2)as s2;
85+
86+
select generate_series(0,2)as s1, generate_series((random()*.1)::int,2)as s2;
87+
88+
explain (verbose, costs off)
89+
select generate_series(0,2)as s1, generate_series((random()*.1)::int,2)as s2
90+
order by s2desc;
91+
92+
select generate_series(0,2)as s1, generate_series((random()*.1)::int,2)as s2
93+
order by s2desc;

0 commit comments

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