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

Commit8ec5429

Browse files
committed
Reduce "X = X" to "X IS NOT NULL", if it's easy to do so.
If the operator is a strict btree equality operator, and X isn't volatile,then the clause must yield true for any non-null value of X, or null if Xis null. At top level of a WHERE clause, we can ignore the distinctionbetween false and null results, so it's valid to simplify the clause to"X IS NOT NULL". This is a useful improvement mainly because we'll geta far better selectivity estimate in most cases.Because such cases seldom arise in well-written queries, it is unappetizingto expend a lot of planner cycles looking for them ... but it turns outthat there's a place we can shoehorn this in practically for free, becauseequivclass.c already has to detect and reject candidate equivalences of theform X = X. That doesn't catch every place that it would be valid tosimplify to X IS NOT NULL, but it catches the typical case. Working harderdoesn't seem justified.Patch by me, reviewed by Petr JelinekDiscussion:https://postgr.es/m/CAMjNa7cC4X9YR-vAJS-jSYCajhRDvJQnN7m2sLH1wLh-_Z2bsw@mail.gmail.com
1 parentb11f0d3 commit8ec5429

File tree

5 files changed

+83
-17
lines changed

5 files changed

+83
-17
lines changed

‎src/backend/optimizer/path/equivclass.c

Lines changed: 52 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@
2727
#include"optimizer/paths.h"
2828
#include"optimizer/planmain.h"
2929
#include"optimizer/prep.h"
30+
#include"optimizer/restrictinfo.h"
3031
#include"optimizer/var.h"
3132
#include"utils/lsyscache.h"
3233

