On Tue, Aug 20, 2019 at 9:10 AM Kyotaro Horiguchi <[email protected]>
wrote:
> Hi,
>
> At Wed, 7 Aug 2019 10:20:09 +0300, Surafel Temesgen <[email protected]>
> wrote in <
> calay4q98xbvhtz4yj9dccmg2-s1_jurr7fyanfw+bkmr22o...@mail.gmail.com>
> > Hi
> > On Wed, Aug 7, 2019 at 6:11 AM Kyotaro Horiguchi <
> [email protected]>
> > wrote:
> >
> > >
> > > I have some comments.
> > >
> > > This patch uses distinct parameters for exact number and
> > > percentage. On the othe hand planner has a notion of
> > > tuple_fraction covering the both. The same handling is also used
> > > for tuple number estimation. Using the same scheme will make
> > > things far simpler. See the comment of grouping_planner().
> > >
> > >
> > Its because of data type difference .In planner the data type is the same
>
> I meant that, with the usage of tuple_fraction, one variable can
> represent both the absolute limit and relative limit. This
> simplifies parse structs.
>
In grouping_planner the patch set tuple bound to -1 in create_ordered_paths
because it iterate until the end in percentage. Did that answer your
question?
>
>
> > > In executor part, addition to LimiteState.position, this patch
> > > uses LimiteState.backwardPosition to count how many tuples we're
> > > back from the end of the current tuplestore. But things will get
> > > simpler by just asking the tuplestore for the number of holding
> > > tuples.
> > >
> > >
> > backwardPosition hold how many tuple returned in backward scan
>
> I meant that we don't need to hold the number in estate.
>
I did it this way because I didn't find an API in tuplestore to do this
>
> > > + slot = node->subSlot;
> > >
> > > Why is this needed? The variable is properly set before use and
> > > the assignment is bogus in the first place.
> > >
> > >
> > its because Tuplestore needs initialized slot.
>
> I meant that the initilized slot is overwritten before first use.
>
> > > The new code block in LIMIT_RESCAN in ExecLimit is useless since
> > > it is exatctly the same with existing code block. Why didn't you
> > > use the existing if block?
> > >
> >
> > But they test different scenario
>
> I meant that the two different scenario can share the code block.
>
Sorry for not clarifying will .One have to be check before offsetting and
the other is after offsetting
>
> > > > if (node->limitOption == PERCENTAGE)
> > > + {
> > > + node->perExactCount = ceil(node->percent *
> > > node->position / 100.0);
> > > +
> > > +
> > >
> > > node->position is the number of tuples returned to upper node so
> > > far (not the number of tuples this node has read from the lower
> > > node so far). I don't understand what the expression means.
> > >
> >
> > node->position hold the number of tuples this node has read from the
> lower
> > node so far. see LIMIT_RESCAN state
>
> Reallly? node->position is incremented when
> tuplestore_gettupleslot_heaptuple() succeeded and reutnrs the
> tuple to the caller immediately...
>
> > > + if (node->perExactCount == node->perExactCount +
> 1)
> > > + node->perExactCount++;
> > >
> > > What? The condition never be true. As the result, the following
> > > if block below won't run.
> > >
> >
> > it became true according to the number of tuple returned in from the
> lower
> > node so far
> > and percentage specification.
>
> Mmm. How do you think X can be equal to (X + 1)?
>
Oops my bad .The attached patch remove the need of doing that
>
> > > > /*
> > > + * Return the tuple up to the number of exact count in
> > > OFFSET
> > > + * clause without percentage value consideration.
> > > + */
> > > + if (node->perExactCount > 0)
> > > + {
> > > +
> > >
> > >
> > >
> > >
> > > + /*
> > > + * We may needed this tuple in backward scan so put it
> into
> > > + * tuplestore.
> > > + */
> > > + if (node->limitOption == PERCENTAGE)
> > > + {
> > > + tuplestore_puttupleslot(node->tupleStore, slot);
> > > + tuplestore_advance(node->tupleStore, true);
> > > + }
> > >
> > > "needed"->"need" ? The comment says that this is needed for
> > > backward scan, but it is actually required even in forward
> > > scan. More to the point, tuplestore_advance lacks comment.
> >
> >
> > ok
> >
> >
> > >
> > > Anyway, the code in LIMIT_RESCAN is broken in some ways. For
> > > example, if it is used as the inner scan of a NestLoop, the
> > > tuplestore accumulates tuples by every scan. You will see that
> > > the tuplestore stores up to 1000 tuples (10 times of the inner)
> > > by the following query.
> > >
> >
> > It this because in percentage we scan the whole table
>
> It's useless and rather harmful that the tuplestore holds
> indefinite number of duplicate set of the whole tuples from the
> lower node. We must reuse tuples already stored in the tuplestore
> or clear it before the next round.
>
>
i agree with this optimization but it don't have to be in first version
regards
Surafel
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 82d8140ba2..562c5acc45 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3035,7 +3035,8 @@ estimate_path_cost_size(PlannerInfo *root,
if (fpextra && fpextra->has_limit)
{
adjust_limit_rows_costs(&rows, &startup_cost, &total_cost,
- fpextra->offset_est, fpextra->count_est);
+ fpextra->offset_est, fpextra->count_est,
+ EXACT_NUMBER);
retrieved_rows = rows;
}
}
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 06d611b64c..4ef42df51d 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -44,7 +44,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
- [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
+ [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] [ PERCENT ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
<phrase>where <replaceable class="parameter">from_item</replaceable> can be one of:</phrase>
@@ -1430,7 +1430,7 @@ OFFSET <replaceable class="parameter">start</replaceable>
which <productname>PostgreSQL</productname> also supports. It is:
<synopsis>
OFFSET <replaceable class="parameter">start</replaceable> { ROW | ROWS }
-FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY
+FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] [ PERCENT ] { ROW | ROWS } ONLY
</synopsis>
In this syntax, the <replaceable class="parameter">start</replaceable>
or <replaceable class="parameter">count</replaceable> value is required by
@@ -1440,8 +1440,10 @@ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] {
ambiguity.
If <replaceable class="parameter">count</replaceable> is
omitted in a <literal>FETCH</literal> clause, it defaults to 1.
- <literal>ROW</literal>
- and <literal>ROWS</literal> as well as <literal>FIRST</literal>
+ With <literal>PERCENT</literal> count specifies the maximum number of rows to return
+ in percentage round up to the nearest integer. Using <literal>PERCENT</literal>
+ is best suited to returning single-digit percentages of the query's total row count.
+ <literal>ROW</literal> and <literal>ROWS</literal> as well as <literal>FIRST</literal>
and <literal>NEXT</literal> are noise words that don't influence
the effects of these clauses.
According to the standard, the <literal>OFFSET</literal> clause must come
diff --git a/src/backend/executor/nodeLimit.c b/src/backend/executor/nodeLimit.c
index baa669abe8..d23508cc99 100644
--- a/src/backend/executor/nodeLimit.c
+++ b/src/backend/executor/nodeLimit.c
@@ -21,6 +21,8 @@
#include "postgres.h"
+#include <math.h>
+
#include "executor/executor.h"
#include "executor/nodeLimit.h"
#include "miscadmin.h"
@@ -52,6 +54,7 @@ ExecLimit(PlanState *pstate)
*/
direction = node->ps.state->es_direction;
outerPlan = outerPlanState(node);
+ slot = node->subSlot;
/*
* The main logic is a simple state machine.
@@ -81,7 +84,15 @@ ExecLimit(PlanState *pstate)
/*
* Check for empty window; if so, treat like empty subplan.
*/
- if (node->count <= 0 && !node->noCount)
+ if (node->limitOption == PERCENTAGE)
+ {
+ if (node->percent == 0.0)
+ {
+ node->lstate = LIMIT_EMPTY;
+ return NULL;
+ }
+ }
+ else if (node->count <= 0 && !node->noCount)
{
node->lstate = LIMIT_EMPTY;
return NULL;
@@ -107,6 +118,16 @@ ExecLimit(PlanState *pstate)
break;
}
+ /*
+ * We may needed this tuple in subsequent scan so put it into
+ * tuplestore.
+ */
+ if (node->limitOption == PERCENTAGE)
+ {
+ tuplestore_puttupleslot(node->tupleStore, slot);
+ tuplestore_advance(node->tupleStore, true);
+ }
+
/*
* Okay, we have the first tuple of the window.
*/
@@ -124,6 +145,82 @@ ExecLimit(PlanState *pstate)
case LIMIT_INWINDOW:
if (ScanDirectionIsForward(direction))
{
+ /*
+ * In case of coming back from backward scan the tuple is
+ * already in tuple store.
+ */
+ if (node->limitOption == PERCENTAGE && node->backwardPosition > 0)
+ {
+ if (tuplestore_gettupleslot_heaptuple(node->tupleStore, true, true, slot))
+ {
+ node->subSlot = slot;
+ node->position++;
+ node->backwardPosition--;
+ return slot;
+ }
+ else
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
+ return NULL;
+ }
+ }
+
+ /*
+ * In PERCENTAGE case no need of executing outerPlan multiple
+ * times.
+ */
+ if (node->limitOption == PERCENTAGE && node->reachEnd)
+ {
+ node->lstate = LIMIT_WINDOWEND;
+
+ /*
+ * If we know we won't need to back up, we can release
+ * resources at this point.
+ */
+ if (!(node->ps.state->es_top_eflags & EXEC_FLAG_BACKWARD))
+ (void) ExecShutdownNode(outerPlan);
+
+ return NULL;
+ }
+
+ /*
+ * When in percentage mode, we need to see if we can get any
+ * additional rows from the subplan (enough to increase the
+ * node->count value).
+ */
+ if (node->limitOption == PERCENTAGE)
+ {
+ /* loop until the node->count became greater than the number of tuple returned so far */
+ do
+ {
+ int64 cnt;
+
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ {
+ node->reachEnd = true;
+ node->lstate = LIMIT_SUBPLANEOF;
+
+ /*
+ * The only operation from here is backward scan
+ * but there's no API to refetch the tuple at the
+ * current position. We have to move one tuple
+ * backward, and then we will scan forward for it
+ * for the first tuple and precede as usual
+ * for the rest
+ */
+ tuplestore_advance(node->tupleStore, false);
+ return NULL;
+ }
+
+ tuplestore_puttupleslot(node->tupleStore, slot);
+
+ cnt = tuplestore_tuple_count(node->tupleStore) + node->offset;
+
+ node->count = ceil(node->percent * cnt / 100.0);
+ }while (node->position - node->offset >= node->count);
+ }
+
/*
* Forwards scan, so check for stepping off end of window. If
* we are at the end of the window, return NULL without
@@ -145,17 +242,34 @@ ExecLimit(PlanState *pstate)
return NULL;
}
- /*
- * Get next tuple from subplan, if any.
- */
- slot = ExecProcNode(outerPlan);
- if (TupIsNull(slot))
+ if (node->limitOption == PERCENTAGE)
{
- node->lstate = LIMIT_SUBPLANEOF;
- return NULL;
+ if (tuplestore_gettupleslot_heaptuple(node->tupleStore, true, true, slot))
+ {
+ node->subSlot = slot;
+ node->position++;
+ }
+ else
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
+ return NULL;
+ }
+
+ }
+ else if (node->limitOption == EXACT_NUMBER)
+ {
+ /*
+ * Get next tuple from subplan, if any.
+ */
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
+ return NULL;
+ }
+ node->subSlot = slot;
+ node->position++;
}
- node->subSlot = slot;
- node->position++;
}
else
{
@@ -168,15 +282,29 @@ ExecLimit(PlanState *pstate)
node->lstate = LIMIT_WINDOWSTART;
return NULL;
}
-
- /*
- * Get previous tuple from subplan; there should be one!
- */
- slot = ExecProcNode(outerPlan);
- if (TupIsNull(slot))
- elog(ERROR, "LIMIT subplan failed to run backwards");
- node->subSlot = slot;
- node->position--;
+ /* In PERCENTAGE case the result is already in tuplestore */
+ if (node->limitOption == PERCENTAGE)
+ {
+ if (tuplestore_gettupleslot_heaptuple(node->tupleStore, false, true, slot))
+ {
+ node->subSlot = slot;
+ node->position--;
+ node->backwardPosition++;
+ }
+ else
+ elog(ERROR, "LIMIT subplan failed to run backwards");
+ }
+ else if (node->limitOption == EXACT_NUMBER)
+ {
+ /*
+ * Get previous tuple from subplan; there should be one!
+ */
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ elog(ERROR, "LIMIT subplan failed to run backwards");
+ node->subSlot = slot;
+ node->position--;
+ }
}
break;
@@ -185,15 +313,32 @@ ExecLimit(PlanState *pstate)
return NULL;
/*
- * Backing up from subplan EOF, so re-fetch previous tuple; there
- * should be one! Note previous tuple must be in window.
+ * Scan forward for the first tuple
*/
- slot = ExecProcNode(outerPlan);
- if (TupIsNull(slot))
- elog(ERROR, "LIMIT subplan failed to run backwards");
- node->subSlot = slot;
- node->lstate = LIMIT_INWINDOW;
- /* position does not change 'cause we didn't advance it before */
+ if (node->limitOption == PERCENTAGE)
+ {
+ if (tuplestore_gettupleslot_heaptuple(node->tupleStore, true, true, slot))
+ {
+ node->subSlot = slot;
+ node->lstate = LIMIT_INWINDOW;
+ }
+ else
+ elog(ERROR, "LIMIT subplan failed to run backwards");
+ }
+ else if (node->limitOption == EXACT_NUMBER)
+ {
+ /*
+ * Backing up from subplan EOF, so re-fetch previous tuple;
+ * there should be one! Note previous tuple must be in
+ * window.
+ */
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ elog(ERROR, "LIMIT subplan failed to run backwards");
+ node->subSlot = slot;
+ node->lstate = LIMIT_INWINDOW;
+ /* position does not change 'cause we didn't advance it before */
+ }
break;
case LIMIT_WINDOWEND:
@@ -283,12 +428,36 @@ recompute_limits(LimitState *node)
}
else
{
- node->count = DatumGetInt64(val);
- if (node->count < 0)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_ROW_COUNT_IN_LIMIT_CLAUSE),
- errmsg("LIMIT must not be negative")));
- node->noCount = false;
+ if (node->limitOption == PERCENTAGE)
+ {
+ /*
+ * We expect to return at least one row (unless there are no
+ * rows in the subplan), and we'll update this count later as
+ * we go.
+ */
+ node->count = 0;
+ node->percent = DatumGetFloat8(val);
+
+ if (node->percent < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ROW_COUNT_IN_RESULT_OFFSET_CLAUSE),
+ errmsg("PERCENT must not be negative")));
+
+ if (node->percent > 100)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ROW_COUNT_IN_RESULT_OFFSET_CLAUSE),
+ errmsg("PERCENT must not be greater than 100")));
+
+ }
+ else
+ {
+ node->count = DatumGetInt64(val);
+ if (node->count < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ROW_COUNT_IN_LIMIT_CLAUSE),
+ errmsg("LIMIT must not be negative")));
+
+ }
}
}
else
@@ -301,6 +470,8 @@ recompute_limits(LimitState *node)
/* Reset position to start-of-scan */
node->position = 0;
node->subSlot = NULL;
+ node->reachEnd = false;
+ node->backwardPosition = 0;
/* Set state-machine state */
node->lstate = LIMIT_RESCAN;
@@ -309,9 +480,11 @@ recompute_limits(LimitState *node)
* Notify child node about limit. Note: think not to "optimize" by
* skipping ExecSetTupleBound if compute_tuples_needed returns < 0. We
* must update the child node anyway, in case this is a rescan and the
- * previous time we got a different result.
+ * previous time we got a different result. In PERCENTAGE option there are
+ * no bound on the number of output tuples
*/
- ExecSetTupleBound(compute_tuples_needed(node), outerPlanState(node));
+ if (node->limitOption != PERCENTAGE)
+ ExecSetTupleBound(compute_tuples_needed(node), outerPlanState(node));
}
/*
@@ -374,6 +547,7 @@ ExecInitLimit(Limit *node, EState *estate, int eflags)
(PlanState *) limitstate);
limitstate->limitCount = ExecInitExpr((Expr *) node->limitCount,
(PlanState *) limitstate);
+ limitstate->limitOption = node->limitOption;
/*
* Initialize result type.
@@ -390,6 +564,9 @@ ExecInitLimit(Limit *node, EState *estate, int eflags)
*/
limitstate->ps.ps_ProjInfo = NULL;
+ if (node->limitOption == PERCENTAGE)
+ limitstate->tupleStore = tuplestore_begin_heap(true, false, work_mem);
+
return limitstate;
}
@@ -405,6 +582,8 @@ ExecEndLimit(LimitState *node)
{
ExecFreeExprContext(&node->ps);
ExecEndNode(outerPlanState(node));
+ if (node->tupleStore != NULL)
+ tuplestore_end(node->tupleStore);
}
@@ -424,4 +603,6 @@ ExecReScanLimit(LimitState *node)
*/
if (node->ps.lefttree->chgParam == NULL)
ExecReScan(node->ps.lefttree);
+ if (node->tupleStore != NULL)
+ tuplestore_rescan(node->tupleStore);
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index a2617c7cfd..d2c6ababb3 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1147,6 +1147,7 @@ _copyLimit(const Limit *from)
*/
COPY_NODE_FIELD(limitOffset);
COPY_NODE_FIELD(limitCount);
+ COPY_SCALAR_FIELD(limitOption);
return newnode;
}
@@ -3035,6 +3036,7 @@ _copyQuery(const Query *from)
COPY_NODE_FIELD(sortClause);
COPY_NODE_FIELD(limitOffset);
COPY_NODE_FIELD(limitCount);
+ COPY_SCALAR_FIELD(limitOption);
COPY_NODE_FIELD(rowMarks);
COPY_NODE_FIELD(setOperations);
COPY_NODE_FIELD(constraintDeps);
@@ -3119,6 +3121,7 @@ _copySelectStmt(const SelectStmt *from)
COPY_NODE_FIELD(sortClause);
COPY_NODE_FIELD(limitOffset);
COPY_NODE_FIELD(limitCount);
+ COPY_SCALAR_FIELD(limitOption);
COPY_NODE_FIELD(lockingClause);
COPY_NODE_FIELD(withClause);
COPY_SCALAR_FIELD(op);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 4f2ebe5118..2e14fa72b4 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -975,6 +975,7 @@ _equalQuery(const Query *a, const Query *b)
COMPARE_NODE_FIELD(sortClause);
COMPARE_NODE_FIELD(limitOffset);
COMPARE_NODE_FIELD(limitCount);
+ COMPARE_SCALAR_FIELD(limitOption);
COMPARE_NODE_FIELD(rowMarks);
COMPARE_NODE_FIELD(setOperations);
COMPARE_NODE_FIELD(constraintDeps);
@@ -1049,6 +1050,7 @@ _equalSelectStmt(const SelectStmt *a, const SelectStmt *b)
COMPARE_NODE_FIELD(sortClause);
COMPARE_NODE_FIELD(limitOffset);
COMPARE_NODE_FIELD(limitCount);
+ COMPARE_SCALAR_FIELD(limitOption);
COMPARE_NODE_FIELD(lockingClause);
COMPARE_NODE_FIELD(withClause);
COMPARE_SCALAR_FIELD(op);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index e6ce8e2110..959dc79a00 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -911,6 +911,7 @@ _outLimit(StringInfo str, const Limit *node)
WRITE_NODE_FIELD(limitOffset);
WRITE_NODE_FIELD(limitCount);
+ WRITE_ENUM_FIELD(limitOption, LimitOption);
}
static void
@@ -2108,6 +2109,7 @@ _outLimitPath(StringInfo str, const LimitPath *node)
WRITE_NODE_FIELD(subpath);
WRITE_NODE_FIELD(limitOffset);
WRITE_NODE_FIELD(limitCount);
+ WRITE_ENUM_FIELD(limitOption, LimitOption);
}
static void
@@ -2704,6 +2706,7 @@ _outSelectStmt(StringInfo str, const SelectStmt *node)
WRITE_NODE_FIELD(sortClause);
WRITE_NODE_FIELD(limitOffset);
WRITE_NODE_FIELD(limitCount);
+ WRITE_ENUM_FIELD(limitOption, LimitOption);
WRITE_NODE_FIELD(lockingClause);
WRITE_NODE_FIELD(withClause);
WRITE_ENUM_FIELD(op, SetOperation);
@@ -2914,6 +2917,7 @@ _outQuery(StringInfo str, const Query *node)
WRITE_NODE_FIELD(sortClause);
WRITE_NODE_FIELD(limitOffset);
WRITE_NODE_FIELD(limitCount);
+ WRITE_ENUM_FIELD(limitOption, LimitOption);
WRITE_NODE_FIELD(rowMarks);
WRITE_NODE_FIELD(setOperations);
WRITE_NODE_FIELD(constraintDeps);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 764e3bb90c..65a59d6650 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -278,6 +278,7 @@ _readQuery(void)
READ_NODE_FIELD(sortClause);
READ_NODE_FIELD(limitOffset);
READ_NODE_FIELD(limitCount);
+ READ_ENUM_FIELD(limitOption, LimitOption);
READ_NODE_FIELD(rowMarks);
READ_NODE_FIELD(setOperations);
READ_NODE_FIELD(constraintDeps);
@@ -2337,6 +2338,7 @@ _readLimit(void)
READ_NODE_FIELD(limitOffset);
READ_NODE_FIELD(limitCount);
+ READ_ENUM_FIELD(limitOption, LimitOption);
READ_DONE();
}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 0c036209f0..01c2590b08 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -2333,7 +2333,8 @@ create_minmaxagg_plan(PlannerInfo *root, MinMaxAggPath *best_path)
plan = (Plan *) make_limit(plan,
subparse->limitOffset,
- subparse->limitCount);
+ subparse->limitCount,
+ subparse->limitOption);
/* Must apply correct cost/width data to Limit node */
plan->startup_cost = mminfo->path->startup_cost;
@@ -2646,7 +2647,8 @@ create_limit_plan(PlannerInfo *root, LimitPath *best_path, int flags)
plan = make_limit(subplan,
best_path->limitOffset,
- best_path->limitCount);
+ best_path->limitCount,
+ best_path->limitOption);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6549,7 +6551,7 @@ make_lockrows(Plan *lefttree, List *rowMarks, int epqParam)
* Build a Limit plan node
*/
Limit *
-make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount)
+make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount, LimitOption limitOption)
{
Limit *node = makeNode(Limit);
Plan *plan = &node->plan;
@@ -6561,6 +6563,7 @@ make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount)
node->limitOffset = limitOffset;
node->limitCount = limitCount;
+ node->limitOption = limitOption;
return node;
}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 17c5f086fb..a7c5d152d5 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -2247,12 +2247,25 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
*/
if (parse->sortClause)
{
- current_rel = create_ordered_paths(root,
- current_rel,
- final_target,
- final_target_parallel_safe,
- have_postponed_srfs ? -1.0 :
- limit_tuples);
+
+ /*
+ * In PERCENTAGE option there are no bound on the number of output
+ * tuples
+ */
+ if (parse->limitOption == PERCENTAGE)
+ current_rel = create_ordered_paths(root,
+ current_rel,
+ final_target,
+ final_target_parallel_safe,
+ have_postponed_srfs ? -1.0 :
+ -1.0);
+ else
+ current_rel = create_ordered_paths(root,
+ current_rel,
+ final_target,
+ final_target_parallel_safe,
+ have_postponed_srfs ? -1.0 :
+ limit_tuples);
/* Fix things up if final_target contains SRFs */
if (parse->hasTargetSRFs)
adjust_paths_for_srfs(root, current_rel,
@@ -2315,6 +2328,7 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
path = (Path *) create_limit_path(root, final_rel, path,
parse->limitOffset,
parse->limitCount,
+ parse->limitOption,
offset_est, count_est);
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 34acb732ee..704d443ac0 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3546,6 +3546,7 @@ LimitPath *
create_limit_path(PlannerInfo *root, RelOptInfo *rel,
Path *subpath,
Node *limitOffset, Node *limitCount,
+ LimitOption limitOption,
int64 offset_est, int64 count_est)
{
LimitPath *pathnode = makeNode(LimitPath);
@@ -3567,6 +3568,7 @@ create_limit_path(PlannerInfo *root, RelOptInfo *rel,
pathnode->subpath = subpath;
pathnode->limitOffset = limitOffset;
pathnode->limitCount = limitCount;
+ pathnode->limitOption = limitOption;
/*
* Adjust the output rows count and costs according to the offset/limit.
@@ -3574,7 +3576,8 @@ create_limit_path(PlannerInfo *root, RelOptInfo *rel,
adjust_limit_rows_costs(&pathnode->path.rows,
&pathnode->path.startup_cost,
&pathnode->path.total_cost,
- offset_est, count_est);
+ offset_est, count_est,
+ limitOption);
return pathnode;
}
@@ -3600,7 +3603,8 @@ adjust_limit_rows_costs(double *rows, /* in/out parameter */
Cost *startup_cost, /* in/out parameter */
Cost *total_cost, /* in/out parameter */
int64 offset_est,
- int64 count_est)
+ int64 count_est,
+ LimitOption limitOption)
{
double input_rows = *rows;
Cost input_startup_cost = *startup_cost;
@@ -3633,6 +3637,19 @@ adjust_limit_rows_costs(double *rows, /* in/out parameter */
count_rows = (double) count_est;
else
count_rows = clamp_row_est(input_rows * 0.10);
+ if (limitOption == PERCENTAGE)
+ {
+ double per_count = DatumGetFloat8(count_est);
+
+ count_rows = clamp_row_est((input_rows * per_count) / 100);
+ if (rows > 0)
+ {
+ *startup_cost = count_rows *
+ input_total_cost / input_rows;
+ *total_cost = input_total_cost +
+ (count_rows * 0.1);
+ }
+ }
if (count_rows > *rows)
count_rows = *rows;
if (input_rows > 0)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 85d7a96406..79b389009c 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1288,10 +1288,11 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
}
/* transform LIMIT */
- qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset,
+ qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset, stmt->limitOption,
EXPR_KIND_OFFSET, "OFFSET");
- qry->limitCount = transformLimitClause(pstate, stmt->limitCount,
+ qry->limitCount = transformLimitClause(pstate, stmt->limitCount, stmt->limitOption,
EXPR_KIND_LIMIT, "LIMIT");
+ qry->limitOption = stmt->limitOption;
/* transform window clauses after we have seen all window functions */
qry->windowClause = transformWindowDefinitions(pstate,
@@ -1536,10 +1537,11 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
EXPR_KIND_ORDER_BY,
false /* allow SQL92 rules */ );
- qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset,
+ qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset, stmt->limitOption,
EXPR_KIND_OFFSET, "OFFSET");
- qry->limitCount = transformLimitClause(pstate, stmt->limitCount,
+ qry->limitCount = transformLimitClause(pstate, stmt->limitCount, stmt->limitOption,
EXPR_KIND_LIMIT, "LIMIT");
+ qry->limitOption = stmt->limitOption;
if (stmt->lockingClause)
ereport(ERROR,
@@ -1770,10 +1772,11 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
parser_errposition(pstate,
exprLocation(list_nth(qry->targetList, tllen)))));
- qry->limitOffset = transformLimitClause(pstate, limitOffset,
+ qry->limitOffset = transformLimitClause(pstate, limitOffset, stmt->limitOption,
EXPR_KIND_OFFSET, "OFFSET");
- qry->limitCount = transformLimitClause(pstate, limitCount,
+ qry->limitCount = transformLimitClause(pstate, limitCount, stmt->limitOption,
EXPR_KIND_LIMIT, "LIMIT");
+ qry->limitOption = stmt->limitOption;
qry->rtable = pstate->p_rtable;
qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c97bb367f8..4c6d3ded5d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -127,6 +127,21 @@ typedef struct ImportQual
List *table_names;
} ImportQual;
+/* Private struct for the result of opt_select_limit production */
+typedef struct SelectLimit
+{
+ Node *limitOffset;
+ Node *limitCount;
+ void *limitOption;
+} SelectLimit;
+
+/* Private struct for the result of limit_clause production */
+typedef struct LimitClause
+{
+ Node *limitCount;
+ void *limitOption;
+} LimitClause;
+
/* ConstraintAttributeSpec yields an integer bitmask of these flags: */
#define CAS_NOT_DEFERRABLE 0x01
#define CAS_DEFERRABLE 0x02
@@ -165,6 +180,7 @@ static List *makeOrderedSetArgs(List *directargs, List *orderedargs,
static void insertSelectOptions(SelectStmt *stmt,
List *sortClause, List *lockingClause,
Node *limitOffset, Node *limitCount,
+ void *limitOption,
WithClause *withClause,
core_yyscan_t yyscanner);
static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg);
@@ -242,6 +258,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
PartitionSpec *partspec;
PartitionBoundSpec *partboundspec;
RoleSpec *rolespec;
+ struct SelectLimit *SelectLimit;
+ struct LimitClause *LimitClause;
}
%type <node> stmt schema_stmt
@@ -373,6 +391,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> import_qualification_type
%type <importqual> import_qualification
%type <node> vacuum_relation
+%type <SelectLimit> opt_select_limit select_limit
+%type <LimitClause> limit_clause
%type <list> stmtblock stmtmulti
OptTableElementList TableElementList OptInherit definition
@@ -393,8 +413,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
target_list opt_target_list insert_column_list set_target_list
set_clause_list set_clause
def_list operator_def_list indirection opt_indirection
- reloption_list group_clause TriggerFuncArgs select_limit
- opt_select_limit opclass_item_list opclass_drop_list
+ reloption_list group_clause TriggerFuncArgs
+ opclass_item_list opclass_drop_list
opclass_purpose opt_opfamily transaction_mode_list_or_empty
OptTableFuncElementList TableFuncElementList opt_type_modifiers
prep_type_clause
@@ -455,7 +475,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
comment_type_any_name comment_type_name
security_label_type_any_name security_label_type_name
-%type <node> fetch_args limit_clause select_limit_value
+%type <node> fetch_args select_limit_value
offset_clause select_offset_value
select_fetch_first_value I_or_F_const
%type <ival> row_or_rows first_or_next
@@ -667,7 +687,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
ORDER ORDINALITY OTHERS OUT_P OUTER_P
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
- PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY
+ PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PERCENT PLACING PLANS POLICY
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
@@ -11225,14 +11245,15 @@ select_no_parens:
| select_clause sort_clause
{
insertSelectOptions((SelectStmt *) $1, $2, NIL,
- NULL, NULL, NULL,
+ NULL, NULL, NULL, NULL,
yyscanner);
$$ = $1;
}
| select_clause opt_sort_clause for_locking_clause opt_select_limit
{
insertSelectOptions((SelectStmt *) $1, $2, $3,
- list_nth($4, 0), list_nth($4, 1),
+ ($4)->limitOffset, ($4)->limitCount,
+ ($4)->limitOption,
NULL,
yyscanner);
$$ = $1;
@@ -11240,7 +11261,8 @@ select_no_parens:
| select_clause opt_sort_clause select_limit opt_for_locking_clause
{
insertSelectOptions((SelectStmt *) $1, $2, $4,
- list_nth($3, 0), list_nth($3, 1),
+ ($3)->limitOffset, ($3)->limitCount,
+ ($3)->limitOption,
NULL,
yyscanner);
$$ = $1;
@@ -11249,7 +11271,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, NULL, NIL,
NULL, NULL,
- $1,
+ NULL, $1,
yyscanner);
$$ = $2;
}
@@ -11257,14 +11279,15 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, $3, NIL,
NULL, NULL,
- $1,
+ NULL, $1,
yyscanner);
$$ = $2;
}
| with_clause select_clause opt_sort_clause for_locking_clause opt_select_limit
{
insertSelectOptions((SelectStmt *) $2, $3, $4,
- list_nth($5, 0), list_nth($5, 1),
+ ($5)->limitOffset, ($5)->limitCount,
+ ($5)->limitOption,
$1,
yyscanner);
$$ = $2;
@@ -11272,7 +11295,8 @@ select_no_parens:
| with_clause select_clause opt_sort_clause select_limit opt_for_locking_clause
{
insertSelectOptions((SelectStmt *) $2, $3, $5,
- list_nth($4, 0), list_nth($4, 1),
+ ($4)->limitOffset, ($4)->limitCount,
+ ($4)->limitOption,
$1,
yyscanner);
$$ = $2;
@@ -11566,20 +11590,60 @@ sortby: a_expr USING qual_all_Op opt_nulls_order
select_limit:
- limit_clause offset_clause { $$ = list_make2($2, $1); }
- | offset_clause limit_clause { $$ = list_make2($1, $2); }
- | limit_clause { $$ = list_make2(NULL, $1); }
- | offset_clause { $$ = list_make2($1, NULL); }
+ limit_clause offset_clause
+ {
+ SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
+ n->limitOffset = $2;
+ n->limitCount = ($1)->limitCount;
+ n->limitOption = ($1)->limitOption;
+ $$ = n;
+ }
+ | offset_clause limit_clause
+ {
+ SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
+ n->limitOffset = $1;
+ n->limitCount = ($2)->limitCount;
+ n->limitOption = ($2)->limitOption;
+ $$ = n;
+ }
+ | limit_clause
+ {
+ SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
+ n->limitOffset = NULL;
+ n->limitCount = ($1)->limitCount;
+ n->limitOption = ($1)->limitOption;
+ $$ = n;
+ }
+ | offset_clause
+ {
+ SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
+ n->limitOffset = $1;
+ n->limitCount = NULL;
+ n->limitOption = NULL;
+ $$ = n;
+ }
;
opt_select_limit:
select_limit { $$ = $1; }
- | /* EMPTY */ { $$ = list_make2(NULL,NULL); }
+ | /* EMPTY */
+ {
+ SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
+ n->limitOffset = NULL;
+ n->limitCount = NULL;
+ n->limitOption = NULL;
+ $$ = n;
+ }
;
limit_clause:
LIMIT select_limit_value
- { $$ = $2; }
+ {
+ LimitClause *n = (LimitClause *) palloc(sizeof(LimitClause));
+ n->limitCount = $2;
+ n->limitOption = NULL;
+ $$ = n;
+ }
| LIMIT select_limit_value ',' select_offset_value
{
/* Disabled because it was too confusing, bjm 2002-02-18 */
@@ -11597,9 +11661,26 @@ limit_clause:
* we can see the ONLY token in the lookahead slot.
*/
| FETCH first_or_next select_fetch_first_value row_or_rows ONLY
- { $$ = $3; }
+ {
+ LimitClause *n = (LimitClause *) palloc(sizeof(LimitClause));
+ n->limitCount = $3;
+ n->limitOption = makeString("EXACT_NUMBER");
+ $$ = n;
+ }
+ | FETCH first_or_next select_fetch_first_value PERCENT row_or_rows ONLY
+ {
+ LimitClause *n = (LimitClause *) palloc(sizeof(LimitClause));
+ n->limitCount = $3;
+ n->limitOption = makeString("PERCENTAGE");
+ $$ = n;
+ }
| FETCH first_or_next row_or_rows ONLY
- { $$ = makeIntConst(1, -1); }
+ {
+ LimitClause *n = (LimitClause *) palloc(sizeof(LimitClause));
+ n->limitCount = makeIntConst(1, -1);
+ n->limitOption = NULL;
+ $$ = n;
+ }
;
offset_clause:
@@ -15192,6 +15273,7 @@ unreserved_keyword:
| PARTITION
| PASSING
| PASSWORD
+ | PERCENT
| PLANS
| POLICY
| PRECEDING
@@ -15856,6 +15938,7 @@ static void
insertSelectOptions(SelectStmt *stmt,
List *sortClause, List *lockingClause,
Node *limitOffset, Node *limitCount,
+ void *limitOption,
WithClause *withClause,
core_yyscan_t yyscanner)
{
@@ -15894,6 +15977,17 @@ insertSelectOptions(SelectStmt *stmt,
parser_errposition(exprLocation(limitCount))));
stmt->limitCount = limitCount;
}
+ if (limitOption)
+ {
+ if (stmt->limitOption)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("multiple LIMIT options not allowed")));
+ if (strcmp(strVal(limitOption), "PERCENTAGE") == 0)
+ stmt->limitOption = PERCENTAGE;
+ else
+ stmt->limitOption = EXACT_NUMBER;
+ }
if (withClause)
{
if (stmt->withClause)
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 260ccd4d7f..0fa1d5e01e 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1704,7 +1704,7 @@ transformWhereClause(ParseState *pstate, Node *clause,
* constructName does not affect the semantics, but is used in error messages
*/
Node *
-transformLimitClause(ParseState *pstate, Node *clause,
+transformLimitClause(ParseState *pstate, Node *clause, LimitOption limitOption,
ParseExprKind exprKind, const char *constructName)
{
Node *qual;
@@ -1713,8 +1713,10 @@ transformLimitClause(ParseState *pstate, Node *clause,
return NULL;
qual = transformExpr(pstate, clause, exprKind);
-
- qual = coerce_to_specific_type(pstate, qual, INT8OID, constructName);
+ if (limitOption == PERCENTAGE && (strcmp(constructName, "LIMIT") == 0))
+ qual = coerce_to_specific_type(pstate, qual, FLOAT8OID, constructName);
+ else
+ qual = coerce_to_specific_type(pstate, qual, INT8OID, constructName);
/* LIMIT can't refer to any variables of the current query */
checkExprIsVarFree(pstate, qual, constructName);
diff --git a/src/backend/utils/sort/tuplestore.c b/src/backend/utils/sort/tuplestore.c
index 3fc7f92182..a03e568f1f 100644
--- a/src/backend/utils/sort/tuplestore.c
+++ b/src/backend/utils/sort/tuplestore.c
@@ -1100,6 +1100,39 @@ tuplestore_gettupleslot(Tuplestorestate *state, bool forward,
}
}
+/*
+ * tuplestore_gettupleslot_heaptuple
+ * It is similar to tuplestore_gettupleslot except it return stored HeapTuple
+ * instead of MinimalTuple
+ */
+bool
+tuplestore_gettupleslot_heaptuple(Tuplestorestate *state, bool forward,
+ bool copy, TupleTableSlot *slot)
+{
+ MinimalTuple tuple;
+ HeapTuple htuple;
+ bool should_free;
+
+ tuple = (MinimalTuple) tuplestore_gettuple(state, forward, &should_free);
+
+ if (tuple)
+ {
+ if (copy && !should_free)
+ {
+ tuple = heap_copy_minimal_tuple(tuple);
+ should_free = true;
+ }
+ htuple = heap_tuple_from_minimal_tuple(tuple);
+ ExecForceStoreHeapTuple(htuple, slot, should_free);
+ return true;
+ }
+ else
+ {
+ ExecClearTuple(slot);
+ return false;
+ }
+}
+
/*
* tuplestore_advance - exported function to adjust position without fetching
*
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index f42189d2bf..b41151cd50 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2299,8 +2299,14 @@ typedef struct LimitState
PlanState ps; /* its first field is NodeTag */
ExprState *limitOffset; /* OFFSET parameter, or NULL if none */
ExprState *limitCount; /* COUNT parameter, or NULL if none */
+ LimitOption limitOption; /* limit specification type */
int64 offset; /* current OFFSET value */
int64 count; /* current COUNT, if any */
+ float8 percent; /* percentage */
+ int64 backwardPosition; /* the number of tuple returned in
+ * backward scan */
+ bool reachEnd; /* if true, outerPlan executed until the end */
+ Tuplestorestate *tupleStore; /* holds the returned tuple */
bool noCount; /* if true, ignore count */
LimitStateCond lstate; /* state machine status, as above */
int64 position; /* 1-based index of last tuple returned */
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 3cbb08df92..c85b9e2ab8 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -821,4 +821,16 @@ typedef enum OnConflictAction
ONCONFLICT_UPDATE /* ON CONFLICT ... DO UPDATE */
} OnConflictAction;
+/*
+ * LimitOption -
+ * LIMIT option of query
+ *
+ * This is needed in both parsenodes.h and plannodes.h, so put it here...
+ */
+typedef enum LimitOption
+{
+ EXACT_NUMBER, /* LIMIT in exact number of rows */
+ PERCENTAGE /* LIMIT in percentage */
+}LimitOption;
+
#endif /* NODES_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 94ded3c135..b158e9eaeb 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -159,6 +159,7 @@ typedef struct Query
Node *limitOffset; /* # of result tuples to skip (int8 expr) */
Node *limitCount; /* # of result tuples to return (int8 expr) */
+ LimitOption limitOption; /* limit type */
List *rowMarks; /* a list of RowMarkClause's */
@@ -1595,6 +1596,7 @@ typedef struct SelectStmt
List *sortClause; /* sort clause (a list of SortBy's) */
Node *limitOffset; /* # of result tuples to skip */
Node *limitCount; /* # of result tuples to return */
+ LimitOption limitOption; /* limit type */
List *lockingClause; /* FOR UPDATE (list of LockingClause's) */
WithClause *withClause; /* WITH clause */
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 23a06d718e..b77697f45a 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1793,6 +1793,7 @@ typedef struct LimitPath
Path *subpath; /* path representing input source */
Node *limitOffset; /* OFFSET parameter, or NULL if none */
Node *limitCount; /* COUNT parameter, or NULL if none */
+ LimitOption limitOption; /* LIMIT in percentage or exact number */
} LimitPath;
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 8e6594e355..c8c497295b 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -967,6 +967,7 @@ typedef struct Limit
Plan plan;
Node *limitOffset; /* OFFSET parameter, or NULL if none */
Node *limitCount; /* COUNT parameter, or NULL if none */
+ LimitOption limitOption; /* LIMIT in percentage or exact number */
} Limit;
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index a12af54971..117ed8d761 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -262,10 +262,11 @@ extern ModifyTablePath *create_modifytable_path(PlannerInfo *root,
extern LimitPath *create_limit_path(PlannerInfo *root, RelOptInfo *rel,
Path *subpath,
Node *limitOffset, Node *limitCount,
+ LimitOption limitOption,
int64 offset_est, int64 count_est);
extern void adjust_limit_rows_costs(double *rows,
Cost *startup_cost, Cost *total_cost,
- int64 offset_est, int64 count_est);
+ int64 offset_est, int64 count_est, LimitOption limitOption);
extern Path *reparameterize_path(PlannerInfo *root, Path *path,
Relids required_outer,
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index e7aaddd50d..3395e558c4 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -56,7 +56,7 @@ extern Agg *make_agg(List *tlist, List *qual,
int numGroupCols, AttrNumber *grpColIdx, Oid *grpOperators, Oid *grpCollations,
List *groupingSets, List *chain,
double dNumGroups, Plan *lefttree);
-extern Limit *make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount);
+extern Limit *make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount, LimitOption limitOption);
/*
* prototypes for plan/initsplan.c
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 00ace8425e..e1a8d703ab 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -299,6 +299,7 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD)
PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD)
PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD)
PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD)
+PG_KEYWORD("percent", PERCENT, UNRESERVED_KEYWORD)
PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD)
PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD)
PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD)
diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h
index 42adc63d1f..e66510e965 100644
--- a/src/include/parser/parse_clause.h
+++ b/src/include/parser/parse_clause.h
@@ -22,7 +22,7 @@ extern int setTargetTable(ParseState *pstate, RangeVar *relation,
extern Node *transformWhereClause(ParseState *pstate, Node *clause,
ParseExprKind exprKind, const char *constructName);
-extern Node *transformLimitClause(ParseState *pstate, Node *clause,
+extern Node *transformLimitClause(ParseState *pstate, Node *clause, LimitOption limitOption,
ParseExprKind exprKind, const char *constructName);
extern List *transformGroupClause(ParseState *pstate, List *grouplist,
List **groupingSets,
diff --git a/src/include/utils/tuplestore.h b/src/include/utils/tuplestore.h
index f9b6fcec29..eeefde3da4 100644
--- a/src/include/utils/tuplestore.h
+++ b/src/include/utils/tuplestore.h
@@ -47,7 +47,6 @@ typedef struct Tuplestorestate Tuplestorestate;
extern Tuplestorestate *tuplestore_begin_heap(bool randomAccess,
bool interXact,
int maxKBytes);
-
extern void tuplestore_set_eflags(Tuplestorestate *state, int eflags);
extern void tuplestore_puttupleslot(Tuplestorestate *state,
@@ -72,9 +71,12 @@ extern bool tuplestore_in_memory(Tuplestorestate *state);
extern bool tuplestore_gettupleslot(Tuplestorestate *state, bool forward,
bool copy, TupleTableSlot *slot);
+extern bool tuplestore_gettupleslot_heaptuple(Tuplestorestate *state, bool forward,
+ bool copy, TupleTableSlot *slot);
extern bool tuplestore_advance(Tuplestorestate *state, bool forward);
+
extern bool tuplestore_skiptuples(Tuplestorestate *state,
int64 ntuples, bool forward);
diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out
index c18f547cbd..95f9f5f3d9 100644
--- a/src/test/regress/expected/limit.out
+++ b/src/test/regress/expected/limit.out
@@ -108,6 +108,63 @@ SELECT ''::text AS five, unique1, unique2, stringu1
| 904 | 793 | UIAAAA
(5 rows)
+--
+-- PERCENT
+-- Check the PERCENT option of limit clause
+--
+SELECT ''::text AS two, unique1, unique2, stringu1
+ FROM onek WHERE unique1 > 50
+ ORDER BY unique1 FETCH FIRST 1 PERCENT ROWS ONLY;
+ two | unique1 | unique2 | stringu1
+-----+---------+---------+----------
+ | 51 | 76 | ZBAAAA
+ | 52 | 985 | ACAAAA
+ | 53 | 196 | BCAAAA
+ | 54 | 356 | CCAAAA
+ | 55 | 627 | DCAAAA
+ | 56 | 54 | ECAAAA
+ | 57 | 942 | FCAAAA
+ | 58 | 114 | GCAAAA
+ | 59 | 593 | HCAAAA
+ | 60 | 483 | ICAAAA
+(10 rows)
+
+SELECT ''::text AS two, unique1, unique2, stringu1
+ FROM onek WHERE unique1 > 60 AND unique1 < 63
+ ORDER BY unique1 FETCH FIRST 50 PERCENT ROWS ONLY;
+ two | unique1 | unique2 | stringu1
+-----+---------+---------+----------
+ | 61 | 560 | JCAAAA
+(1 row)
+
+SELECT ''::text AS three, unique1, unique2, stringu1
+ FROM onek WHERE unique1 > 100
+ ORDER BY unique1 FETCH FIRST 1 PERCENT ROWS ONLY OFFSET 20;
+ three | unique1 | unique2 | stringu1
+-------+---------+---------+----------
+ | 121 | 700 | REAAAA
+ | 122 | 519 | SEAAAA
+ | 123 | 777 | TEAAAA
+ | 124 | 503 | UEAAAA
+ | 125 | 849 | VEAAAA
+ | 126 | 330 | WEAAAA
+ | 127 | 511 | XEAAAA
+ | 128 | 721 | YEAAAA
+ | 129 | 696 | ZEAAAA
+(9 rows)
+
+SELECT ''::text AS eleven, unique1, unique2, stringu1
+ FROM onek WHERE unique1 < 50
+ ORDER BY unique1 DESC FETCH FIRST 10 PERCENT ROWS ONLY OFFSET 39;
+ eleven | unique1 | unique2 | stringu1
+--------+---------+---------+----------
+ | 10 | 520 | KAAAAA
+ | 9 | 49 | JAAAAA
+ | 8 | 653 | IAAAAA
+ | 7 | 647 | HAAAAA
+ | 6 | 978 | GAAAAA
+(5 rows)
+
-- Test null limit and offset. The planner would discard a simple null
-- constant, so to ensure executor is exercised, do this:
select * from int8_tbl limit (case when random() < 0.5 then null::bigint end);
@@ -286,6 +343,46 @@ fetch all in c4;
----+----
(0 rows)
+declare c5 cursor for select * from int8_tbl fetch first 50 percent rows only;
+fetch all in c5;
+ q1 | q2
+------------------+------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+(3 rows)
+
+fetch 1 in c5;
+ q1 | q2
+----+----
+(0 rows)
+
+fetch backward 1 in c5;
+ q1 | q2
+------------------+-----
+ 4567890123456789 | 123
+(1 row)
+
+fetch backward all in c5;
+ q1 | q2
+-----+------------------
+ 123 | 4567890123456789
+ 123 | 456
+(2 rows)
+
+fetch backward 1 in c5;
+ q1 | q2
+----+----
+(0 rows)
+
+fetch all in c5;
+ q1 | q2
+------------------+------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+(3 rows)
+
rollback;
-- Stress test for variable LIMIT in conjunction with bounded-heap sorting
SELECT
@@ -503,3 +600,19 @@ select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
45020 | 45020
(3 rows)
+select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
+ from tenk1 group by thousand order by thousand FETCH FIRST 1 PERCENT ROWS ONLY;
+ s1 | s2
+-------+-------
+ 45000 | 45000
+ 45010 | 45010
+ 45020 | 45020
+ 45030 | 45030
+ 45040 | 45040
+ 45050 | 45050
+ 45060 | 45060
+ 45070 | 45070
+ 45080 | 45080
+ 45090 | 45090
+(10 rows)
+
diff --git a/src/test/regress/sql/limit.sql b/src/test/regress/sql/limit.sql
index 2a313d80ca..051b21a099 100644
--- a/src/test/regress/sql/limit.sql
+++ b/src/test/regress/sql/limit.sql
@@ -31,6 +31,23 @@ SELECT ''::text AS five, unique1, unique2, stringu1
FROM onek
ORDER BY unique1 LIMIT 5 OFFSET 900;
+--
+-- PERCENT
+-- Check the PERCENT option of limit clause
+--
+SELECT ''::text AS two, unique1, unique2, stringu1
+ FROM onek WHERE unique1 > 50
+ ORDER BY unique1 FETCH FIRST 1 PERCENT ROWS ONLY;
+SELECT ''::text AS two, unique1, unique2, stringu1
+ FROM onek WHERE unique1 > 60 AND unique1 < 63
+ ORDER BY unique1 FETCH FIRST 50 PERCENT ROWS ONLY;
+SELECT ''::text AS three, unique1, unique2, stringu1
+ FROM onek WHERE unique1 > 100
+ ORDER BY unique1 FETCH FIRST 1 PERCENT ROWS ONLY OFFSET 20;
+SELECT ''::text AS eleven, unique1, unique2, stringu1
+ FROM onek WHERE unique1 < 50
+ ORDER BY unique1 DESC FETCH FIRST 10 PERCENT ROWS ONLY OFFSET 39;
+
-- Test null limit and offset. The planner would discard a simple null
-- constant, so to ensure executor is exercised, do this:
select * from int8_tbl limit (case when random() < 0.5 then null::bigint end);
@@ -38,7 +55,6 @@ select * from int8_tbl offset (case when random() < 0.5 then null::bigint end);
-- Test assorted cases involving backwards fetch from a LIMIT plan node
begin;
-
declare c1 cursor for select * from int8_tbl limit 10;
fetch all in c1;
fetch 1 in c1;
@@ -71,6 +87,14 @@ fetch backward all in c4;
fetch backward 1 in c4;
fetch all in c4;
+declare c5 cursor for select * from int8_tbl fetch first 50 percent rows only;
+fetch all in c5;
+fetch 1 in c5;
+fetch backward 1 in c5;
+fetch backward all in c5;
+fetch backward 1 in c5;
+fetch all in c5;
+
rollback;
-- Stress test for variable LIMIT in conjunction with bounded-heap sorting
@@ -141,3 +165,6 @@ select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
from tenk1 group by thousand order by thousand limit 3;
+
+select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
+ from tenk1 group by thousand order by thousand FETCH FIRST 1 PERCENT ROWS ONLY;
