Re: contraints_exclusion fails to refute simple condition - Mailing list pgsql-hackers

From Andrew Gierth
Subject Re: contraints_exclusion fails to refute simple condition
Date
Msg-id 87bnk7z9h6.fsf@news-spur.riddles.org.uk
Whole thread Raw
In response to contraints_exclusion fails to refute simple condition  (Sandro Santilli <strk@keybit.net>)
List pgsql-hackers
>>>>> "Sandro" == Sandro Santilli <strk@keybit.net> writes:
Sandro> PostGIS installs standard constraints of this kind:
Sandro>   CHECK (geometrytype(g) = 'POINT'::text OR g IS NULL)

If geometrytype() is strict, the IS NULL condition is superfluous.
CHECK(x) _passes_ when x is null, rather than rejecting the row.
Sandro> But it is _NOT_ used if the NOT NULL condition is removed:
Sandro>   WHERE geometrytype(g) = 'LINESTRING'
Sandro> As the "geometrytype" is defined as STRICT and IMMUTABLE,Sandro> there's no way for geometrytype(g) =
'LINESTRING'to hold true,Sandro> so why is the "IS NOT NULL" condition also needed by theSandro> planner ?
 
Sandro> Andres Freund on IRC suggested thatSandro> predicate_refuted_by_simple_clause() looks like trying toSandro>
handlesuch cases, but if that's the case it seems to failSandro> here.
 

It looks like it searches only one level deep into the clause, so it
knows that if OP or FUNC is strict that (g OP const) or (FUNC(g)) will
refute (g IS NULL), but not that (FUNC(g) OP const) will do so, because
the reference to g is nested too deep.

In this case the obvious thing to do is remove the redundant clause.
Whether it would be worth teaching the predicate prover to recurse in
such cases would be a performance tradeoff - likely a poor one, since it
would have to recurse into arbitrarily complex expressions.

-- 
Andrew (irc:RhodiumToad)



pgsql-hackers by date:

Previous
From: Stephen Frost
Date:
Subject: Re: Additional role attributes && superuser review
Next
From: Stephen Frost
Date:
Subject: Re: CATUPDATE confusion?