NotificationsYou must be signed in to change notification settings
Fork5
Star27

Commitd6d5f67

committed

Modify create_index regression test to avoid intermittent failures.

We have been seeing intermittent buildfarm failures due to a querysometimes not using an index-only scan plan, because a backgroundauto-ANALYZE prevented the table's all-visible bits from being setimmediately, thereby causing the estimated cost of an index-only scanto go up considerably. Adjust the test case so that a bitmap index scan ispreferred instead, which serves equally well for the purpose the test caseis actually meant for. (Of course, it would be better to eliminate theinterference from auto-ANALYZE, but I see no low-risk way to do that,so any such fix will have to be left for 9.3 or later.)

1 parent9fa82c9 commitd6d5f67Copy full SHA for d6d5f67

File tree

2 files changed

+15

-13

lines changed

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

2 files changed

+15

-13

lines changed

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

Lines changed: 12 additions & 10 deletions

Original file line number	Diff line number	Diff line change
`@@ -2605,21 +2605,23 @@ DROP TABLE onek_with_null;`
`2605`	`2605`	`CREATE TABLE dupindexcols AS`
`2606`	`2606`	`SELECT unique1 as id, stringu2::text as f1 FROM tenk1;`
`2607`	`2607`	`CREATE INDEX dupindexcols_i ON dupindexcols (f1, id, f1 text_pattern_ops);`
`2608`		`-VACUUMANALYZE dupindexcols;`
	`2608`	`+ANALYZE dupindexcols;`
`2609`	`2609`	`EXPLAIN (COSTS OFF)`
`2610`	`2610`	`SELECT count(*) FROM dupindexcols`
`2611`		`- WHERE f1 > 'MA' and id < 1000 and f1 ~<~ 'YX';`
`2612`		`- QUERY PLAN`
`2613`		`----------------------------------------------------------------------------------`
`2614`		`- Aggregate`
`2615`		`- -> Index Only Scan using dupindexcols_i on dupindexcols`
`2616`		`- Index Cond: ((f1 > 'MA'::text) AND (id < 1000) AND (f1 ~<~ 'YX'::text))`
`2617`		`-(3 rows)`
	`2611`	`+ WHERE f1 > 'WA' and id < 1000 and f1 ~<~ 'YX';`
	`2612`	`+ QUERY PLAN`
	`2613`	`+---------------------------------------------------------------------------------------`
	`2614`	`+ Aggregate`
	`2615`	`+ -> Bitmap Heap Scan on dupindexcols`
	`2616`	`+ Recheck Cond: ((f1 > 'WA'::text) AND (id < 1000) AND (f1 ~<~ 'YX'::text))`
	`2617`	`+ -> Bitmap Index Scan on dupindexcols_i`
	`2618`	`+ Index Cond: ((f1 > 'WA'::text) AND (id < 1000) AND (f1 ~<~ 'YX'::text))`
	`2619`	`+(5 rows)`
`2618`	`2620`
`2619`	`2621`	`SELECT count(*) FROM dupindexcols`
`2620`		`- WHERE f1 > 'MA' and id < 1000 and f1 ~<~ 'YX';`
	`2622`	`+ WHERE f1 > 'WA' and id < 1000 and f1 ~<~ 'YX';`
`2621`	`2623`	`count`
`2622`	`2624`	`-------`
`2623`		`-497`
	`2625`	`+ 97`
`2624`	`2626`	`(1 row)`
`2625`	`2627`

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

Lines changed: 3 additions & 3 deletions

Original file line number	Diff line number	Diff line change
`@@ -865,10 +865,10 @@ DROP TABLE onek_with_null;`
`865`	`865`	`CREATETABLEdupindexcolsAS`
`866`	`866`	`SELECT unique1as id, stringu2::textas f1FROM tenk1;`
`867`	`867`	`CREATEINDEXdupindexcols_iON dupindexcols (f1, id, f1 text_pattern_ops);`
`868`		`-VACUUMANALYZE dupindexcols;`
	`868`	`+ANALYZE dupindexcols;`
`869`	`869`
`870`	`870`	`EXPLAIN (COSTS OFF)`
`871`	`871`	`SELECTcount(*)FROM dupindexcols`
`872`		`-WHERE f1>'MA'and id<1000and f1 ~<~'YX';`
	`872`	`+WHERE f1>'WA'and id<1000and f1 ~<~'YX';`
`873`	`873`	`SELECTcount(*)FROM dupindexcols`
`874`		`-WHERE f1>'MA'and id<1000and f1 ~<~'YX';`
	`874`	`+WHERE f1>'WA'and id<1000and f1 ~<~'YX';`

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!

Commitd6d5f67

File tree

2 files changed

2 files changed

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

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

0 commit comments