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

On 8/10/2023 15:26, Richard Guo wrote:
Hi,
> On Thu, Sep 28, 2023 at 11:51 AM David Rowley <dgrowleyml(at)gmail(dot)com
> <mailto:dgrowleyml(at)gmail(dot)com>> wrote:
>
> On Thu, 28 Sept 2023 at 16:22, Richard Guo <guofenglinux(at)gmail(dot)com
> <mailto: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.

Setting aside the thread's subject, I am interested in this feature
because of its connection with the SJE feature and the same issue raised
[1] during the discussion.
In the attachment - rebased version of your patch (because of the
5d8aa8bced).
Although the patch is already in a good state, some improvements can be
made. Look:
explain (costs off)
SELECT oid,relname FROM pg_class
WHERE oid < 5 OR (oid = 1 AND oid IS NULL);

Bitmap Heap Scan on pg_class
Recheck Cond: ((oid < '5'::oid) OR ((oid = '1'::oid) AND (oid IS NULL)))
-> BitmapOr
-> Bitmap Index Scan on pg_class_oid_index
Index Cond: (oid < '5'::oid)
-> Bitmap Index Scan on pg_class_oid_index
Index Cond: ((oid = '1'::oid) AND (oid IS NULL))

If we go deeply through the filter, I guess we could replace such buried
clauses.

[1] Removing unneeded self joins
https://www.postgresql.org/message-id/CAPpHfdt-0kVV7O%3D%3DaJEbjY2iGYBu%2BXBzTHEbPv_6sVNeC7fffQ%40mail.gmail.com

--
regards,
Andrei Lepikhov
Postgres Professional