Re: BUG #17540: Prepared statement: PG switches to a generic query plan which is consistently much slower

On Thu, Sep 28, 2023 at 11:51 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Thu, 28 Sept 2023 at 16:22, Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> > It seems that optimizing IS NULL quals is more complex than optimizing
> > IS NOT NULL quals. I also wonder if it's worth the trouble to optimize
> > IS NULL quals.
>
> I'm happy to reduce the scope of this patch. As for what to cut, I
> think if we're doing a subset then we should try to do that subset in
> a way that best leaves things open for phase 2 at some later date.

I had a go at supporting IS NULL quals and ended up with the attached.
The patch generates a new constant-FALSE RestrictInfo that is marked
with the same required_relids etc as the original one if it is an IS
NULL qual that can be reduced to FALSE. Note that the original
rinfo_serial is also copied to the new RestrictInfo.

One thing that is not great is that we may have 'FALSE and otherquals'
in the final plan, as shown by the plan below which is from the new
added test case.

+explain (costs off)
+select * from pred_tab t1 left join pred_tab t2 on true left join pred_tab
t3 on t2.a is null and t2.b = 1;
+ QUERY PLAN
+---------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Nested Loop Left Join
+ Join Filter: (false AND (t2.b = 1))
+ -> Seq Scan on pred_tab t2
+ -> Result
+ One-Time Filter: false
+(8 rows)

Maybe we can artificially reduce it to 'FALSE', but I'm not sure if it's
worth the trouble.

Thanks
Richard