Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commitb262ad4

Browse files
committed
Add better handling of redundant IS [NOT] NULL quals
Until now PostgreSQL has not been very smart about optimizing away ISNOT NULL base quals on columns defined as NOT NULL. The evaluation ofthese needless quals adds overhead. Ordinarily, anyone who camecomplaining about that would likely just have been told to not includethe qual in their query if it's not required. However, a recent bugreport indicates this might not always be possible.Bug 17540 highlighted that when we optimize Min/Max aggregates the IS NOTNULL qual that the planner adds to make the rewritten plan ignore NULLscan cause issues with poor index choice. That particular casedemonstrated that other quals, especially ones where no statistics areavailable to allow the planner a chance at estimating an approximateselectivity for can result in poor index choice due to cheap startup pathsbeing prefered with LIMIT 1.Here we take generic approach to fixing this by having the planner checkfor NOT NULL columns and just have the planner remove these quals (whenthey're not needed) for all queries, not just when optimizing Min/Maxaggregates.Additionally, here we also detect IS NULL quals on a NOT NULL column andtransform that into a gating qual so that we don't have to perform thescan at all. This also works for join relations when the Var is notnullable by any outer join.This also helps with the self-join removal work as it must replacestrict join quals with IS NOT NULL quals to ensure equivalence with theoriginal query.Author: David Rowley, Richard Guo, Andy FanReviewed-by: Richard Guo, David RowleyDiscussion:https://postgr.es/m/CAApHDvqg6XZDhYRPz0zgOcevSMo0d3vxA9DvHrZtKfqO30WTnw@mail.gmail.comDiscussion:https://postgr.es/m/17540-7aa1855ad5ec18b4%40postgresql.org
1 parent183b6f7 commitb262ad4

File tree

13 files changed

+664
-67
lines changed

13 files changed

+664
-67
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines 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 WHEREc1 IS NULL; -- NullTest
660-
QUERY PLAN
661-
-------------------------------------------------------------------------------------------------
659+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHEREc3 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 WHEREc1 IS NOT NULL; -- NullTest
668-
QUERY PLAN
669-
-----------------------------------------------------------------------------------------------------
667+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHEREc3 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

Lines 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 t1WHEREt1.c1=1;-- Var, OpExpr(b), Const
334334
EXPLAIN (VERBOSE, COSTS OFF)SELECT*FROM ft1 t1WHEREt1.c1=100ANDt1.c2=0;-- BoolExpr
335-
EXPLAIN (VERBOSE, COSTS OFF)SELECT*FROM ft1 t1WHEREc1 ISNULL;-- NullTest
336-
EXPLAIN (VERBOSE, COSTS OFF)SELECT*FROM ft1 t1WHEREc1IS NOT NULL;-- NullTest
335+
EXPLAIN (VERBOSE, COSTS OFF)SELECT*FROM ft1 t1WHEREc3 ISNULL;-- NullTest
336+
EXPLAIN (VERBOSE, COSTS OFF)SELECT*FROM ft1 t1WHEREc3IS NOT NULL;-- NullTest
337337
EXPLAIN (VERBOSE, COSTS OFF)SELECT*FROM ft1 t1WHERE round(abs(c1),0)=1;-- FuncExpr
338338
EXPLAIN (VERBOSE, COSTS OFF)SELECT*FROM ft1 t1WHERE c1=-c1;-- OpExpr(l)
339339
EXPLAIN (VERBOSE, COSTS OFF)SELECT*FROM ft1 t1WHERE (c1IS NOT NULL) IS DISTINCTFROM (c1IS NOT NULL);-- DistinctExpr

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

Lines 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+
staticvoid
2630+
add_base_clause_to_rel(PlannerInfo*root,Indexrelid,
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+
intsave_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+
staticbool
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+
returnexpr_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+
returnexpr_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
Relidsrelids=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

Lines 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
intcur_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+
intsave_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

Lines 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 (inti=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

Lines 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

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -913,6 +913,8 @@ typedef struct RelOptInfo
913913
Relids*attr_neededpg_node_attr(read_write_ignore);
914914
/* array indexed [min_attr .. max_attr] */
915915
int32*attr_widthspg_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
Relidsnulling_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

Lines 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
externvoidfind_lateral_references(PlannerInfo*root);
7777
externvoidcreate_lateral_join_info(PlannerInfo*root);
7878
externList*deconstruct_jointree(PlannerInfo*root);
79+
externboolrestriction_is_always_true(PlannerInfo*root,
80+
RestrictInfo*restrictinfo);
81+
externboolrestriction_is_always_false(PlannerInfo*root,
82+
RestrictInfo*restrictinfo);
7983
externvoiddistribute_restrictinfo_to_rels(PlannerInfo*root,
8084
RestrictInfo*restrictinfo);
8185
externRestrictInfo*process_implied_equality(PlannerInfo*root,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp