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

Commitcdd230d

Browse files
committed
Improve planning of OR indexscan plans: for quals like
WHERE (a = 1 or a = 2) and b = 42and an index on (a,b), include the clause b = 42 in the indexqualsgenerated for each arm of the OR clause. Essentially this is an index-driven conversion from CNF to DNF. Implementation is a bit klugy, butbetter than not exploiting the extra quals at all ...
1 parent7c579fa commitcdd230d

File tree

4 files changed

+86
-61
lines changed

4 files changed

+86
-61
lines changed

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.75 2001/06/0505:26:04 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.76 2001/06/0517:13:51 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -232,7 +232,7 @@ set_plain_rel_pathlist(Query *root, RelOptInfo *rel, RangeTblEntry *rte)
232232
create_index_paths(root,rel);
233233

234234
/* create_index_paths must be done before create_or_index_paths */
235-
create_or_index_paths(root,rel,rel->baserestrictinfo);
235+
create_or_index_paths(root,rel);
236236

237237
/* Now find the cheapest of the paths for this rel */
238238
set_cheapest(rel);

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

Lines changed: 71 additions & 38 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/indxpath.c,v 1.105 2001/05/20 20:28:18 tgl Exp $
12+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/indxpath.c,v 1.106 2001/06/05 17:13:51 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -397,7 +397,7 @@ match_or_subclause_to_indexkey(RelOptInfo *rel,
397397
clause, false);
398398
}
399399

400-
/*
400+
/*----------
401401
* Given an OR subclause that has previously been determined to match
402402
* the specified index, extract a list of specific opclauses that can be
403403
* used as indexquals.
@@ -406,65 +406,98 @@ match_or_subclause_to_indexkey(RelOptInfo *rel,
406406
* given opclause.However, if the OR subclause is an AND, we have to
407407
* scan it to find the opclause(s) that match the index. (There should
408408
* be at least one, if match_or_subclause_to_indexkey succeeded, but there
409-
* could be more.)Also, we apply expand_indexqual_conditions() to convert
410-
* any special matching opclauses to indexable operators.
409+
* could be more.)
410+
*
411+
* Also, we can look at other restriction clauses of the rel to discover
412+
* additional candidate indexquals: for example, consider
413+
*... where (a = 11 or a = 12) and b = 42;
414+
* If we are dealing with an index on (a,b) then we can include the clause
415+
* b = 42 in the indexqual list generated for each of the OR subclauses.
416+
* Essentially, we are making an index-specific transformation from CNF to
417+
* DNF. (NOTE: when we do this, we end up with a slightly inefficient plan
418+
* because create_indexscan_plan is not very bright about figuring out which
419+
* restriction clauses are implied by the generated indexqual condition.
420+
* Currently we'll end up rechecking both the OR clause and the transferred
421+
* restriction clause as qpquals. FIXME someday.)
422+
*
423+
* Also, we apply expand_indexqual_conditions() to convert any special
424+
* matching opclauses to indexable operators.
411425
*
412426
* The passed-in clause is not changed.
427+
*----------
413428
*/
414429
List*
415430
extract_or_indexqual_conditions(RelOptInfo*rel,
416431
IndexOptInfo*index,
417432
Expr*orsubclause)
418433
{
419434
List*quals=NIL;
435+
int*indexkeys=index->indexkeys;
436+
Oid*classes=index->classlist;
420437

421-
if (and_clause((Node*)orsubclause))
438+
/*
439+
* Extract relevant indexclauses in indexkey order. This is essentially
440+
* just like group_clauses_by_indexkey() except that the input and
441+
* output are lists of bare clauses, not of RestrictInfo nodes.
442+
*/
443+
do
422444
{
445+
intcurIndxKey=indexkeys[0];
446+
OidcurClass=classes[0];
447+
List*clausegroup=NIL;
448+
List*item;
423449

424-
/*
425-
* Extract relevant sub-subclauses in indexkey order. This is
426-
* just like group_clauses_by_indexkey() except that the input and
427-
* output are lists of bare clauses, not of RestrictInfo nodes.
428-
*/
429-
int*indexkeys=index->indexkeys;
430-
Oid*classes=index->classlist;
450+
if (and_clause((Node*)orsubclause))
451+
{
452+
foreach(item,orsubclause->args)
453+
{
454+
Expr*subsubclause= (Expr*)lfirst(item);
431455

432-
do
456+
if (match_clause_to_indexkey(rel,index,
457+
curIndxKey,curClass,
458+
subsubclause, false))
459+
clausegroup=lappend(clausegroup,subsubclause);
460+
}
461+
}
462+
elseif (match_clause_to_indexkey(rel,index,
463+
curIndxKey,curClass,
464+
orsubclause, false))
433465
{
434-
intcurIndxKey=indexkeys[0];
435-
OidcurClass=classes[0];
436-
List*clausegroup=NIL;
437-
List*item;
466+
clausegroup=makeList1(orsubclause);
467+
}
438468

439-
foreach(item,orsubclause->args)
469+
/*
470+
* If we found no clauses for this indexkey in the OR subclause
471+
* itself, try looking in the rel's top-level restriction list.
472+
*/
473+
if (clausegroup==NIL)
474+
{
475+
foreach(item,rel->baserestrictinfo)
440476
{
477+
RestrictInfo*rinfo= (RestrictInfo*)lfirst(item);
478+
441479
if (match_clause_to_indexkey(rel,index,
442480
curIndxKey,curClass,
443-
lfirst(item), false))
444-
clausegroup=lappend(clausegroup,lfirst(item));
481+
rinfo->clause, false))
482+
clausegroup=lappend(clausegroup,rinfo->clause);
445483
}
484+
}
446485

