Re: Evaluate expression at planning time for two more cases

Hi ,

Thank you for looking into this

On Fri, Aug 28, 2020 at 9:48 AM Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
wrote:

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

In that case the table have to be specified in from clause otherwise its
error

e.g postgres=# CREATE TABLE cities (

name text,

population float,

altitude int

);

CREATE TABLE

postgres=# CREATE TABLE capitals (

id serial primary key,

state char(2)

) INHERITS (cities);

CREATE TABLE

postgres=# EXPLAIN SELECT * FROM cities WHERE id is not null;

ERROR: column "id" does not exist

LINE 1: EXPLAIN SELECT * FROM cities WHERE id is not null;

Even it will not work on the child table because the primary key constraint
on the parent table is not in-force in the child table.

> 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()).
>
>
The place where all the necessary information available is on
reduce_outer_joins as the comment of the function states but the downside
is its will only be inexpensive if the query contains outer join

> 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.
>
>
I don’t add NOT NULL constraint optimization to the patch because cached
plan is not invalidation in case of a change in NOT NULL constraint

> Please add the patch to the next commitfest
> https://commitfest.postgresql.org/.
>
>
I add it is here https://commitfest.postgresql.org/29/2699/
Thank you

regards
Surafel