Re: [HACKERS] contraints_exclusion fails to refute simple condition

2015-03-05 Thread Andrew Gierth
 Sandro == Sandro Santilli s...@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 the
 Sandro planner ?

 Sandro Andres Freund on IRC suggested that
 Sandro predicate_refuted_by_simple_clause() looks like trying to
 Sandro handle such cases, but if that's the case it seems to fail
 Sandro 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)


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] contraints_exclusion fails to refute simple condition

2015-03-05 Thread Sandro Santilli
PostGIS installs standard constraints of this kind:

  CHECK (geometrytype(g) = 'POINT'::text OR g IS NULL)

The constraint is used by constraint_exclusion if using this condition:

  WHERE g IS NOT NULL AND geometrytype(g) = 'LINESTRING'

But it is _NOT_ used if the NOT NULL condition is removed:

  WHERE geometrytype(g) = 'LINESTRING'

As the geometrytype is defined as STRICT and IMMUTABLE, there's
no way for geometrytype(g) = 'LINESTRING' to hold true, so why
is the IS NOT NULL condition also needed by the planner ?

Andres Freund on IRC suggested that predicate_refuted_by_simple_clause()
looks like trying to handle such cases, but if that's the case it seems
to fail here.

--strk; 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers