Re: Evaluate expression at planning time for two more cases - Mailing list pgsql-hackers

Hi Surafel,

On Thu, Aug 27, 2020 at 6:01 PM Surafel Temesgen <surafel3000@gmail.com> wrote:
>
> Hi,
>
> In good written query IS NULL and IS NOT NULL check on primary and non null constraints columns should not happen but
ifit is mentioned PostgreSQL have to be smart enough for not checking every return result about null value on primary
keycolumn. Instead it can be evaluate its truth value and set the result only once. The attached patch evaluate and set
thetruth value for null and not null check on primary column on planning time if the relation attribute is not mention
onnullable side of outer join. 
>
> Thought?

Thanks for the patch. Such SQL may arise from not-so-smart SQL
generation tools. It will be useful to have this optimization. Here
are some comments on your patch.

                     }
                     else
                         has_nonconst_input = true;
@@ -3382,7 +3395,47 @@ eval_const_expressions_mutator(Node *node,

+
+                    if (pkattnos != NULL &&
bms_is_member(var->varattno - FirstLowInvalidHeapAttributeNumber,
pkattnos)
+                        && !check_null_side(context->root, relid))

Since this is working on parse->rtable this will work only for top level tables
as against the inherited tables or partitions which may have their own primary
key constraints if the parent doesn't have those.

This better be done when planning individual relations, plain or join or upper,
where all the necessary information is already available with each of the
relations and also the quals, derived as well as user specified, are
distributed to individual relations where they should be evalutated. My memory
is hazy but it might be possible do this while distributing the quals
themselves (distribute_qual_to_rels()).

Said that, to me, this looks more like something we should be able to do at the
time of constraint exclusion. But IIRC, we just prove whether constraints
refute a qual and not necessarily whether constraints imply a qual, making it
redundant, as is required here. E.g. primary key constraint implies key NOT
NULL rendering a "key IS NOT NULL" qual redundant. It might be better to test
the case when col IS NOT NULL is specified on a column which already has a NOT
NULL constraint. That may be another direction to take. We may require much
lesser code.

With either of these two approaches, the amount of code changes might
be justified.

+explain (costs off)
+SELECT * FROM b RIGHT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL
OR a.id > 0);
+                  QUERY PLAN
+-----------------------------------------------
+ Hash Right Join
+   Hash Cond: (b.a_id = a.id)
+   ->  Seq Scan on b
+   ->  Hash
+         ->  Bitmap Heap Scan on a
+               Recheck Cond: (id > 0)
+               ->  Bitmap Index Scan on a_pkey
+                     Index Cond: (id > 0)
+(8 rows)

Thanks for the tests.

Please add the patch to the next commitfest https://commitfest.postgresql.org/.

--
Best Wishes,
Ashutosh Bapat