Odd plan shown in src/backend/optimizer/README

Started by Etsuro Fujitaalmost 9 years ago3 messages
#1Etsuro Fujita
fujita.etsuro@lab.ntt.co.jp
1 attachment(s)

While working on a bug in postgres_fdw, I found this:

NestLoop
-> Seq Scan on SmallTable1 A
NestLoop
-> Seq Scan on SmallTable2 B
NestLoop
-> Index Scan using XYIndex on LargeTable C
Index Condition: C.X = A.AID and C.Y = B.BID

This seems odd to me; we would not need the bottom-level Nestloop.
Attached is a small patch for fixing that. I also adjusted the
indentation to the last line "Index Condition: ...", to match others in
the section of Parameterized Paths.

Best regards,
Etsuro Fujita

Attachments:

optimizer-readme.patchtext/x-patch; name=optimizer-readme.patchDownload
*** a/src/backend/optimizer/README
--- b/src/backend/optimizer/README
***************
*** 756,764 **** to create a plan like
          -> Seq Scan on SmallTable1 A
          NestLoop
              -> Seq Scan on SmallTable2 B
!             NestLoop
!                 -> Index Scan using XYIndex on LargeTable C
!                       Index Condition: C.X = A.AID and C.Y = B.BID
  
  so we should be willing to pass down A.AID through a join even though
  there is no join order constraint forcing the plan to look like this.
--- 756,763 ----
          -> Seq Scan on SmallTable1 A
          NestLoop
              -> Seq Scan on SmallTable2 B
!             -> Index Scan using XYIndex on LargeTable C
!                 Index Condition: C.X = A.AID and C.Y = B.BID
  
  so we should be willing to pass down A.AID through a join even though
  there is no join order constraint forcing the plan to look like this.
#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Etsuro Fujita (#1)
Re: Odd plan shown in src/backend/optimizer/README

Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> writes:

This seems odd to me; we would not need the bottom-level Nestloop.
Attached is a small patch for fixing that.

Pushed, thanks.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#3Etsuro Fujita
fujita.etsuro@lab.ntt.co.jp
In reply to: Tom Lane (#2)
Re: Odd plan shown in src/backend/optimizer/README

On 2017/01/23 23:39, Tom Lane wrote:

Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> writes:

This seems odd to me; we would not need the bottom-level Nestloop.
Attached is a small patch for fixing that.

Pushed, thanks.

Thank you for picking this up!

Best regards,
Etsuro Fujita

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers