NotificationsYou must be signed in to change notification settings
Fork6
Star31

Commit1e731ed

committed

Fix incorrect row estimates used for Memoize costing

In order to estimate the cache hit ratio of a Memoize node, one of theinputs we require is the estimated number of times the Memoize node willbe rescanned. The higher this number, the large the cache hit ratio islikely to become. Unfortunately, the value being passed as the number of"calls" to the Memoize was incorrectly using the Nested Loop'souter_path->parent->rows instead of outer_path->rows. This failed toaccount for the fact that the outer_path might be parameterized by someupper-level Nested Loop.This problem could lead to Memoize plans appearing more favorable thanthey might actually be. It could also lead to extended executor startuptimes when work_mem values were large due to the planner setting overlylarge MemoizePath->est_entries resulting in the Memoize hash table beinginitially made much larger than might be required.Fix this simply by passing outer_path->rows rather thanouter_path->parent->rows. Also, adjust the expected regression testoutput for a plan change.Reported-by: Pavel StehuleAuthor: David RowleyDiscussion:https://postgr.es/m/CAFj8pRAMp%3DQsMi6sPQJ4W3hczoFJRvyXHJV3AZAZaMyTVM312Q%40mail.gmail.comBackpatch-through: 14, where Memoize was introduced

1 parent5bcc4d0 commit1e731edCopy full SHA for 1e731ed

File tree

2 files changed

+11

-17

lines changed

src
- backend/optimizer/path
  - joinpath.c
- test/regress/expected
  - join.out

2 files changed

+11

-17

lines changed

`‎src/backend/optimizer/path/joinpath.c`

Lines changed: 1 addition & 1 deletion

Original file line number	Diff line number	Diff line change
`@@ -610,7 +610,7 @@ get_memoize_path(PlannerInfo root, RelOptInfo innerrel,`
`610`	`610`	`hash_operators,`
`611`	`611`	`extra->inner_unique,`
`612`	`612`	`binary_mode,`
`613`		`-outer_path->parent->rows);`
	`613`	`+outer_path->rows);`
`614`	`614`	`}`
`615`	`615`
`616`	`616`	`returnNULL;`

`‎src/test/regress/expected/join.out`

Lines changed: 10 additions & 16 deletions

Original file line number	Diff line number	Diff line change
`@@ -3673,8 +3673,8 @@ select * from tenk1 t1 left join`
`3673`	`3673`	`(tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)`
`3674`	`3674`	`on t1.hundred = t2.hundred and t1.ten = t3.ten`
`3675`	`3675`	`where t1.unique1 = 1;`
`3676`		`-QUERY PLAN`
`3677`		`---------------------------------------------------------------`
	`3676`	`+ QUERY PLAN`
	`3677`	`+--------------------------------------------------------`
`3678`	`3678`	`Nested Loop Left Join`
`3679`	`3679`	`-> Index Scan using tenk1_unique1 on tenk1 t1`
`3680`	`3680`	`Index Cond: (unique1 = 1)`
`@@ -3684,20 +3684,17 @@ where t1.unique1 = 1;`
`3684`	`3684`	`Recheck Cond: (t1.hundred = hundred)`
`3685`	`3685`	`-> Bitmap Index Scan on tenk1_hundred`
`3686`	`3686`	`Index Cond: (hundred = t1.hundred)`
`3687`		`- -> Memoize`
`3688`		`- Cache Key: t2.thousand`
`3689`		`- Cache Mode: logical`
`3690`		`- -> Index Scan using tenk1_unique2 on tenk1 t3`
`3691`		`- Index Cond: (unique2 = t2.thousand)`
`3692`		`-(14 rows)`
	`3687`	`+ -> Index Scan using tenk1_unique2 on tenk1 t3`
	`3688`	`+ Index Cond: (unique2 = t2.thousand)`
	`3689`	`+(11 rows)`
`3693`	`3690`
`3694`	`3691`	`explain (costs off)`
`3695`	`3692`	`select * from tenk1 t1 left join`
`3696`	`3693`	`(tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)`
`3697`	`3694`	`on t1.hundred = t2.hundred and t1.ten + t2.ten = t3.ten`
`3698`	`3695`	`where t1.unique1 = 1;`
`3699`		`-QUERY PLAN`
`3700`		`---------------------------------------------------------------`
	`3696`	`+ QUERY PLAN`
	`3697`	`+--------------------------------------------------------`
`3701`	`3698`	`Nested Loop Left Join`
`3702`	`3699`	`-> Index Scan using tenk1_unique1 on tenk1 t1`
`3703`	`3700`	`Index Cond: (unique1 = 1)`
`@@ -3707,12 +3704,9 @@ where t1.unique1 = 1;`
`3707`	`3704`	`Recheck Cond: (t1.hundred = hundred)`
`3708`	`3705`	`-> Bitmap Index Scan on tenk1_hundred`
`3709`	`3706`	`Index Cond: (hundred = t1.hundred)`
`3710`		`- -> Memoize`
`3711`		`- Cache Key: t2.thousand`
`3712`		`- Cache Mode: logical`
`3713`		`- -> Index Scan using tenk1_unique2 on tenk1 t3`
`3714`		`- Index Cond: (unique2 = t2.thousand)`
`3715`		`-(14 rows)`
	`3707`	`+ -> Index Scan using tenk1_unique2 on tenk1 t3`
	`3708`	`+ Index Cond: (unique2 = t2.thousand)`
	`3709`	`+(11 rows)`
`3716`	`3710`
`3717`	`3711`	`explain (costs off)`
`3718`	`3712`	`select count(*) from`

0 commit comments

Comments

(0)

Movatterモバイル変換

Navigation Menu

Search code, repositories, users, issues, pull requests...

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Commit1e731ed

File tree

2 files changed

2 files changed

`‎src/backend/optimizer/path/joinpath.c`

`‎src/test/regress/expected/join.out`

0 commit comments