NotificationsYou must be signed in to change notification settings
Fork6
Star31

Commita4fac4f

committed

Attempt to fix unstable Result Cache regression tests

force_parallel_mode = regress is causing a few more problems than Ithought. It seems that both the leader and the single worker cancontribute to the execution. I had mistakenly thought that only the workerprocess would do any work. Since it's not deterministic as to whichof the two processes will get a chance to work on the plan, it seems justbetter to disable force_parallel_mode for these tests. At least doingthis seems better than changing to EXPLAIN only rather than EXPLAINANALYZE.Additionally, I overlooked the fact that the number of executions of thesub-plan below a Result Cache will execute a varying number of timesdepending on cache eviction. 32-bit machines will use less memory andevict fewer tuples from the cache. That results in the subnode beingexecuted fewer times on 32-bit machines. Let's just blank out the numberof loops in each node.

1 parent2bda93f commita4fac4fCopy full SHA for a4fac4f

File tree

2 files changed

+31

-21

lines changed

src/test/regress
- expected
  - resultcache.out
- sql
  - resultcache.sql

2 files changed

+31

-21

lines changed

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

Lines changed: 26 additions & 21 deletions

Original file line number	Diff line number	Diff line change
`@@ -23,28 +23,32 @@ begin`
`23`	`23`	`ln := regexp_replace(ln, 'Evictions: \d+', 'Evictions: N');`
`24`	`24`	`ln := regexp_replace(ln, 'Memory Usage: \d+', 'Memory Usage: N');`
`25`	`25`	`ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N');`
	`26`	`+ln := regexp_replace(ln, 'loops=\d+', 'loops=N');`
`26`	`27`	`return next ln;`
`27`	`28`	`end loop;`
`28`	`29`	`end;`
`29`	`30`	`$$;`
`30`	`31`	`-- Ensure we get a result cache on the inner side of the nested loop`
`31`	`32`	`SET enable_hashjoin TO off;`
`32`	`33`	`SET enable_bitmapscan TO off;`
	`34`	`+-- force_parallel_mode = regress can cause some instability in EXPLAIN ANALYZE`
	`35`	`+-- output, so let's ensure that we turn it off.`
	`36`	`+SET force_parallel_mode TO off;`
`33`	`37`	`SELECT explain_resultcache('`
`34`	`38`	`SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1`
`35`	`39`	`INNER JOIN tenk1 t2 ON t1.unique1 = t2.twenty`
`36`	`40`	`WHERE t2.unique1 < 1000;', false);`
`37`		`- explain_resultcache`
`38`		`---------------------------------------------------------------------------------------------`
`39`		`- Aggregate (actual rows=1 loops=1)`
`40`		`- -> Nested Loop (actual rows=1000 loops=1)`
`41`		`- -> Seq Scan on tenk1 t2 (actual rows=1000 loops=1)`
	`41`	`+ explain_resultcache`
	`42`	`+-------------------------------------------------------------------------------------------`
	`43`	`+ Aggregate (actual rows=1 loops=N)`
	`44`	`+ -> Nested Loop (actual rows=1000 loops=N)`
	`45`	`+ -> Seq Scan on tenk1 t2 (actual rows=1000 loops=N)`
`42`	`46`	`Filter: (unique1 < 1000)`
`43`	`47`	`Rows Removed by Filter: 9000`
`44`		`- -> Result Cache (actual rows=1 loops=1000)`
	`48`	`+ -> Result Cache (actual rows=1 loops=N)`
`45`	`49`	`Cache Key: t2.twenty`
`46`	`50`	`Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB`
`47`		`- -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=20)`
	`51`	`+ -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)`
`48`	`52`	`Index Cond: (unique1 = t2.twenty)`
`49`	`53`	`Heap Fetches: N`
`50`	`54`	`(11 rows)`
`@@ -63,17 +67,17 @@ SELECT explain_resultcache('`
`63`	`67`	`SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,`
`64`	`68`	`LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2`
`65`	`69`	`WHERE t1.unique1 < 1000;', false);`
`66`		`- explain_resultcache`
`67`		`---------------------------------------------------------------------------------------------`
`68`		`- Aggregate (actual rows=1 loops=1)`
`69`		`- -> Nested Loop (actual rows=1000 loops=1)`
`70`		`- -> Seq Scan on tenk1 t1 (actual rows=1000 loops=1)`
	`70`	`+ explain_resultcache`
	`71`	`+-------------------------------------------------------------------------------------------`
	`72`	`+ Aggregate (actual rows=1 loops=N)`
	`73`	`+ -> Nested Loop (actual rows=1000 loops=N)`
	`74`	`+ -> Seq Scan on tenk1 t1 (actual rows=1000 loops=N)`
