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.

diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 670776b032b40c75d38e63c4795c6ee343024097..db91b8277d9966d3597996a1f574233e6b062241 100644 (file)
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -68,6 +68,7 @@ static void preprocess_rowmarks(PlannerInfo *root);
 static double preprocess_limit(PlannerInfo *root,
                 double tuple_fraction,
                 int64 *offset_est, int64 *count_est);
+static bool limit_needed(Query *parse);
 static void preprocess_groupclause(PlannerInfo *root);
 static bool choose_hashed_grouping(PlannerInfo *root,
                       double tuple_fraction, double limit_tuples,
@@ -1825,7 +1826,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
    /*
     * Finally, if there is a LIMIT/OFFSET clause, add the LIMIT node.
     */
-   if (parse->limitCount || parse->limitOffset)
+   if (limit_needed(parse))
    {
        result_plan = (Plan *) make_limit(result_plan,
                                          parse->limitOffset,
@@ -2296,6 +2297,60 @@ preprocess_limit(PlannerInfo *root, double tuple_fraction,
    return tuple_fraction;
 }
 
+/*
+ * limit_needed - do we actually need a Limit plan node?
+ *
+ * If we have constant-zero OFFSET and constant-null LIMIT, we can skip adding
+ * a Limit node.  This is worth checking for because "OFFSET 0" is a common
+ * locution for an optimization fence.  (Because other places in the planner
+ * merely check whether parse->limitOffset isn't NULL, it will still work as
+ * an optimization fence --- we're just suppressing unnecessary run-time
+ * overhead.)
+ *
+ * This might look like it could be merged into preprocess_limit, but there's
+ * a key distinction: here we need hard constants in OFFSET/LIMIT, whereas
+ * in preprocess_limit it's good enough to consider estimated values.
+ */
+static bool
+limit_needed(Query *parse)
+{
+   Node       *node;
+
+   node = parse->limitCount;
+   if (node)
+   {
+       if (IsA(node, Const))
+       {
+           /* NULL indicates LIMIT ALL, ie, no limit */
+           if (!((Const *) node)->constisnull)
+               return true;    /* LIMIT with a constant value */
+       }
+       else
+           return true;        /* non-constant LIMIT */
+   }
+
+   node = parse->limitOffset;
+   if (node)
+   {
+       if (IsA(node, Const))
+       {
+           /* Treat NULL as no offset; the executor would too */
+           if (!((Const *) node)->constisnull)
+           {
+               int64   offset = DatumGetInt64(((Const *) node)->constvalue);
+
+               /* Executor would treat less-than-zero same as zero */
+               if (offset > 0)
+                   return true;    /* OFFSET with a positive value */
+           }
+       }
+       else
+           return true;        /* non-constant OFFSET */
+   }
+
+   return false;               /* don't need a Limit plan node */
+}
+
 
 /*
  * preprocess_groupclause - do preparatory work on GROUP BY clause

diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index ead08d69b10fd302acbc0d8e5438ea1aad22ccff..005bb0961e8b7c10464b6d61bdbc9339c1438379 100644 (file)
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -542,36 +542,34 @@ SELECT * FROM rw_view2;
 (2 rows)
 
 EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
-                            QUERY PLAN                            
-------------------------------------------------------------------
+                           QUERY PLAN                           
+----------------------------------------------------------------
  Update on base_tbl
    ->  Nested Loop
          ->  Index Scan using base_tbl_pkey on base_tbl
                Index Cond: (a = 2)
          ->  Subquery Scan on rw_view1
                Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
-               ->  Limit
-                     ->  Bitmap Heap Scan on base_tbl base_tbl_1
-                           Recheck Cond: (a > 0)
-                           ->  Bitmap Index Scan on base_tbl_pkey
-                                 Index Cond: (a > 0)
-(11 rows)
+               ->  Bitmap Heap Scan on base_tbl base_tbl_1
+                     Recheck Cond: (a > 0)
+                     ->  Bitmap Index Scan on base_tbl_pkey
+                           Index Cond: (a > 0)
+(10 rows)
 
 EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
-                            QUERY PLAN                            
-------------------------------------------------------------------
+                           QUERY PLAN                           
+----------------------------------------------------------------
  Delete on base_tbl
    ->  Nested Loop
          ->  Index Scan using base_tbl_pkey on base_tbl
                Index Cond: (a = 2)
          ->  Subquery Scan on rw_view1
                Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
-               ->  Limit
-                     ->  Bitmap Heap Scan on base_tbl base_tbl_1
-                           Recheck Cond: (a > 0)
-                           ->  Bitmap Index Scan on base_tbl_pkey
-                                 Index Cond: (a > 0)
-(11 rows)
+               ->  Bitmap Heap Scan on base_tbl base_tbl_1
+                     Recheck Cond: (a > 0)
+                     ->  Bitmap Index Scan on base_tbl_pkey
+                           Index Cond: (a > 0)
+(10 rows)
 
 DROP TABLE base_tbl CASCADE;
 NOTICE:  drop cascades to 2 other objects
@@ -775,30 +773,28 @@ SELECT * FROM rw_view2;
 (2 rows)
 
 EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
-                         QUERY PLAN                         
-------------------------------------------------------------
+                        QUERY PLAN                        
+----------------------------------------------------------
  Update on rw_view1 rw_view1_1
    ->  Subquery Scan on rw_view1
          Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
-         ->  Limit
-               ->  Bitmap Heap Scan on base_tbl
-                     Recheck Cond: (a > 0)
-                     ->  Bitmap Index Scan on base_tbl_pkey
-                           Index Cond: (a > 0)
-(8 rows)
+         ->  Bitmap Heap Scan on base_tbl
+               Recheck Cond: (a > 0)
+               ->  Bitmap Index Scan on base_tbl_pkey
+                     Index Cond: (a > 0)
+(7 rows)
 
 EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
-                         QUERY PLAN                         
-------------------------------------------------------------
+                        QUERY PLAN                        
+----------------------------------------------------------
  Delete on rw_view1 rw_view1_1
    ->  Subquery Scan on rw_view1
          Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
-         ->  Limit
-               ->  Bitmap Heap Scan on base_tbl
-                     Recheck Cond: (a > 0)
-                     ->  Bitmap Index Scan on base_tbl_pkey
-                           Index Cond: (a > 0)
-(8 rows)
+         ->  Bitmap Heap Scan on base_tbl
+               Recheck Cond: (a > 0)
+               ->  Bitmap Index Scan on base_tbl_pkey
+                     Index Cond: (a > 0)
+(7 rows)
 
 DROP TABLE base_tbl CASCADE;
 NOTICE:  drop cascades to 2 other objects