Re: disfavoring unparameterized nested loops

On Mon, Jun 21, 2021 at 1:38 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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