Skip to content

Commit 1a1832e

Browse files
tglsfdctglsfdc
committed
Avoid inserting no-op Limit plan nodes.
This was discussed in connection with the patch to avoid inserting no-op Result nodes, but not actually implemented therein.
1 parent fb60e72 commit 1a1832e

File tree

2 files changed

+84
-33
lines changed

2 files changed

+84
-33
lines changed

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

Lines changed: 56 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -68,6 +68,7 @@ static void preprocess_rowmarks(PlannerInfo *root);
6868
static double preprocess_limit(PlannerInfo *root,
6969
double tuple_fraction,
7070
int64 *offset_est, int64 *count_est);
71+
static bool limit_needed(Query *parse);
7172
static void preprocess_groupclause(PlannerInfo *root);
7273
static bool choose_hashed_grouping(PlannerInfo *root,
7374
double tuple_fraction, double limit_tuples,
@@ -1825,7 +1826,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
18251826
/*
18261827
* Finally, if there is a LIMIT/OFFSET clause, add the LIMIT node.
18271828
*/
1828-
if (parse->limitCount || parse->limitOffset)
1829+
if (limit_needed(parse))
18291830
{
18301831
result_plan = (Plan *) make_limit(result_plan,
18311832
parse->limitOffset,
@@ -2296,6 +2297,60 @@ preprocess_limit(PlannerInfo *root, double tuple_fraction,
22962297
return tuple_fraction;
22972298
}
22982299

2300+
/*
2301+
* limit_needed - do we actually need a Limit plan node?
2302+
*
2303+
* If we have constant-zero OFFSET and constant-null LIMIT, we can skip adding
2304+
* a Limit node. This is worth checking for because "OFFSET 0" is a common
2305+
* locution for an optimization fence. (Because other places in the planner
2306+
* merely check whether parse->limitOffset isn't NULL, it will still work as
2307+
* an optimization fence --- we're just suppressing unnecessary run-time
2308+
* overhead.)
2309+
*
2310+
* This might look like it could be merged into preprocess_limit, but there's
2311+
* a key distinction: here we need hard constants in OFFSET/LIMIT, whereas
2312+
* in preprocess_limit it's good enough to consider estimated values.
2313+
*/
2314+
static bool
2315+
limit_needed(Query *parse)
2316+
{
2317+
Node *node;
2318+
2319+
node = parse->limitCount;
2320+
if (node)
2321+
{
2322+
if (IsA(node, Const))
2323+
{
2324+
/* NULL indicates LIMIT ALL, ie, no limit */
2325+
if (!((Const *) node)->constisnull)
2326+
return true; /* LIMIT with a constant value */
2327+
}
2328+
else
2329+
return true; /* non-constant LIMIT */
2330+
}
2331+
2332+
node = parse->limitOffset;
2333+
if (node)
2334+
{
2335+
if (IsA(node, Const))
2336+
{
2337+
/* Treat NULL as no offset; the executor would too */
2338+
if (!((Const *) node)->constisnull)
2339+
{
2340+
int64 offset = DatumGetInt64(((Const *) node)->constvalue);
2341+
2342+
/* Executor would treat less-than-zero same as zero */
2343+
if (offset > 0)
2344+
return true; /* OFFSET with a positive value */
2345+
}
2346+
}
2347+
else
2348+
return true; /* non-constant OFFSET */
2349+
}
2350+
2351+
return false; /* don't need a Limit plan node */
2352+
}
2353+
22992354

23002355
/*
23012356
* preprocess_groupclause - do preparatory work on GROUP BY clause

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

Lines changed: 28 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -542,36 +542,34 @@ SELECT * FROM rw_view2;
542542
(2 rows)
543543

544544
EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
545-
QUERY PLAN
546-
------------------------------------------------------------------
545+
QUERY PLAN
546+
----------------------------------------------------------------
547547
Update on base_tbl
548548
-> Nested Loop
549549
-> Index Scan using base_tbl_pkey on base_tbl
550550
Index Cond: (a = 2)
551551
-> Subquery Scan on rw_view1
552552
Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
553-
-> Limit
554-
-> Bitmap Heap Scan on base_tbl base_tbl_1
555-
Recheck Cond: (a > 0)
556-
-> Bitmap Index Scan on base_tbl_pkey
557-
Index Cond: (a > 0)
558-
(11 rows)
553+
-> Bitmap Heap Scan on base_tbl base_tbl_1
554+
Recheck Cond: (a > 0)
555+
-> Bitmap Index Scan on base_tbl_pkey
556+
Index Cond: (a > 0)
557+
(10 rows)
559558

560559
EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
561-
QUERY PLAN
562-
------------------------------------------------------------------
560+
QUERY PLAN
561+
----------------------------------------------------------------
563562
Delete on base_tbl
564563
-> Nested Loop
565564
-> Index Scan using base_tbl_pkey on base_tbl
566565
Index Cond: (a = 2)
567566
-> Subquery Scan on rw_view1
568567
Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
569-
-> Limit
570-
-> Bitmap Heap Scan on base_tbl base_tbl_1
571-
Recheck Cond: (a > 0)
572-
-> Bitmap Index Scan on base_tbl_pkey
573-
Index Cond: (a > 0)
574-
(11 rows)
568+
-> Bitmap Heap Scan on base_tbl base_tbl_1
569+
Recheck Cond: (a > 0)
570+
-> Bitmap Index Scan on base_tbl_pkey
571+
Index Cond: (a > 0)
572+
(10 rows)
575573

576574
DROP TABLE base_tbl CASCADE;
577575
NOTICE: drop cascades to 2 other objects
@@ -775,30 +773,28 @@ SELECT * FROM rw_view2;
775773
(2 rows)
776774

777775
EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
778-
QUERY PLAN
779-
------------------------------------------------------------
776+
QUERY PLAN
777+
----------------------------------------------------------
780778
Update on rw_view1 rw_view1_1
781779
-> Subquery Scan on rw_view1
782780
Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
783-
-> Limit
784-
-> Bitmap Heap Scan on base_tbl
785-
Recheck Cond: (a > 0)
786-
-> Bitmap Index Scan on base_tbl_pkey
787-
Index Cond: (a > 0)
788-
(8 rows)
781+
-> Bitmap Heap Scan on base_tbl
782+
Recheck Cond: (a > 0)
783+
-> Bitmap Index Scan on base_tbl_pkey
784+
Index Cond: (a > 0)
785+
(7 rows)
789786

790787
EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
791-
QUERY PLAN
792-
------------------------------------------------------------
788+
QUERY PLAN
789+
----------------------------------------------------------
793790
Delete on rw_view1 rw_view1_1
794791
-> Subquery Scan on rw_view1
795792
Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
796-
-> Limit
797-
-> Bitmap Heap Scan on base_tbl
798-
Recheck Cond: (a > 0)
799-
-> Bitmap Index Scan on base_tbl_pkey
800-
Index Cond: (a > 0)
801-
(8 rows)
793+
-> Bitmap Heap Scan on base_tbl
794+
Recheck Cond: (a > 0)
795+
-> Bitmap Index Scan on base_tbl_pkey
796+
Index Cond: (a > 0)
797+
(7 rows)
802798

803799
DROP TABLE base_tbl CASCADE;
804800
NOTICE: drop cascades to 2 other objects

0 commit comments

Comments
 (0)