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

Commitdfdf07a

Browse files
committed
Fix up LIMIT/OFFSET planning so that we cope with non-constant LIMIT
or OFFSET clauses by using estimate_expression_value(). The main advantageof this is that if the expression is a Param and we have a value for theParam, we'll use that value rather than defaulting. Also, fix somethinkos in the logic for combining LIMIT/OFFSET with an externallysupplied tuple fraction (this covers cases like EXISTS(...LIMIT...)).And make sure the results of all this are shown by EXPLAIN. Per agripe from Merlin Moncure.
1 parent96f63ae commitdfdf07a

File tree

4 files changed

+201
-101
lines changed

4 files changed

+201
-101
lines changed

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

Lines changed: 48 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/createplan.c,v 1.196 2005/07/28 20:26:21 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/createplan.c,v 1.197 2005/08/18 17:51:11 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -2673,55 +2673,67 @@ make_setop(SetOpCmd cmd, Plan *lefttree,
26732673
returnnode;
26742674
}
26752675

2676+
/*
2677+
* Note: offset_est and count_est are passed in to save having to repeat
2678+
* work already done to estimate the values of the limitOffset and limitCount
2679+
* expressions. Their values are as returned by preprocess_limit (0 means
2680+
* "not relevant", -1 means "couldn't estimate"). Keep the code below in sync
2681+
* with that function!
2682+
*/
26762683
Limit*
2677-
make_limit(Plan*lefttree,Node*limitOffset,Node*limitCount)
2684+
make_limit(Plan*lefttree,Node*limitOffset,Node*limitCount,
2685+
intoffset_est,intcount_est)
26782686
{
26792687
Limit*node=makeNode(Limit);
26802688
Plan*plan=&node->plan;
26812689

26822690
copy_plan_costsize(plan,lefttree);
26832691

26842692
/*
2685-
* If offset/count are constants, adjust the output rows count and
2686-
* costs accordingly. This is only a cosmetic issue if we are at top
2687-
* level, but if we are building a subquery then it's important to
2688-
* report correct info to the outer planner.
2693+
* Adjust the output rows count and costs according to the offset/limit.
2694+
* This is only a cosmetic issue if we are at top level, but if we are
2695+
* building a subquery then it's important to report correct info to the
2696+
* outer planner.
2697+
*
2698+
* When the offset or count couldn't be estimated, use 10% of the
2699+
* estimated number of rows emitted from the subplan.
26892700
*/
2690-
if (limitOffset&&IsA(limitOffset,Const))
2701+
if (offset_est!=0)
26912702
{
2692-
Const*limito= (Const*)limitOffset;
2693-
int32offset=DatumGetInt32(limito->constvalue);
2703+
doubleoffset_rows;
26942704

2695-
if (!limito->constisnull&&offset>0)
2696-
{
2697-
if (offset>plan->plan_rows)
2698-
offset= (int32)plan->plan_rows;
2699-
if (plan->plan_rows>0)
2700-
plan->startup_cost+=
2701-
(plan->total_cost-plan->startup_cost)
2702-
* ((double)offset) /plan->plan_rows;
2703-
plan->plan_rows-=offset;
2704-
if (plan->plan_rows<1)
2705-
plan->plan_rows=1;
2706-
}
2705+
if (offset_est>0)
2706+
offset_rows= (double)offset_est;
2707+
else
2708+
offset_rows=clamp_row_est(lefttree->plan_rows*0.10);
2709+
if (offset_rows>plan->plan_rows)
2710+
offset_rows=plan->plan_rows;
2711+
if (plan->plan_rows>0)
2712+
plan->startup_cost+=
2713+
(plan->total_cost-plan->startup_cost)
2714+
*offset_rows /plan->plan_rows;
2715+
plan->plan_rows-=offset_rows;
2716+
if (plan->plan_rows<1)
2717+
plan->plan_rows=1;
27072718
}
2708-
if (limitCount&&IsA(limitCount,Const))
2719+
2720+
if (count_est!=0)
27092721
{
2710-
Const*limitc= (Const*)limitCount;
2711-
int32count=DatumGetInt32(limitc->constvalue);
2722+
doublecount_rows;
27122723

2713-
if (!limitc->constisnull&&count >=0)
2714-
{
2715-
if (count>plan->plan_rows)
2716-
count= (int32)plan->plan_rows;
2717-
if (plan->plan_rows>0)
2718-
plan->total_cost=plan->startup_cost+
2719-
(plan->total_cost-plan->startup_cost)
2720-
* ((double)count) /plan->plan_rows;
2721-
plan->plan_rows=count;
2722-
if (plan->plan_rows<1)
2723-
plan->plan_rows=1;
2724-
}
2724+
if (count_est>0)
2725+
count_rows= (double)count_est;
2726+
else
2727+
count_rows=clamp_row_est(lefttree->plan_rows*0.10);
2728+
if (count_rows>plan->plan_rows)
2729+
count_rows=plan->plan_rows;
2730+
if (plan->plan_rows>0)
2731+
plan->total_cost=plan->startup_cost+
2732+
(plan->total_cost-plan->startup_cost)
2733+
*count_rows /plan->plan_rows;
2734+
plan->plan_rows=count_rows;
2735+
if (plan->plan_rows<1)
2736+
plan->plan_rows=1;
27252737
}
27262738

27272739
plan->targetlist=copyObject(lefttree->targetlist);

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

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planagg.c,v 1.7 2005/07/28 20:26:21 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planagg.c,v 1.8 2005/08/18 17:51:11 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -519,7 +519,8 @@ make_agg_subplan(PlannerInfo *root, MinMaxAggInfo *info, List *constant_quals)
519519

520520
plan= (Plan*)make_limit(plan,
521521
subparse->limitOffset,
522-
subparse->limitCount);
522+
subparse->limitCount,
523+
0,1);
523524

524525
/*
525526
* Convert the plan into an InitPlan, and make a Param for its result.

0 commit comments

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