Skip to content

Commit dfdf07a

Browse files
tglsfdctglsfdc
committed
Fix up LIMIT/OFFSET planning so that we cope with non-constant LIMIT
or OFFSET clauses by using estimate_expression_value(). The main advantage of this is that if the expression is a Param and we have a value for the Param, we'll use that value rather than defaulting. Also, fix some thinkos in the logic for combining LIMIT/OFFSET with an externally supplied tuple fraction (this covers cases like EXISTS(...LIMIT...)). And make sure the results of all this are shown by EXPLAIN. Per a gripe from Merlin Moncure.
1 parent 96f63ae commit dfdf07a

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
return node;
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+
int offset_est, int count_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-
int32 offset = DatumGetInt32(limito->constvalue);
2703+
double offset_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-
int32 count = DatumGetInt32(limitc->constvalue);
2722+
double count_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)