@@ -71,8 +72,14 @@ static bool reconsider_full_join_clause(PlannerInfo *root,
7172
* any delay by an outer join, so its two sides can be considered equal
7273
* anywhere they are both computable; moreover that equality can be
7374
* extended transitively. Record this knowledge in the EquivalenceClass
74-
* data structure. Returns TRUE if successful, FALSE if not (in which
75-
* case caller should treat the clause as ordinary, not an equivalence).
75+
* data structure, if applicable. Returns TRUE if successful, FALSE if not
76+
* (in which case caller should treat the clause as ordinary, not an
77+
* equivalence).
78+
*
79+
* In some cases, although we cannot convert a clause into EquivalenceClass
80+
* knowledge, we can still modify it to a more useful form than the original.
81+
* Then, *p_restrictinfo will be replaced by a new RestrictInfo, which is what
82+
* the caller should use for further processing.
7683
*
7784
* If below_outer_join is true, then the clause was found below the nullable
7885
* side of an outer join, so its sides might validly be both NULL rather than
@@ -104,9 +111,11 @@ static bool reconsider_full_join_clause(PlannerInfo *root,
104111
* memory context.
105112
*/
106113
bool
107-
process_equivalence(PlannerInfo*root,RestrictInfo*restrictinfo,
114+
process_equivalence(PlannerInfo*root,
115+
RestrictInfo**p_restrictinfo,
108116
boolbelow_outer_join)
109117
{
118+
RestrictInfo*restrictinfo=*p_restrictinfo;
110119
Expr*clause=restrictinfo->clause;
111120
Oidopno,
112121
collation,
@@ -154,16 +163,45 @@ process_equivalence(PlannerInfo *root, RestrictInfo *restrictinfo,
154163
collation);
155164

156165
/*
157-
* Reject clauses of the form X=X. These are not as redundant as they
158-
* might seem at first glance: assuming the operator is strict, this is
159-
* really an expensive way to write X IS NOT NULL. So we must not risk
160-
* just losing the clause, which would be possible if there is already a
161-
* single-element EquivalenceClass containing X. The case is not common
162-
* enough to be worth contorting the EC machinery for, so just reject the
163-
* clause and let it be processed as a normal restriction clause.
166+
* Clauses of the form X=X cannot be translated into EquivalenceClasses.
167+
* We'd either end up with a single-entry EC, losing the knowledge that
168+
* the clause was present at all, or else make an EC with duplicate
169+
* entries, causing other issues.
164170
*/
165171
if (equal(item1,item2))
166-
return false;/* X=X is not a useful equivalence */
172+
{
173+
/*
174+
* If the operator is strict, then the clause can be treated as just
175+
* "X IS NOT NULL". (Since we know we are considering a top-level
176+
* qual, we can ignore the difference between FALSE and NULL results.)
177+
* It's worth making the conversion because we'll typically get a much
178+
* better selectivity estimate than we would for X=X.
179+
*
180+
* If the operator is not strict, we can't be sure what it will do
181+
* with NULLs, so don't attempt to optimize it.
182+
*/
183+
set_opfuncid((OpExpr*)clause);
184+
if (func_strict(((OpExpr*)clause)->opfuncid))
185+
{
186+
NullTest*ntest=makeNode(NullTest);
187+
188+
ntest->arg=item1;
189+
ntest->nulltesttype=IS_NOT_NULL;
190+
ntest->argisrow= false;/* correct even if composite arg */
191+
ntest->location=-1;
192+
193+
*p_restrictinfo=
194+
make_restrictinfo((Expr*)ntest,
195+
restrictinfo->is_pushed_down,
196+
restrictinfo->outerjoin_delayed,
197+
restrictinfo->pseudoconstant,
198+
restrictinfo->security_level,
199+
NULL,
200+
restrictinfo->outer_relids,
201+
restrictinfo->nullable_relids);
202+
}
203+
return false;
204+
}
167205

168206
/*
169207
* If below outer join, check for strictness, else reject.
@@ -1741,7 +1779,7 @@ reconsider_outer_join_clause(PlannerInfo *root, RestrictInfo *rinfo,
17411779
bms_copy(inner_relids),
17421780
bms_copy(inner_nullable_relids),
17431781
cur_ec->ec_min_security);
1744-
if (process_equivalence(root,newrinfo, true))
1782+
if (process_equivalence(root,&newrinfo, true))
17451783
match= true;
17461784
}
17471785

@@ -1884,7 +1922,7 @@ reconsider_full_join_clause(PlannerInfo *root, RestrictInfo *rinfo)
18841922
bms_copy(left_relids),
18851923
bms_copy(left_nullable_relids),
18861924
cur_ec->ec_min_security);
1887-
if (process_equivalence(root,newrinfo, true))
1925+
if (process_equivalence(root,&newrinfo, true))
18881926
matchleft= true;
18891927
}
18901928
eq_op=select_equality_operator(cur_ec,
@@ -1899,7 +1937,7 @@ reconsider_full_join_clause(PlannerInfo *root, RestrictInfo *rinfo)
18991937
bms_copy(right_relids),
19001938
bms_copy(right_nullable_relids),
19011939
cur_ec->ec_min_security);
1902-
if (process_equivalence(root,newrinfo, true))
1940+
if (process_equivalence(root,&newrinfo, true))
19031941
matchright= true;
19041942
}
19051943
}

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

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1964,10 +1964,11 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
19641964
if (maybe_equivalence)
19651965
{
19661966
if (check_equivalence_delay(root,restrictinfo)&&
1967-
process_equivalence(root,restrictinfo,below_outer_join))
1967+
process_equivalence(root,&restrictinfo,below_outer_join))
19681968
return;
19691969
/* EC rejected it, so set left_ec/right_ec the hard way ... */
1970-
initialize_mergeclause_eclasses(root,restrictinfo);
1970+
if (restrictinfo->mergeopfamilies)/* EC might have changed this */
1971+
initialize_mergeclause_eclasses(root,restrictinfo);
19711972
/* ... and fall through to distribute_restrictinfo_to_rels */
19721973
}
19731974
elseif (maybe_outer_join&&restrictinfo->can_join)

‎src/include/optimizer/paths.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -127,7 +127,8 @@ typedef bool (*ec_matches_callback_type) (PlannerInfo *root,
127127
EquivalenceMember*em,
128128
void*arg);
129129

130-
externboolprocess_equivalence(PlannerInfo*root,RestrictInfo*restrictinfo,
130+
externboolprocess_equivalence(PlannerInfo*root,
131+
RestrictInfo**p_restrictinfo,
131132
boolbelow_outer_join);
132133
externExpr*canonicalize_ec_expression(Expr*expr,
133134
Oidreq_type,Oidreq_collation);

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

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -421,3 +421,21 @@ reset session authorization;
421421
revoke select on ec0 from regress_user_ectest;
422422
revoke select on ec1 from regress_user_ectest;
423423
drop user regress_user_ectest;
424+
-- check that X=X is converted to X IS NOT NULL when appropriate
425+
explain (costs off)
426+
select * from tenk1 where unique1 = unique1 and unique2 = unique2;
427+
QUERY PLAN
428+
-------------------------------------------------------------
429+
Seq Scan on tenk1
430+
Filter: ((unique1 IS NOT NULL) AND (unique2 IS NOT NULL))
431+
(2 rows)
432+
433+
-- this could be converted, but isn't at present
434+
explain (costs off)
435+
select * from tenk1 where unique1 = unique1 or unique2 = unique2;
436+
QUERY PLAN
437+
--------------------------------------------------------
438+
Seq Scan on tenk1
439+
Filter: ((unique1 = unique1) OR (unique2 = unique2))
440+
(2 rows)
441+

‎src/test/regress/sql/equivclass.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -254,3 +254,11 @@ revoke select on ec0 from regress_user_ectest;
254254
revokeselecton ec1from regress_user_ectest;
255255

256256
dropuser regress_user_ectest;
257+
258+
-- check that X=X is converted to X IS NOT NULL when appropriate
259+
explain (costs off)
260+
select*from tenk1where unique1= unique1and unique2= unique2;
261+
262+
-- this could be converted, but isn't at present
263+
explain (costs off)
264+
select*from tenk1where unique1= unique1or unique2= unique2;

0 commit comments

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