`71`	`75`	`Filter: (unique1 < 1000)`
`72`	`76`	`Rows Removed by Filter: 9000`
`73`		`- -> Result Cache (actual rows=1 loops=1000)`
	`77`	`+ -> Result Cache (actual rows=1 loops=N)`
`74`	`78`	`Cache Key: t1.twenty`
`75`	`79`	`Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB`
`76`		`- -> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=20)`
	`80`	`+ -> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N)`
`77`	`81`	`Index Cond: (unique1 = t1.twenty)`
`78`	`82`	`Heap Fetches: N`
`79`	`83`	`(11 rows)`
`@@ -97,23 +101,24 @@ SELECT explain_resultcache('`
`97`	`101`	`SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1`
`98`	`102`	`INNER JOIN tenk1 t2 ON t1.unique1 = t2.thousand`
`99`	`103`	`WHERE t2.unique1 < 1200;', true);`
`100`		`-explain_resultcache`
`101`		`-----------------------------------------------------------------------------------------------`
`102`		`- Aggregate (actual rows=1 loops=1)`
`103`		`- -> Nested Loop (actual rows=1200 loops=1)`
`104`		`- -> Seq Scan on tenk1 t2 (actual rows=1200 loops=1)`
	`104`	`+ explain_resultcache`
	`105`	`+-------------------------------------------------------------------------------------------`
	`106`	`+ Aggregate (actual rows=1 loops=N)`
	`107`	`+ -> Nested Loop (actual rows=1200 loops=N)`
	`108`	`+ -> Seq Scan on tenk1 t2 (actual rows=1200 loops=N)`
`105`	`109`	`Filter: (unique1 < 1200)`
`106`	`110`	`Rows Removed by Filter: 8800`
`107`		`- -> Result Cache (actual rows=1 loops=1200)`
	`111`	`+ -> Result Cache (actual rows=1 loops=N)`
`108`	`112`	`Cache Key: t2.thousand`
`109`	`113`	`Hits: N Misses: N Evictions: N Overflows: 0 Memory Usage: NkB`
`110`		`- -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=1028)`
	`114`	`+ -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)`
`111`	`115`	`Index Cond: (unique1 = t2.thousand)`
`112`	`116`	`Heap Fetches: N`
`113`	`117`	`(11 rows)`
`114`	`118`
`115`	`119`	`RESET enable_mergejoin;`
`116`	`120`	`RESET work_mem;`
	`121`	`+RESET force_parallel_mode;`
`117`	`122`	`RESET enable_bitmapscan;`
`118`	`123`	`RESET enable_hashjoin;`
`119`	`124`	`-- Test parallel plans with Result Cache.`

`‎src/test/regress/sql/resultcache.sql`

Lines changed: 5 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -24,6 +24,7 @@ begin`
`24`	`24`	`ln := regexp_replace(ln,'Evictions:\d+','Evictions: N');`
`25`	`25`	`ln := regexp_replace(ln,'Memory Usage:\d+','Memory Usage: N');`
`26`	`26`	`ln := regexp_replace(ln,'Heap Fetches:\d+','Heap Fetches: N');`
	`27`	`+ln := regexp_replace(ln,'loops=\d+','loops=N');`
`27`	`28`	`return next ln;`
`28`	`29`	`end loop;`
`29`	`30`	`end;`
`@@ -32,6 +33,9 @@ $$;`
`32`	`33`	`-- Ensure we get a result cache on the inner side of the nested loop`
`33`	`34`	`SET enable_hashjoin TO off;`
`34`	`35`	`SET enable_bitmapscan TO off;`
	`36`	`+-- force_parallel_mode = regress can cause some instability in EXPLAIN ANALYZE`
	`37`	`+-- output, so let's ensure that we turn it off.`
	`38`	`+SET force_parallel_mode TO off;`
`35`	`39`	`SELECT explain_resultcache('`
`36`	`40`	`SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1`
`37`	`41`	`INNER JOIN tenk1 t2 ON t1.unique1 = t2.twenty`
`@@ -65,6 +69,7 @@ INNER JOIN tenk1 t2 ON t1.unique1 = t2.thousand`
`65`	`69`	`WHERE t2.unique1 < 1200;', true);`
`66`	`70`	`RESET enable_mergejoin;`
`67`	`71`	`RESET work_mem;`
	`72`	`+RESET force_parallel_mode;`
`68`	`73`	`RESET enable_bitmapscan;`
`69`	`74`	`RESET enable_hashjoin;`
`70`	`75`

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!

Commita4fac4f

File tree

2 files changed

2 files changed

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

`‎src/test/regress/sql/resultcache.sql`

0 commit comments