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
Appearance settings

Commit b262ad4

Browse filesBrowse files
david-rowleydavid-rowley
committed
Add better handling of redundant IS [NOT] NULL quals
Until now PostgreSQL has not been very smart about optimizing away IS NOT NULL base quals on columns defined as NOT NULL. The evaluation of these needless quals adds overhead. Ordinarily, anyone who came complaining about that would likely just have been told to not include the qual in their query if it's not required. However, a recent bug report indicates this might not always be possible. Bug 17540 highlighted that when we optimize Min/Max aggregates the IS NOT NULL qual that the planner adds to make the rewritten plan ignore NULLs can cause issues with poor index choice. That particular case demonstrated that other quals, especially ones where no statistics are available to allow the planner a chance at estimating an approximate selectivity for can result in poor index choice due to cheap startup paths being prefered with LIMIT 1. Here we take generic approach to fixing this by having the planner check for NOT NULL columns and just have the planner remove these quals (when they're not needed) for all queries, not just when optimizing Min/Max aggregates. Additionally, here we also detect IS NULL quals on a NOT NULL column and transform that into a gating qual so that we don't have to perform the scan at all. This also works for join relations when the Var is not nullable by any outer join. This also helps with the self-join removal work as it must replace strict join quals with IS NOT NULL quals to ensure equivalence with the original query. Author: David Rowley, Richard Guo, Andy Fan Reviewed-by: Richard Guo, David Rowley Discussion: https://postgr.es/m/CAApHDvqg6XZDhYRPz0zgOcevSMo0d3vxA9DvHrZtKfqO30WTnw@mail.gmail.com Discussion: https://postgr.es/m/17540-7aa1855ad5ec18b4%40postgresql.org
1 parent 183b6f7 commit b262ad4
Copy full SHA for b262ad4

File tree

Expand file treeCollapse file tree

13 files changed

+664
-67
lines changed
Filter options
Expand file treeCollapse file tree

13 files changed

+664
-67
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Copy file name to clipboardExpand all lines: contrib/postgres_fdw/expected/postgres_fdw.out
+8-8Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -656,20 +656,20 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 =
656656
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 100)) AND ((c2 = 0))
657657
(3 rows)
658658

659-
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest
660-
QUERY PLAN
661-
-------------------------------------------------------------------------------------------------
659+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NULL; -- NullTest
660+
QUERY PLAN
661+
----------------------------------------------------------------------------------------------
662662
Foreign Scan on public.ft1 t1
663663
Output: c1, c2, c3, c4, c5, c6, c7, c8
664-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" IS NULL))
664+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c3 IS NULL))
665665
(3 rows)
666666

667-
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest
668-
QUERY PLAN
669-
-----------------------------------------------------------------------------------------------------
667+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NOT NULL; -- NullTest
668+
QUERY PLAN
669+
--------------------------------------------------------------------------------------------------
670670
Foreign Scan on public.ft1 t1
671671
Output: c1, c2, c3, c4, c5, c6, c7, c8
672-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" IS NOT NULL))
672+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c3 IS NOT NULL))
673673
(3 rows)
674674

675675
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Copy file name to clipboardExpand all lines: contrib/postgres_fdw/sql/postgres_fdw.sql
+2-2Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -332,8 +332,8 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft_empty ORDER BY c1;
332332
-- ===================================================================
333333
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 1; -- Var, OpExpr(b), Const
334334
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr
335-
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest
336-
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest
335+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NULL; -- NullTest
336+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NOT NULL; -- NullTest
337337
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
338338
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l)
339339
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr

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

Copy file name to clipboardExpand all lines: src/backend/optimizer/plan/initsplan.c
+188-9Lines changed: 188 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -2618,6 +2618,193 @@ check_redundant_nullability_qual(PlannerInfo *root, Node *clause)
26182618
return false;
26192619
}
26202620

