Robert Haas <robertmh...@gmail.com> writes: > On Mon, Jun 21, 2021 at 11:55 AM Tom Lane <t...@sss.pgh.pa.us> wrote: >> There are certainly cases where the optimizer can prove (in principle; >> it doesn't do so today) that a plan node will produce at most one row. >> They're hardly uncommon either: an equality comparison on a unique >> key, or a subquery with a simple aggregate function, come to mind.
> 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. Nor do I think this is an unusual situation that we can just ignore. 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. regards, tom lane