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

On Tue, Oct 24, 2023 at 12:25 PM Andrei Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
wrote:

> 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.

Thanks for taking an interest in this.

I rebased this patch over the SJE commit, and found that it can help
discard redundant IS_NOT_NULL quals added by SJE logic if we've
successfully removed some self-joins on primary keys, as shown by the
regression test plan changes, which IMO makes this patch look more
useful in practice.

> 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.

Yeah, we can do that by exploring harder on OR clauses. But for now I
think it's more important for this patch to introduce the
'reduce-quals-to-constant' mechanism. As a start I think it'd be better
to keep the logic simple for review. In the future maybe we can extend
it to consider more than just NullTest quals, for example we could also
consider applicable constraint expressions of the given relation.

Thanks
Richard