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

On Thu, Jul 6, 2023 at 5:26 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:

> On Thu, Jul 6, 2023 at 7:55 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
>> 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.
>

Attached is what I have in mind. The patch extends the logic from two
points.

* it also checks OR clauses to see if it is always true.

* it also checks for join clauses by additionally testing if the nulling
bitmap is empty.

I did not try the logic about testing a qual against the relation's
constraints though.

Thanks
Richard