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

Commita81e281

Browse files
committed
Revert my best_inner_indexscan patch of yesterday, which turns out to have
had a bad side-effect: it stopped finding plans that involved BitmapAndcombinations of indexscans using both join and non-join conditions. Instead,make choose_bitmap_and more aggressive about detecting redundancies betweenBitmapOr subplans.
1 parent83843a4 commita81e281

File tree

1 file changed

+100
-64
lines changed

1 file changed

+100
-64
lines changed

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

Lines changed: 100 additions & 64 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.203 2006/04/08 21:32:17 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.204 2006/04/09 18:18:41 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -53,6 +53,7 @@ static List *find_usable_indexes(PlannerInfo *root, RelOptInfo *rel,
5353
staticPath*choose_bitmap_and(PlannerInfo*root,RelOptInfo*rel,List*paths);
5454
staticintbitmap_path_comparator(constvoid*a,constvoid*b);
5555
staticCostbitmap_and_cost_est(PlannerInfo*root,RelOptInfo*rel,List*paths);
56+
staticList*pull_indexpath_quals(Path*bitmapqual);
5657
staticboollists_intersect_ptr(List*list1,List*list2);
5758
staticboolmatch_clause_to_indexcol(IndexOptInfo*index,
5859
intindexcol,Oidopclass,
@@ -253,10 +254,6 @@ find_usable_indexes(PlannerInfo *root, RelOptInfo *rel,
253254
List*all_clauses=NIL;/* not computed till needed */
254255
ListCell*ilist;
255256

256-
/* quick exit if no available clauses */
257-
if (clauses==NIL)
258-
returnNIL;
259-
260257
foreach(ilist,rel->indexlist)
261258
{
262259
IndexOptInfo*index= (IndexOptInfo*)lfirst(ilist);
@@ -581,9 +578,10 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
581578
* lower estimated cost.
582579
*
583580
* We also make some effort to detect directly redundant input paths, as
584-
* can happen if there are multiple possibly usable indexes. For this we
585-
* look only at plain IndexPath and single-element BitmapOrPath inputs
586-
* (the latter can arise in the presence of ScalarArrayOpExpr quals). We
581+
* can happen if there are multiple possibly usable indexes. (Another
582+
* way it can happen is that best_inner_indexscan will find the same OR
583+
* join clauses that create_or_index_quals has pulled OR restriction
584+
* clauses out of, and then both versions show up as duplicate paths.) We
587585
* consider an index redundant if any of its index conditions were already
588586
* used by earlier indexes. (We could use predicate_implied_by to have a
589587
* more intelligent, but much more expensive, check --- but in most cases
@@ -620,53 +618,31 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
620618

621619
paths=list_make1(patharray[0]);
622620
costsofar=bitmap_and_cost_est(root,rel,paths);
623-
qualsofar=NIL;
624-
if (IsA(patharray[0],IndexPath))
625-
qualsofar=list_copy(((IndexPath*)patharray[0])->indexclauses);
626-
elseif (IsA(patharray[0],BitmapOrPath))
627-
{
628-
List*orquals= ((BitmapOrPath*)patharray[0])->bitmapquals;
629-
630-
if (list_length(orquals)==1&&
631-
IsA(linitial(orquals),IndexPath))
632-
qualsofar=list_copy(((IndexPath*)linitial(orquals))->indexclauses);
633-
}
621+
qualsofar=pull_indexpath_quals(patharray[0]);
634622
lastcell=list_head(paths);/* for quick deletions */
635623

636624
for (i=1;i<npaths;i++)
637625
{
638626
Path*newpath=patharray[i];
639-
List*newqual=NIL;
627+
List*newqual;
640628
Costnewcost;
641629

642-
if (IsA(newpath,IndexPath))
643-
{
644-
newqual= ((IndexPath*)newpath)->indexclauses;
645-
if (lists_intersect_ptr(newqual,qualsofar))
646-
continue;/* redundant */
647-
}
648-
elseif (IsA(newpath,BitmapOrPath))
649-
{
650-
List*orquals= ((BitmapOrPath*)newpath)->bitmapquals;
651-
652-
if (list_length(orquals)==1&&
653-
IsA(linitial(orquals),IndexPath))
654-
newqual= ((IndexPath*)linitial(orquals))->indexclauses;
655-
if (lists_intersect_ptr(newqual,qualsofar))
656-
continue;/* redundant */
657-
}
658-
630+
newqual=pull_indexpath_quals(newpath);
631+
if (lists_intersect_ptr(newqual,qualsofar))
632+
continue;/* consider it redundant */
633+
/* tentatively add newpath to paths, so we can estimate cost */
659634
paths=lappend(paths,newpath);
660635
newcost=bitmap_and_cost_est(root,rel,paths);
661636
if (newcost<costsofar)
662637
{
638+
/* keep newpath in paths, update subsidiary variables */
663639
costsofar=newcost;
664-
if (newqual)
665-
qualsofar=list_concat(qualsofar,list_copy(newqual));
640+
qualsofar=list_concat(qualsofar,newqual);
666641
lastcell=lnext(lastcell);
667642
}
668643
else
669644
{
645+
/* reject newpath, remove it from paths list */
670646
paths=list_delete_cell(paths,lnext(lastcell),lastcell);
671647
}
672648
Assert(lnext(lastcell)==NULL);
@@ -733,6 +709,62 @@ bitmap_and_cost_est(PlannerInfo *root, RelOptInfo *rel, List *paths)
733709
returnbpath.total_cost;
734710
}
735711

712+
/*
713+
* pull_indexpath_quals
714+
*
715+
* Given the Path structure for a plain or bitmap indexscan, extract a
716+
* list of RestrictInfo nodes for all the indexquals used in the Path.
717+
*
718+
* This is sort of a simplified version of make_restrictinfo_from_bitmapqual;
719+
* here, we are not trying to produce an accurate representation of the AND/OR
720+
* semantics of the Path, but just find out all the base conditions used.
721+
*
722+
* The result list contains pointers to the RestrictInfos used in the Path,
723+
* but all the list cells are freshly built, so it's safe to destructively
724+
* modify the list (eg, by concat'ing it with other lists).
725+
*/
726+
staticList*
727+
pull_indexpath_quals(Path*bitmapqual)
728+
{
729+
List*result=NIL;
730+
ListCell*l;
731+
732+
if (IsA(bitmapqual,BitmapAndPath))
733+
{
734+
BitmapAndPath*apath= (BitmapAndPath*)bitmapqual;
735+
736+
foreach(l,apath->bitmapquals)
737+
{
738+
List*sublist;
739+
740+
sublist=pull_indexpath_quals((Path*)lfirst(l));
741+
result=list_concat(result,sublist);
742+
}
743+
}
744+
elseif (IsA(bitmapqual,BitmapOrPath))
745+
{
746+
BitmapOrPath*opath= (BitmapOrPath*)bitmapqual;
747+
748+
foreach(l,opath->bitmapquals)
749+
{
750+
List*sublist;
751+
752+
sublist=pull_indexpath_quals((Path*)lfirst(l));
753+
result=list_concat(result,sublist);
754+
}
755+
}
756+
elseif (IsA(bitmapqual,IndexPath))
757+
{
758+
IndexPath*ipath= (IndexPath*)bitmapqual;
759+
760+
result=list_copy(ipath->indexclauses);
761+
}
762+
else
763+
elog(ERROR,"unrecognized node type: %d",nodeTag(bitmapqual));
764+
765+
returnresult;
766+
}
767+
736768

737769
/*
738770
* lists_intersect_ptr
@@ -1374,20 +1406,24 @@ best_inner_indexscan(PlannerInfo *root, RelOptInfo *rel,
13741406
}
13751407

13761408
/*
1377-
* Find all the relevant join clauses.
1409+
* Find all the relevant restriction and join clauses.
1410+
*
1411+
* Note: because we include restriction clauses, we will find indexscans
1412+
* that could be plain indexscans, ie, they don't require the join context
1413+
* at all. This may seem redundant, but we need to include those scans in
1414+
* the input given to choose_bitmap_and() to be sure we find optimal AND
1415+
* combinations of join and non-join scans. The worst case is that we
1416+
* might return a "best inner indexscan" that's really just a plain
1417+
* indexscan, causing some redundant effort in joinpath.c.
13781418
*/
13791419
clause_list=find_clauses_for_join(root,rel,outer_relids,isouterjoin);
13801420

13811421
/*
13821422
* Find all the index paths that are usable for this join, except for
1383-
* stuff involving OR and ScalarArrayOpExpr clauses. We can use both
1384-
* join and restriction clauses as indexquals, but we insist the path
1385-
* use at least one join clause (else it'd not be an "inner indexscan"
1386-
* but a plain indexscan, and those have already been considered).
1423+
* stuff involving OR and ScalarArrayOpExpr clauses.
13871424
*/
13881425
indexpaths=find_usable_indexes(root,rel,
1389-
clause_list,
1390-
rel->baserestrictinfo,
1426+
clause_list,NIL,
13911427
false, true,
13921428
outer_relids,
13931429
SAOP_FORBID);
@@ -1397,8 +1433,7 @@ best_inner_indexscan(PlannerInfo *root, RelOptInfo *rel,
13971433
* clauses present in the clause list.
13981434
*/
13991435
bitindexpaths=generate_bitmap_or_paths(root,rel,
1400-
clause_list,
1401-
rel->baserestrictinfo,
1436+
clause_list,NIL,
14021437
true,
14031438
outer_relids);
14041439

@@ -1448,12 +1483,13 @@ best_inner_indexscan(PlannerInfo *root, RelOptInfo *rel,
14481483

14491484
/*
14501485
* find_clauses_for_join
1451-
* Generate a list ofjoinclauses that are potentially useful for
1486+
* Generate a list of clauses that are potentially useful for
14521487
* scanning rel as the inner side of a nestloop join.
14531488
*
1454-
* Any joinclause that uses only otherrels in the specified outer_relids is
1455-
* fair game. Note that restriction clauses on rel can also be used in
1456-
* forming index conditions, but we do not include those here.
1489+
* We consider both join and restriction clauses. Any joinclause that uses
1490+
* only otherrels in the specified outer_relids is fair game. But there must
1491+
* be at least one such joinclause in the final list, otherwise we return NIL
1492+
* indicating that there isn't any potential win here.
14571493
*/
14581494
staticList*
14591495
find_clauses_for_join(PlannerInfo*root,RelOptInfo*rel,
@@ -1481,28 +1517,28 @@ find_clauses_for_join(PlannerInfo *root, RelOptInfo *rel,
14811517

14821518
bms_free(join_relids);
14831519

1484-
/*quick exitif no join clause was matched */
1520+
/* if no join clause was matched then forget it, per comments above */
14851521
if (clause_list==NIL)
14861522
returnNIL;
14871523

1524+
/*
1525+
* We can also use any plain restriction clauses for the rel. We put
1526+
* these at the front of the clause list for the convenience of
1527+
* remove_redundant_join_clauses, which can never remove non-join clauses
1528+
* and hence won't be able to get rid of a non-join clause if it appears
1529+
* after a join clause it is redundant with.
1530+
*/
1531+
clause_list=list_concat(list_copy(rel->baserestrictinfo),clause_list);
1532+
14881533
/*
14891534
* We may now have clauses that are known redundant. Get rid of 'em.
14901535
*/
14911536
if (list_length(clause_list)>1)
1537+
{
14921538
clause_list=remove_redundant_join_clauses(root,
14931539
clause_list,
14941540
isouterjoin);
1495-
1496-
/*
1497-
* We might have found join clauses that are known redundant with
1498-
* restriction clauses on rel (due to conclusions drawn by implied
1499-
* equality deduction; without that, this would obviously never happen).
1500-
* Get rid of them too.
1501-
*/
1502-
if (rel->baserestrictinfo!=NIL)
1503-
clause_list=select_nonredundant_join_clauses(root,clause_list,
1504-
rel->baserestrictinfo,
1505-
isouterjoin);
1541+
}
15061542

15071543
returnclause_list;
15081544
}

0 commit comments

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