contraints_exclusion fails to refute simple condition

From: Sandro Santilli <strk(at)keybit(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: contraints_exclusion fails to refute simple condition
Date: 2015-03-05 12:20:20
Message-ID: 20150305122020.GA30797@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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;

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2015-03-05 12:46:56 Re: Providing catalog view to pg_hba.conf file - Patch submission
Previous Message Syed, Rahila 2015-03-05 12:14:04 Re: [REVIEW] Re: Compression of full-page-writes