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

Commit98359c3

Browse files
committed
In the recent changes to make the planner account better for cache
effects in a nestloop inner indexscan, I had only dealt with plain indexscans and the index portion of bitmap scans. But there will be cachebenefits for the heap accesses of bitmap scans too, so fixcost_bitmap_heap_scan() to account for that.
1 parentb0dc1fb commit98359c3

File tree

5 files changed

+62
-32
lines changed

5 files changed

+62
-32
lines changed

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

Lines changed: 34 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -54,7 +54,7 @@
5454
* Portions Copyright (c) 1994, Regents of the University of California
5555
*
5656
* IDENTIFICATION
57-
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.162 2006/07/14 14:52:20 momjian Exp $
57+
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.163 2006/07/22 15:41:55 tgl Exp $
5858
*
5959
*-------------------------------------------------------------------------
6060
*/
@@ -437,13 +437,17 @@ index_pages_fetched(double tuples_fetched, BlockNumber pages,
437437
*
438438
* 'baserel' is the relation to be scanned
439439
* 'bitmapqual' is a tree of IndexPaths, BitmapAndPaths, and BitmapOrPaths
440+
* 'outer_rel' is the outer relation when we are considering using the bitmap
441+
*scan as the inside of a nestloop join (hence, some of the indexQuals
442+
*are join clauses, and we should expect repeated scans of the table);
443+
*NULL for a plain bitmap scan
440444
*
441-
* Note:we take no explicit notice here of whetherthis is a join inner path.
442-
*If it is, the component IndexPathsshould have been costed accordingly.
445+
* Note:ifthis is a join inner path, the component IndexPaths in bitmapqual
446+
* should have been costed accordingly.
443447
*/
444448
void
445449
cost_bitmap_heap_scan(Path*path,PlannerInfo*root,RelOptInfo*baserel,
446-
Path*bitmapqual)
450+
Path*bitmapqual,RelOptInfo*outer_rel)
447451
{
448452
Coststartup_cost=0;
449453
Costrun_cost=0;
@@ -472,14 +476,36 @@ cost_bitmap_heap_scan(Path *path, PlannerInfo *root, RelOptInfo *baserel,
472476
startup_cost+=indexTotalCost;
473477

474478
/*
475-
* The number of heap pages that need to be fetched is the same as the
476-
* Mackert and Lohman formula for the case T <= b (ie, no re-reads
477-
* needed).
479+
* Estimate number of main-table pages fetched.
478480
*/
479481
tuples_fetched=clamp_row_est(indexSelectivity*baserel->tuples);
480482

481483
T= (baserel->pages>1) ? (double)baserel->pages :1.0;
482-
pages_fetched= (2.0*T*tuples_fetched) / (2.0*T+tuples_fetched);
484+
485+
if (outer_rel!=NULL&&outer_rel->rows>1)
486+
{
487+
/*
488+
* For repeated bitmap scans, scale up the number of tuples fetched
489+
* in the Mackert and Lohman formula by the number of scans, so
490+
* that we estimate the number of pages fetched by all the scans.
491+
* Then pro-rate for one scan.
492+
*/
493+
doublenum_scans=outer_rel->rows;
494+
495+
pages_fetched=index_pages_fetched(tuples_fetched*num_scans,
496+
baserel->pages,
497+
0/* XXX total index size? */);
498+
pages_fetched /=num_scans;
499+
}
500+
else
501+
{
502+
/*
503+
* For a single scan, the number of heap pages that need to be fetched
504+
* is the same as the Mackert and Lohman formula for the case T <= b
505+
* (ie, no re-reads needed).
506+
*/
507+
pages_fetched= (2.0*T*tuples_fetched) / (2.0*T+tuples_fetched);
508+
}
483509
if (pages_fetched >=T)
484510
pages_fetched=T;
485511
else

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

Lines changed: 17 additions & 13 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.210 2006/07/13 17:47:01 momjian Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.211 2006/07/22 15:41:55 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -53,9 +53,11 @@ static List *find_usable_indexes(PlannerInfo *root, RelOptInfo *rel,
5353
staticList*find_saop_paths(PlannerInfo*root,RelOptInfo*rel,
5454
List*clauses,List*outer_clauses,
5555
boolistoplevel,RelOptInfo*outer_rel);
56-
staticPath*choose_bitmap_and(PlannerInfo*root,RelOptInfo*rel,List*paths);
56+
staticPath*choose_bitmap_and(PlannerInfo*root,RelOptInfo*rel,
57+
List*paths,RelOptInfo*outer_rel);
5758
staticintbitmap_path_comparator(constvoid*a,constvoid*b);
58-
staticCostbitmap_and_cost_est(PlannerInfo*root,RelOptInfo*rel,List*paths);
59+
staticCostbitmap_and_cost_est(PlannerInfo*root,RelOptInfo*rel,
60+
List*paths,RelOptInfo*outer_rel);
5961
staticList*pull_indexpath_quals(Path*bitmapqual);
6062
staticboollists_intersect_ptr(List*list1,List*list2);
6163
staticboolmatch_clause_to_indexcol(IndexOptInfo*index,
@@ -210,8 +212,8 @@ create_index_paths(PlannerInfo *root, RelOptInfo *rel)
210212
Path*bitmapqual;
211213
BitmapHeapPath*bpath;
212214

213-
bitmapqual=choose_bitmap_and(root,rel,bitindexpaths);
214-
bpath=create_bitmap_heap_path(root,rel,bitmapqual,false);
215+
bitmapqual=choose_bitmap_and(root,rel,bitindexpaths,NULL);
216+
bpath=create_bitmap_heap_path(root,rel,bitmapqual,NULL);
215217
add_path(rel, (Path*)bpath);
216218
}
217219
}
@@ -536,7 +538,7 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
536538
* OK, pick the most promising AND combination, and add it to
537539
* pathlist.
538540
*/
539-
bitmapqual=choose_bitmap_and(root,rel,indlist);
541+
bitmapqual=choose_bitmap_and(root,rel,indlist,outer_rel);
540542
pathlist=lappend(pathlist,bitmapqual);
541543
}
542544

@@ -567,7 +569,8 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
567569
* combining multiple inputs.
568570
*/
569571
staticPath*
570-
choose_bitmap_and(PlannerInfo*root,RelOptInfo*rel,List*paths)
572+
choose_bitmap_and(PlannerInfo*root,RelOptInfo*rel,
573+
List*paths,RelOptInfo*outer_rel)
571574
{
572575
intnpaths=list_length(paths);
573576
Path**patharray;
@@ -629,7 +632,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
629632
qsort(patharray,npaths,sizeof(Path*),bitmap_path_comparator);
630633

631634
paths=list_make1(patharray[0]);
632-
costsofar=bitmap_and_cost_est(root,rel,paths);
635+
costsofar=bitmap_and_cost_est(root,rel,paths,outer_rel);
633636
qualsofar=pull_indexpath_quals(patharray[0]);
634637
lastcell=list_head(paths);/* for quick deletions */
635638

@@ -644,7 +647,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
644647
continue;/* consider it redundant */
645648
/* tentatively add newpath to paths, so we can estimate cost */
646649
paths=lappend(paths,newpath);
647-
newcost=bitmap_and_cost_est(root,rel,paths);
650+
newcost=bitmap_and_cost_est(root,rel,paths,outer_rel);
648651
if (newcost<costsofar)
649652
{
650653
/* keep newpath in paths, update subsidiary variables */
@@ -702,7 +705,8 @@ bitmap_path_comparator(const void *a, const void *b)
702705
* inputs.
703706
*/
704707
staticCost
705-
bitmap_and_cost_est(PlannerInfo*root,RelOptInfo*rel,List*paths)
708+
bitmap_and_cost_est(PlannerInfo*root,RelOptInfo*rel,
709+
List*paths,RelOptInfo*outer_rel)
706710
{
707711
BitmapAndPathapath;
708712
Pathbpath;
@@ -714,7 +718,7 @@ bitmap_and_cost_est(PlannerInfo *root, RelOptInfo *rel, List *paths)
714718
cost_bitmap_and_node(&apath,root);
715719

716720
/* Now we can do cost_bitmap_heap_scan */
717-
cost_bitmap_heap_scan(&bpath,root,rel, (Path*)&apath);
721+
cost_bitmap_heap_scan(&bpath,root,rel, (Path*)&apath,outer_rel);
718722

719723
returnbpath.total_cost;
720724
}
@@ -1486,8 +1490,8 @@ best_inner_indexscan(PlannerInfo *root, RelOptInfo *rel,
14861490
Path*bitmapqual;
14871491
BitmapHeapPath*bpath;
14881492

1489-
bitmapqual=choose_bitmap_and(root,rel,bitindexpaths);
1490-
bpath=create_bitmap_heap_path(root,rel,bitmapqual,true);
1493+
bitmapqual=choose_bitmap_and(root,rel,bitindexpaths,outer_rel);
1494+
bpath=create_bitmap_heap_path(root,rel,bitmapqual,outer_rel);
14911495
indexpaths=lappend(indexpaths,bpath);
14921496
}
14931497

‎src/backend/optimizer/util/pathnode.c‎

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/util/pathnode.c,v 1.130 2006/07/14 14:52:21 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/util/pathnode.c,v 1.131 2006/07/22 15:41:55 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -513,14 +513,14 @@ create_index_path(PlannerInfo *root,
513513
*
514514
* 'bitmapqual' is a tree of IndexPath, BitmapAndPath, and BitmapOrPath nodes.
515515
*
516-
* If this is a join inner indexscan path,the component IndexPaths should
517-
*have been costed accordingly, and TRUEshouldbe passed for isjoininner.
516+
* If this is a join inner indexscan path,'outer_rel' is the outer relation,
517+
*and all the component IndexPathsshouldhave been costed accordingly.
518518
*/
519519
BitmapHeapPath*
520520
create_bitmap_heap_path(PlannerInfo*root,
521521
RelOptInfo*rel,
522522
Path*bitmapqual,
523-
boolisjoininner)
523+
RelOptInfo*outer_rel)
524524
{
525525
BitmapHeapPath*pathnode=makeNode(BitmapHeapPath);
526526

@@ -529,9 +529,9 @@ create_bitmap_heap_path(PlannerInfo *root,
529529
pathnode->path.pathkeys=NIL;/* always unordered */
530530

531531
pathnode->bitmapqual=bitmapqual;
532-
pathnode->isjoininner=isjoininner;
532+
pathnode->isjoininner=(outer_rel!=NULL);
533533

534-
if (isjoininner)
534+
if (pathnode->isjoininner)
535535
{
536536
/*
537537
* We must compute the estimated number of output rows for the
@@ -560,7 +560,7 @@ create_bitmap_heap_path(PlannerInfo *root,
560560
pathnode->rows=rel->rows;
561561
}
562562

563-
cost_bitmap_heap_scan(&pathnode->path,root,rel,bitmapqual);
563+
cost_bitmap_heap_scan(&pathnode->path,root,rel,bitmapqual,outer_rel);
564564

565565
returnpathnode;
566566
}

‎src/include/optimizer/cost.h‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/optimizer/cost.h,v 1.76 2006/06/06 17:59:58 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/optimizer/cost.h,v 1.77 2006/07/22 15:41:56 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -61,7 +61,7 @@ extern void cost_seqscan(Path *path, PlannerInfo *root, RelOptInfo *baserel);
6161
externvoidcost_index(IndexPath*path,PlannerInfo*root,IndexOptInfo*index,
6262
List*indexQuals,RelOptInfo*outer_rel);
6363
externvoidcost_bitmap_heap_scan(Path*path,PlannerInfo*root,RelOptInfo*baserel,
64-
Path*bitmapqual);
64+
Path*bitmapqual,RelOptInfo*outer_rel);
6565
externvoidcost_bitmap_and_node(BitmapAndPath*path,PlannerInfo*root);
6666
externvoidcost_bitmap_or_node(BitmapOrPath*path,PlannerInfo*root);
6767
externvoidcost_bitmap_tree_node(Path*path,Cost*cost,Selectivity*selec);

‎src/include/optimizer/pathnode.h‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/optimizer/pathnode.h,v 1.69 2006/07/01 18:38:33 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/optimizer/pathnode.h,v 1.70 2006/07/22 15:41:56 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -37,7 +37,7 @@ extern IndexPath *create_index_path(PlannerInfo *root,
3737
externBitmapHeapPath*create_bitmap_heap_path(PlannerInfo*root,
3838
RelOptInfo*rel,
3939
Path*bitmapqual,
40-
boolisjoininner);
40+
RelOptInfo*outer_rel);
4141
externBitmapAndPath*create_bitmap_and_path(PlannerInfo*root,
4242
RelOptInfo*rel,
4343
List*bitmapquals);

0 commit comments

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