447-
/*
448-
* If no clauses match this key, we're done; we don't want to
449-
* look at keys to its right.
450-
*/
451-
if (clausegroup==NIL)
452-
break;
486+
/*
487+
* If still no clauses match this key, we're done; we don't want to
488+
* look at keys to its right.
489+
*/
490+
if (clausegroup==NIL)
491+
break;
453492

454-
quals=nconc(quals,clausegroup);
493+
quals=nconc(quals,clausegroup);
455494

456-
indexkeys++;
457-
classes++;
458-
}while (!DoneMatchingIndexKeys(indexkeys,index));
495+
indexkeys++;
496+
classes++;
497+
}while (!DoneMatchingIndexKeys(indexkeys,index));
459498

460-
if (quals==NIL)
461-
elog(ERROR,"extract_or_indexqual_conditions: no matching clause");
462-
}
463-
else
464-
{
465-
/* we assume the caller passed a valid indexable qual */
466-
quals=makeList1(orsubclause);
467-
}
499+
if (quals==NIL)
500+
elog(ERROR,"extract_or_indexqual_conditions: no matching clause");
468501

469502
returnexpand_indexqual_conditions(quals);
470503
}

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

Lines changed: 11 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/orindxpath.c,v 1.43 2001/05/20 20:28:18 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/orindxpath.c,v 1.44 2001/06/05 17:13:52 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -38,34 +38,31 @@ static void best_or_subclause_index(Query *root, RelOptInfo *rel,
3838
* create_index_paths() must already have been called.
3939
*
4040
* 'rel' is the relation entry for which the paths are to be created
41-
* 'clauses' is the list of available restriction clause nodes
4241
*
4342
* Returns nothing, but adds paths to rel->pathlist via add_path().
4443
*/
4544
void
46-
create_or_index_paths(Query*root,
47-
RelOptInfo*rel,
48-
List*clauses)
45+
create_or_index_paths(Query*root,RelOptInfo*rel)
4946
{
50-
List*clist;
47+
List*rlist;
5148

52-
foreach(clist,clauses)
49+
foreach(rlist,rel->baserestrictinfo)
5350
{
54-
RestrictInfo*clausenode= (RestrictInfo*)lfirst(clist);
51+
RestrictInfo*restrictinfo= (RestrictInfo*)lfirst(rlist);
5552

5653
/*
5754
* Check to see if this clause is an 'or' clause, and, if so,
5855
* whether or not each of the subclauses within the 'or' clause
5956
* has been matched by an index. The information used was saved
6057
* by create_index_paths().
6158
*/
62-
if (restriction_is_or_clause(clausenode)&&
63-
clausenode->subclauseindices)
59+
if (restriction_is_or_clause(restrictinfo)&&
60+
restrictinfo->subclauseindices)
6461
{
6562
boolall_indexable= true;
6663
List*temp;
6764

68-
foreach(temp,clausenode->subclauseindices)
65+
foreach(temp,restrictinfo->subclauseindices)
6966
{
7067
if (lfirst(temp)==NIL)
7168
{
@@ -75,7 +72,6 @@ create_or_index_paths(Query *root,
7572
}
7673
if (all_indexable)
7774
{
78-
7975
/*
8076
* OK, build an IndexPath for this OR clause, using the
8177
* best available index for each subclause.
@@ -93,10 +89,7 @@ create_or_index_paths(Query *root,
9389
*/
9490
pathnode->path.pathkeys=NIL;
9591

96-
/*
97-
* We don't actually care what order the index scans in
98-
* ...
99-
*/
92+
/* We don't actually care what order the index scans in. */
10093
pathnode->indexscandir=NoMovementScanDirection;
10194

10295
/* This isn't a nestloop innerjoin, so: */
@@ -106,8 +99,8 @@ create_or_index_paths(Query *root,
10699

107100
best_or_subclause_indices(root,
108101
rel,
109-
clausenode->clause->args,
110-
clausenode->subclauseindices,
102+
restrictinfo->clause->args,
103+
restrictinfo->subclauseindices,
111104
pathnode);
112105

113106
add_path(rel, (Path*)pathnode);

‎src/include/optimizer/paths.h‎

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $Id: paths.h,v 1.53 2001/05/20 20:28:20 tgl Exp $
11+
* $Id: paths.h,v 1.54 2001/06/05 17:13:51 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -47,8 +47,7 @@ extern List *expand_indexqual_conditions(List *indexquals);
4747
* orindxpath.c
4848
* additional routines for indexable OR clauses
4949
*/
50-
externvoidcreate_or_index_paths(Query*root,RelOptInfo*rel,
51-
List*clauses);
50+
externvoidcreate_or_index_paths(Query*root,RelOptInfo*rel);
5251

5352
/*
5453
* tidpath.h

0 commit comments

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