On Mon, Jun 21, 2021 at 1:38 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > Hmm, maybe I need to see an example of the sort of plan shape that you > > have in mind. To me it feels like a comparison on a unique key ought > > to use a *parameterized* nested loop. > > The unique-key comparison would be involved in the outer scan in > the cases I'm thinking of. As an example, > > select * from t1, t2 where t1.id = constant and t1.x op t2.y; > > where I'm not assuming much about the properties of "op". > This could be amenable to a plan like > > NestLoop Join > Join Filter: t1.x op t2.y > -> Index Scan on t1_pkey > Index Cond: t1.id = constant > -> Seq Scan on t2 > > and if we can detect that the pkey indexscan produces just one row, > this is very possibly the best available plan.
Hmm, yeah, I guess that's possible. How much do you think this loses as compared with: Hash Join Hash Cond: t1.x op t2.y -> Seq Scan on t2 -> Hash -> Index Scan on t1_pkey (If the operator is not hashable then this plan is impractical, but in such a case the question of preferring the hash join over the nested loop doesn't arise anyway.) > BTW, it strikes me that there might be an additional consideration > here: did parameterization actually help anything? That is, the > proposed rule wants to reject the above but allow > > NestLoop Join > -> Index Scan on t1_pkey > Index Cond: t1.id = constant > -> Seq Scan on t2 > Filter: t1.x op t2.y > > even though the latter isn't meaningfully better. It's possible > this won't arise because we don't consider parameterized paths > except where the parameter is used in an indexqual or the like, > but I'm not confident of that. See in particular reparameterize_path > and friends before you assert there's no such issue. So we might > need to distinguish essential from incidental parameterization, > or something like that. Hmm, perhaps. I think it won't happen in the normal cases, but I can't completely rule out the possibility that there are corner cases where it does. -- Robert Haas EDB: http://www.enterprisedb.com
