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

On Thu, Jul 6, 2023 at 7:55 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Thu, 7 Jul 2022 at 15:50, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > > Anyway, I've no current plans to take the attached any further. I
> > > think it'll be better to pursue your NULLable-Var stuff and see if we
> > > can do something more generic like remove provably redundant NullTests
> > > from baserestrictinfo.
> >
> > Yeah, I suspect that the way forward is to allow
> > preprocess_minmax_aggregates to do what it does now, and then
> > remove the IS NOT NULL clause again later when we have the
> > info available to let us do that in a generic way.
>
> I started looking at a more generic way to fix this. In the attached
> I'm catching quals being added to baserestrictinfo in
> distribute_restrictinfo_to_rels() and checking for IS NOT NULL quals
> on columns defined with NOT NULL.
>
> I did this by adding a new function add_baserestrictinfo_to_rel()
> which can be the place where we add any future logic to ignore other
> always-true quals. Perhaps in the future, we can add some logic there
> to look for quals on partitions which are always true based on the
> partition constraint.

I think this is a good start. Maybe we can extend it with little effort
to cover OR clauses. For an OR clause, we can test its sub-clauses and
if one of them is IS NOT NULL qual on a NOT NULL column then we can know
that the OR clause is always true.

Maybe we can also test if the qual is always true according to the
applicable constraint expressions of the given relation, something that
is like the opposite of relation_excluded_by_constraints(). Of course
that would require much more efforts.

Another thing I'm wondering is that since we already have the
outer-join-aware-Var infrastructure, maybe we can also test whether a IS
NOT NULL qual in join clauses is always true. I imagine we need to test
whether the Var in the IS NOT NULL qual has an empty varnullingrels
besides that the Var is a NOT NULL column.

BTW, with this patch the variable ‘rel’ in function
distribute_restrictinfo_to_rels is unused.

Thanks
Richard