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

Commit9581103

Browse files
committed
Improve planner to drop constant-NULL inputs of AND/OR where it's legal.
In general we can't discard constant-NULL inputs, since they could changethe result of the AND/OR to be NULL. But at top level of WHERE, we do notneed to distinguish a NULL result from a FALSE result, so it's okay totreat NULL as FALSE and then simplify AND/OR accordingly.This is a very ancient oversight, but in 9.2 and later it can lead tofailure to optimize queries that previous releases did optimize, as aresult of more aggressive parameter substitution rules making it possibleto reduce more subexpressions to NULL constants. This is the root cause ofbug #10171 from Arnold Scheffler. We could alternatively have fixed thatby teaching orclauses.c to ignore constant-NULL OR arms, but it seemsbetter to get rid of them globally.I resisted the temptation to back-patch this change into all activebranches, but it seems appropriate to back-patch as far as 9.2 so thatthere will not be performance regressions of the kind shown in this bug.
1 parentdbe3161 commit9581103

File tree

3 files changed

+85
-12
lines changed

3 files changed

+85
-12
lines changed

‎src/backend/optimizer/prep/prepqual.c

Lines changed: 67 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -293,7 +293,7 @@ canonicalize_qual(Expr *qual)
293293
/*
294294
* Pull up redundant subclauses in OR-of-AND trees. We do this only
295295
* within the top-level AND/OR structure; there's no point in looking
296-
* deeper.
296+
* deeper.Also remove any NULL constants in the top-level structure.
297297
*/
298298
newqual=find_duplicate_ors(qual);
299299

@@ -393,6 +393,13 @@ pull_ors(List *orlist)
393393
* OR clauses to which the inverse OR distributive law might apply.
394394
* Only the top-level AND/OR structure is searched.
395395
*
396+
* While at it, we remove any NULL constants within the top-level AND/OR
397+
* structure, eg "x OR NULL::boolean" is reduced to "x". In general that
398+
* would change the result, so eval_const_expressions can't do it; but at
399+
* top level of WHERE, we don't need to distinguish between FALSE and NULL
400+
* results, so it's valid to treat NULL::boolean the same as FALSE and then
401+
* simplify AND/OR accordingly.
402+
*
396403
* Returns the modified qualification.AND/OR flatness is preserved.
397404
*/
398405
staticExpr*
@@ -405,12 +412,30 @@ find_duplicate_ors(Expr *qual)
405412

406413
/* Recurse */
407414
foreach(temp, ((BoolExpr*)qual)->args)
408-
orlist=lappend(orlist,find_duplicate_ors(lfirst(temp)));
415+
{
416+
Expr*arg= (Expr*)lfirst(temp);
409417

410-
/*
411-
* Don't need pull_ors() since this routine will never introduce an OR
412-
* where there wasn't one before.
413-
*/
418+
arg=find_duplicate_ors(arg);
419+
420+
/* Get rid of any constant inputs */
421+
if (arg&&IsA(arg,Const))
422+
{
423+
Const*carg= (Const*)arg;
424+
425+
/* Drop constant FALSE or NULL */
426+
if (carg->constisnull|| !DatumGetBool(carg->constvalue))
427+
continue;
428+
/* constant TRUE, so OR reduces to TRUE */
429+
returnarg;
430+
}
431+
432+
orlist=lappend(orlist,arg);
433+
}
434+
435+
/* Flatten any ORs pulled up to just below here */
436+
orlist=pull_ors(orlist);
437+
438+
/* Now we can look for duplicate ORs */
414439
returnprocess_duplicate_ors(orlist);
415440
}
416441
elseif (and_clause((Node*)qual))
@@ -420,10 +445,38 @@ find_duplicate_ors(Expr *qual)
420445

421446
/* Recurse */
422447
foreach(temp, ((BoolExpr*)qual)->args)
423-
andlist=lappend(andlist,find_duplicate_ors(lfirst(temp)));
448+
{
449+
Expr*arg= (Expr*)lfirst(temp);
450+
451+
arg=find_duplicate_ors(arg);
452+
453+
/* Get rid of any constant inputs */
454+
if (arg&&IsA(arg,Const))
455+
{
456+
Const*carg= (Const*)arg;
457+
458+
/* Drop constant TRUE */
459+
if (!carg->constisnull&&DatumGetBool(carg->constvalue))
460+
continue;
461+
/* constant FALSE or NULL, so AND reduces to FALSE */
462+
return (Expr*)makeBoolConst(false, false);
463+
}
464+
465+
andlist=lappend(andlist,arg);
466+
}
467+
424468
/* Flatten any ANDs introduced just below here */
425469
andlist=pull_ands(andlist);
426-
/* The AND list can't get shorter, so result is always an AND */
470+
471+
/* AND of no inputs reduces to TRUE */
472+
if (andlist==NIL)
473+
return (Expr*)makeBoolConst(true, false);
474+
475+
/* Single-expression AND just reduces to that expression */
476+
if (list_length(andlist)==1)
477+
return (Expr*)linitial(andlist);
478+
479+
/* Else we still need an AND node */
427480
returnmake_andclause(andlist);
428481
}
429482
else
@@ -447,11 +500,13 @@ process_duplicate_ors(List *orlist)
447500
List*neworlist;
448501
ListCell*temp;
449502

503+
/* OR of no inputs reduces to FALSE */
450504
if (orlist==NIL)
451-
returnNULL;/* probably can't happen */
452-
if (list_length(orlist)==1)/* single-expression OR (can this
453-
* happen?) */
454-
returnlinitial(orlist);
505+
return (Expr*)makeBoolConst(false, false);
506+
507+
/* Single-expression OR just reduces to that expression */
508+
if (list_length(orlist)==1)
509+
return (Expr*)linitial(orlist);
455510

456511
/*
457512
* Choose the shortest AND clause as the reference list --- obviously, any

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2771,3 +2771,14 @@ ORDER BY thousand;
27712771
1 | 1001
27722772
(2 rows)
27732773

2774+
--
2775+
-- Check elimination of constant-NULL subexpressions
2776+
--
2777+
explain (costs off)
2778+
select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null));
2779+
QUERY PLAN
2780+
------------------------------------------------------
2781+
Index Scan using tenk1_thous_tenthous on tenk1
2782+
Index Cond: ((thousand = 1) AND (tenthous = 1001))
2783+
(2 rows)
2784+

‎src/test/regress/sql/create_index.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -931,3 +931,10 @@ ORDER BY thousand;
931931
SELECT thousand, tenthousFROM tenk1
932932
WHERE thousand<2AND tenthousIN (1001,3000)
933933
ORDER BY thousand;
934+
935+
--
936+
-- Check elimination of constant-NULL subexpressions
937+
--
938+
939+
explain (costs off)
940+
select*from tenk1where (thousand, tenthous)in ((1,1001), (null,null));

0 commit comments

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