Hi , Thank you for looking into this
On Fri, Aug 28, 2020 at 9:48 AM Ashutosh Bapat <ashutosh.bapat....@gmail.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
