Re: disfavoring unparameterized nested loops

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jun 21, 2021 at 11:55 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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