NotificationsYou must be signed in to change notification settings
Fork5
Star26

Commitb746d0c

committed

Fix old bug in get_loop_count().

While poking at David Kubečka's issue I noticed an ancient logic errorin get_loop_count(): it used 1.0 as a "no data yet" indicator, but sincethat is actually a valid rowcount estimate, this doesn't work. If wehave one input relation with 1.0 as rowcount and then another one witha larger rowcount, we should use 1.0 as the result, but we picked thelarger rowcount instead. (I think when I coded this, I recognized theconflict, but mistakenly thought that the logic would pick the desiredcount anyway.)Fixing this changed the plan for one existing regression test case.Since the point of that test is to exercise creation of a particularshape of nestloop plan, I tweaked the query a little bit so it stillresults in the same plan choice.This is definitely a bug, but I'm hesitant to back-patch since it mightchange plan choices unexpectedly, and anyway failure to implement aheuristic precisely as intended is a pretty low-grade bug.

1 parentb557226 commitb746d0cCopy full SHA for b746d0c

File tree

3 files changed

-8

lines changed

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

3 files changed

-8

lines changed

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

Lines changed: 4 additions & 3 deletions

Original file line number	Diff line number	Diff line change
`@@ -1886,7 +1886,7 @@ get_loop_count(PlannerInfo *root, Index cur_relid, Relids outer_relids)`
`1886`	`1886`	`if (outer_relids==NULL)`
`1887`	`1887`	`return1.0;`
`1888`	`1888`
`1889`		`-result=1.0;`
	`1889`	`+result=0.0;`
`1890`	`1890`	`outer_relid=-1;`
`1891`	`1891`	`while ((outer_relid=bms_next_member(outer_relids,outer_relid)) >=0)`
`1892`	`1892`	`{`
`@@ -1915,10 +1915,11 @@ get_loop_count(PlannerInfo *root, Index cur_relid, Relids outer_relids)`
`1915`	`1915`	`outer_rel->rows);`
`1916`	`1916`
`1917`	`1917`	`/* Remember smallest row count estimate among the outer rels */`
`1918`		`-if (result==1.0\|\|result>rowcount)`
	`1918`	`+if (result==0.0\|\|result>rowcount)`
`1919`	`1919`	`result=rowcount;`
`1920`	`1920`	`}`
`1921`		`-returnresult;`
	`1921`	`+/* Return 1.0 if we found no valid relations (shouldn't happen) */`
	`1922`	`+return (result>0.0) ?result :1.0;`
`1922`	`1923`	`}`
`1923`	`1924`
`1924`	`1925`	`/*`

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

Lines changed: 3 additions & 3 deletions

Original file line number	Diff line number	Diff line change
`@@ -3074,14 +3074,14 @@ select f1, unique2, case when unique2 is null then f1 else 0 end`
`3074`	`3074`	`explain (costs off)`
`3075`	`3075`	`select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)`
`3076`	`3076`	`from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand)`
`3077`		`- where a.unique2= 5530 and coalesce(b.twothousand, a.twothousand) = 44;`
	`3077`	`+ where a.unique2< 10 and coalesce(b.twothousand, a.twothousand) = 44;`
`3078`	`3078`	`QUERY PLAN`
`3079`	`3079`	`---------------------------------------------------------------------------------------------`
`3080`	`3080`	`Nested Loop Left Join`
`3081`	`3081`	`-> Nested Loop Left Join`
`3082`	`3082`	`Filter: (COALESCE(b.twothousand, a.twothousand) = 44)`
`3083`	`3083`	`-> Index Scan using tenk1_unique2 on tenk1 a`
`3084`		`- Index Cond: (unique2= 5530)`
	`3084`	`+ Index Cond: (unique2< 10)`
`3085`	`3085`	`-> Bitmap Heap Scan on tenk1 b`
`3086`	`3086`	`Recheck Cond: (thousand = a.unique1)`
`3087`	`3087`	`-> Bitmap Index Scan on tenk1_thous_tenthous`
`@@ -3092,7 +3092,7 @@ select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)`
`3092`	`3092`
`3093`	`3093`	`select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)`
`3094`	`3094`	`from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand)`
`3095`		`- where a.unique2= 5530 and coalesce(b.twothousand, a.twothousand) = 44;`
	`3095`	`+ where a.unique2< 10 and coalesce(b.twothousand, a.twothousand) = 44;`
`3096`	`3096`	`unique1 \| unique1 \| unique1 \| coalesce`
`3097`	`3097`	`---------+---------+---------+----------`
`3098`	`3098`	`(0 rows)`

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

Lines changed: 2 additions & 2 deletions

Original file line number	Diff line number	Diff line change
`@@ -880,11 +880,11 @@ select f1, unique2, case when unique2 is null then f1 else 0 end`
`880`	`880`	`explain (costs off)`
`881`	`881`	`selecta.unique1,b.unique1,c.unique1, coalesce(b.twothousand,a.twothousand)`
`882`	`882`	`from tenk1 aleft join tenk1 bonb.thousand=a.unique1left join tenk1 conc.unique2= coalesce(b.twothousand,a.twothousand)`
`883`		`-wherea.unique2=5530and coalesce(b.twothousand,a.twothousand)=44;`
	`883`	`+wherea.unique2<10and coalesce(b.twothousand,a.twothousand)=44;`
`884`	`884`
`885`	`885`	`selecta.unique1,b.unique1,c.unique1, coalesce(b.twothousand,a.twothousand)`
`886`	`886`	`from tenk1 aleft join tenk1 bonb.thousand=a.unique1left join tenk1 conc.unique2= coalesce(b.twothousand,a.twothousand)`
`887`		`-wherea.unique2=5530and coalesce(b.twothousand,a.twothousand)=44;`
	`887`	`+wherea.unique2<10and coalesce(b.twothousand,a.twothousand)=44;`
`888`	`888`
`889`	`889`	`--`
`890`	`890`	`-- check handling of join aliases when flattening multiple levels of subquery`

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!

Commitb746d0c

File tree

3 files changed

3 files changed

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

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

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

0 commit comments