2621+
/*
2622+
* add_base_clause_to_rel
2623+
* Add 'restrictinfo' as a baserestrictinfo to the base relation denoted
2624+
* by 'relid'. We offer some simple prechecks to try to determine if the
2625+
* qual is always true, in which case we ignore it rather than add it.
2626+
* If we detect the qual is always false, we replace it with
2627+
* constant-FALSE.
2628+
*/
2629+
static void
2630+
add_base_clause_to_rel(PlannerInfo *root, Index relid,
2631+
RestrictInfo *restrictinfo)
2632+
{
2633+
RelOptInfo *rel = find_base_rel(root, relid);
2634+
2635+
Assert(bms_membership(restrictinfo->required_relids) == BMS_SINGLETON);
2636+
2637+
/* Don't add the clause if it is always true */
2638+
if (restriction_is_always_true(root, restrictinfo))
2639+
return;
2640+
2641+
/*
2642+
* Substitute the origin qual with constant-FALSE if it is provably always
2643+
* false. Note that we keep the same rinfo_serial.
2644+
*/
2645+
if (restriction_is_always_false(root, restrictinfo))
2646+
{
2647+
int save_rinfo_serial = restrictinfo->rinfo_serial;
2648+
2649+
restrictinfo = make_restrictinfo(root,
2650+
(Expr *) makeBoolConst(false, false),
2651+
restrictinfo->is_pushed_down,
2652+
restrictinfo->has_clone,
2653+
restrictinfo->is_clone,
2654+
restrictinfo->pseudoconstant,
2655+
0, /* security_level */
2656+
restrictinfo->required_relids,
2657+
restrictinfo->incompatible_relids,
2658+
restrictinfo->outer_relids);
2659+
restrictinfo->rinfo_serial = save_rinfo_serial;
2660+
}
2661+
2662+
/* Add clause to rel's restriction list */
2663+
rel->baserestrictinfo = lappend(rel->baserestrictinfo, restrictinfo);
2664+
2665+
/* Update security level info */
2666+
rel->baserestrict_min_security = Min(rel->baserestrict_min_security,
2667+
restrictinfo->security_level);
2668+
}
2669+
2670+
/*
2671+
* expr_is_nonnullable
2672+
* Check to see if the Expr cannot be NULL
2673+
*
2674+
* If the Expr is a simple Var that is defined NOT NULL and meanwhile is not
2675+
* nulled by any outer joins, then we can know that it cannot be NULL.
2676+
*/
2677+
static bool
2678+
expr_is_nonnullable(PlannerInfo *root, Expr *expr)
2679+
{
2680+
RelOptInfo *rel;
2681+
Var *var;
2682+
2683+
/* For now only check simple Vars */
2684+
if (!IsA(expr, Var))
2685+
return false;
2686+
2687+
var = (Var *) expr;
2688+
2689+
/* could the Var be nulled by any outer joins? */
2690+
if (!bms_is_empty(var->varnullingrels))
2691+
return false;
2692+
2693+
/* system columns cannot be NULL */
2694+
if (var->varattno < 0)
2695+
return true;
2696+
2697+
/* is the column defined NOT NULL? */
2698+
rel = find_base_rel(root, var->varno);
2699+
if (var->varattno > 0 &&
2700+
bms_is_member(var->varattno, rel->notnullattnums))
2701+
return true;
2702+
2703+
return false;
2704+
}
2705+
2706+
/*
2707+
* restriction_is_always_true
2708+
* Check to see if the RestrictInfo is always true.
2709+
*
2710+
* Currently we only check for NullTest quals and OR clauses that include
2711+
* NullTest quals. We may extend it in the future.
2712+
*/
2713+
bool
2714+
restriction_is_always_true(PlannerInfo *root,
2715+
RestrictInfo *restrictinfo)
2716+
{
2717+
/* Check for NullTest qual */
2718+
if (IsA(restrictinfo->clause, NullTest))
2719+
{
2720+
NullTest *nulltest = (NullTest *) restrictinfo->clause;
2721+
2722+
/* is this NullTest an IS_NOT_NULL qual? */
2723+
if (nulltest->nulltesttype != IS_NOT_NULL)
2724+
return false;
2725+
2726+
return expr_is_nonnullable(root, nulltest->arg);
2727+
}
2728+
2729+
/* If it's an OR, check its sub-clauses */
2730+
if (restriction_is_or_clause(restrictinfo))
2731+
{
2732+
ListCell *lc;
2733+
2734+
Assert(is_orclause(restrictinfo->orclause));
2735+
2736+
/*
2737+
* if any of the given OR branches is provably always true then the
2738+
* entire condition is true.
2739+
*/
2740+
foreach(lc, ((BoolExpr *) restrictinfo->orclause)->args)
2741+
{
2742+
Node *orarg = (Node *) lfirst(lc);
2743+
2744+
if (!IsA(orarg, RestrictInfo))
2745+
continue;
2746+
2747+
if (restriction_is_always_true(root, (RestrictInfo *) orarg))
2748+
return true;
2749+
}
2750+
}
2751+
2752+
return false;
2753+
}
2754+
2755+
/*
2756+
* restriction_is_always_false
2757+
* Check to see if the RestrictInfo is always false.
2758+
*
2759+
* Currently we only check for NullTest quals and OR clauses that include
2760+
* NullTest quals. We may extend it in the future.
2761+
*/
2762+
bool
2763+
restriction_is_always_false(PlannerInfo *root,
2764+
RestrictInfo *restrictinfo)
2765+
{
2766+
/* Check for NullTest qual */
2767+
if (IsA(restrictinfo->clause, NullTest))
2768+
{
2769+
NullTest *nulltest = (NullTest *) restrictinfo->clause;
2770+
2771+
/* is this NullTest an IS_NULL qual? */
2772+
if (nulltest->nulltesttype != IS_NULL)
2773+
return false;
2774+
2775+
return expr_is_nonnullable(root, nulltest->arg);
2776+
}
2777+
2778+
/* If it's an OR, check its sub-clauses */
2779+
if (restriction_is_or_clause(restrictinfo))
2780+
{
2781+
ListCell *lc;
2782+
2783+
Assert(is_orclause(restrictinfo->orclause));
2784+
2785+
/*
2786+
* Currently, when processing OR expressions, we only return true when
2787+
* all of the OR branches are always false. This could perhaps be
2788+
* expanded to remove OR branches that are provably false. This may
2789+
* be a useful thing to do as it could result in the OR being left
2790+
* with a single arg. That's useful as it would allow the OR
2791+
* condition to be replaced with its single argument which may allow
2792+
* use of an index for faster filtering on the remaining condition.
2793+
*/
2794+
foreach(lc, ((BoolExpr *) restrictinfo->orclause)->args)
2795+
{
2796+
Node *orarg = (Node *) lfirst(lc);
2797+
2798+
if (!IsA(orarg, RestrictInfo) ||
2799+
!restriction_is_always_false(root, (RestrictInfo *) orarg))
2800+
return false;
2801+
}
2802+
return true;
2803+
}
2804+
2805+
return false;
2806+
}
2807+
26212808
/*
26222809
* distribute_restrictinfo_to_rels
26232810
* Push a completed RestrictInfo into the proper restriction or join
@@ -2632,7 +2819,6 @@ distribute_restrictinfo_to_rels(PlannerInfo *root,
26322819
RestrictInfo *restrictinfo)
26332820
{
26342821
Relids relids = restrictinfo->required_relids;
2635-
RelOptInfo *rel;
26362822

26372823
if (!bms_is_empty(relids))
26382824
{
@@ -2644,14 +2830,7 @@ distribute_restrictinfo_to_rels(PlannerInfo *root,
26442830
* There is only one relation participating in the clause, so it
26452831
* is a restriction clause for that relation.
26462832
*/
2647-
rel = find_base_rel(root, relid);
2648-
2649-
/* Add clause to rel's restriction list */
2650-
rel->baserestrictinfo = lappend(rel->baserestrictinfo,
2651-
restrictinfo);
2652-
/* Update security level info */
2653-
rel->baserestrict_min_security = Min(rel->baserestrict_min_security,
2654-
restrictinfo->security_level);
2833+
add_base_clause_to_rel(root, relid, restrictinfo);
26552834
}
26562835
else
26572836
{

‎src/backend/optimizer/util/joininfo.c

Copy file name to clipboardExpand all lines: src/backend/optimizer/util/joininfo.c
+28Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,9 +14,12 @@
1414
*/
1515
#include "postgres.h"
1616

17+
#include "nodes/makefuncs.h"
1718
#include "optimizer/joininfo.h"
1819
#include "optimizer/pathnode.h"
1920
#include "optimizer/paths.h"
21+
#include "optimizer/planmain.h"
22+
#include "optimizer/restrictinfo.h"
2023

2124

2225
/*
@@ -98,6 +101,31 @@ add_join_clause_to_rels(PlannerInfo *root,
98101
{
99102
int cur_relid;
100103

104+
/* Don't add the clause if it is always true */
105+
if (restriction_is_always_true(root, restrictinfo))
106+
return;
107+
108+
/*
109+
* Substitute constant-FALSE for the origin qual if it is always false.
110+
* Note that we keep the same rinfo_serial.
111+
*/
112+
if (restriction_is_always_false(root, restrictinfo))
113+
{
114+
int save_rinfo_serial = restrictinfo->rinfo_serial;
115+
116+
restrictinfo = make_restrictinfo(root,
117+
(Expr *) makeBoolConst(false, false),
118+
restrictinfo->is_pushed_down,
119+
restrictinfo->has_clone,
120+
restrictinfo->is_clone,
121+
restrictinfo->pseudoconstant,
122+
0, /* security_level */
123+
restrictinfo->required_relids,
124+
restrictinfo->incompatible_relids,
125+
restrictinfo->outer_relids);
126+
restrictinfo->rinfo_serial = save_rinfo_serial;
127+
}
128+
101129
cur_relid = -1;
102130
while ((cur_relid = bms_next_member(join_relids, cur_relid)) >= 0)
103131
{

‎src/backend/optimizer/util/plancat.c

Copy file name to clipboardExpand all lines: src/backend/optimizer/util/plancat.c
+19Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -163,6 +163,25 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
163163
rel->attr_widths = (int32 *)
164164
palloc0((rel->max_attr - rel->min_attr + 1) * sizeof(int32));
165165

166+
/* record which columns are defined as NOT NULL */
167+
for (int i = 0; i < relation->rd_att->natts; i++)
168+
{
169+
FormData_pg_attribute *attr = &relation->rd_att->attrs[i];
170+
171+
if (attr->attnotnull)
172+
{
173+
rel->notnullattnums = bms_add_member(rel->notnullattnums,
174+
attr->attnum);
175+
176+
/*
177+
* Per RemoveAttributeById(), dropped columns will have their
178+
* attnotnull unset, so we needn't check for dropped columns in
179+
* the above condition.
180+
*/
181+
Assert(!attr->attisdropped);
182+
}
183+
}
184+
166185
/*
167186
* Estimate relation size --- unless it's an inheritance parent, in which
168187
* case the size we want is not the rel's own size but the size of its

‎src/backend/optimizer/util/relnode.c

Copy file name to clipboardExpand all lines: src/backend/optimizer/util/relnode.c
+3Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -222,6 +222,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
222222
rel->relid = relid;
223223
rel->rtekind = rte->rtekind;
224224
/* min_attr, max_attr, attr_needed, attr_widths are set below */
225+
rel->notnullattnums = NULL;
225226
rel->lateral_vars = NIL;
226227
rel->indexlist = NIL;
227228
rel->statlist = NIL;
@@ -719,6 +720,7 @@ build_join_rel(PlannerInfo *root,
719720
joinrel->max_attr = 0;
720721
joinrel->attr_needed = NULL;
721722
joinrel->attr_widths = NULL;
723+
joinrel->notnullattnums = NULL;
722724
joinrel->nulling_relids = NULL;
723725
joinrel->lateral_vars = NIL;
724726
joinrel->lateral_referencers = NULL;
@@ -917,6 +919,7 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
917919
joinrel->max_attr = 0;
918920
joinrel->attr_needed = NULL;
919921
joinrel->attr_widths = NULL;
922+
joinrel->notnullattnums = NULL;
920923
joinrel->nulling_relids = NULL;
921924
joinrel->lateral_vars = NIL;
922925
joinrel->lateral_referencers = NULL;

‎src/include/nodes/pathnodes.h

Copy file name to clipboardExpand all lines: src/include/nodes/pathnodes.h
+6-1Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -913,6 +913,8 @@ typedef struct RelOptInfo
913913
Relids *attr_needed pg_node_attr(read_write_ignore);
914914
/* array indexed [min_attr .. max_attr] */
915915
int32 *attr_widths pg_node_attr(read_write_ignore);
916+
/* zero-based set containing attnums of NOT NULL columns */
917+
Bitmapset *notnullattnums;
916918
/* relids of outer joins that can null this baserel */
917919
Relids nulling_relids;
918920
/* LATERAL Vars and PHVs referenced by rel */
@@ -2598,7 +2600,10 @@ typedef struct RestrictInfo
25982600
* 2. If we manufacture a commuted version of a qual to use as an index
25992601
* condition, it copies the original's rinfo_serial, since it is in
26002602
* practice the same condition.
2601-
* 3. RestrictInfos made for a child relation copy their parent's
2603+
* 3. If we reduce a qual to constant-FALSE, the new constant-FALSE qual
2604+
* copies the original's rinfo_serial, since it is in practice the same
2605+
* condition.
2606+
* 4. RestrictInfos made for a child relation copy their parent's
26022607
* rinfo_serial. Likewise, when an EquivalenceClass makes a derived
26032608
* equality clause for a child relation, it copies the rinfo_serial of
26042609
* the matching equality clause for the parent. This allows detection

‎src/include/optimizer/planmain.h

Copy file name to clipboardExpand all lines: src/include/optimizer/planmain.h
+4Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -76,6 +76,10 @@ extern void add_vars_to_targetlist(PlannerInfo *root, List *vars,
7676
extern void find_lateral_references(PlannerInfo *root);
7777
extern void create_lateral_join_info(PlannerInfo *root);
7878
extern List *deconstruct_jointree(PlannerInfo *root);
79+
extern bool restriction_is_always_true(PlannerInfo *root,
80+
RestrictInfo *restrictinfo);
81+
extern bool restriction_is_always_false(PlannerInfo *root,
82+
RestrictInfo *restrictinfo);
7983
extern void distribute_restrictinfo_to_rels(PlannerInfo *root,
8084
RestrictInfo *restrictinfo);
8185
extern RestrictInfo *process_implied_equality(PlannerInfo *root,

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.