DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=MW9iCOERBSq8M/JyE4KXcqtpmp0Pjd5yg4D6MxsUU+9Xw31UX45xPn/JG0FudaBdfwMYJKBzZjpJStWObD6FIOmBWRE7lIosQqezzNsaJ1GcTFKTQaMvM/zsaydFsQLCSVhJtbb0u4MEf28imzrVujAs4/AqdU2tl1ONwyKJi8k=
Message-ID: <a97c77030801102258y3d501045u6b31afe0541997db@mail.gmail.com>
Date: Fri, 11 Jan 2008 12:28:09 +0530
From: "Rajesh Kumar Mallah" <mallah.rajesh@gmail.com>
To: "postgres admin" <pgsql-sql@postgresql.org>
Subject: (possible) bug with constraint exclusion
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi ,

looks like constraint exclusion is being too aggressive in excluding null values
although its well known that check constraints apply on not null values only.
Hope the minimal test session below explains the problem we facing.
BTW: we are very impressed with the performance gains we achieved  by
partitioning a table recently.


tradein_clients=> SELECT version();
                                              version
---------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.4.6 20060404 (Red Hat 3.4.6-9)
(1 row)

tradein_clients=> \pset null NULL


 tradein_clients=> \d x
        Table "temp.x"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
Check constraints:
    "x_id_check" CHECK (id > 0)

tradein_clients=> SELECT * from x;
  id
------
    1
    2
 NULL
(3 rows)

tradein_clients=> explain SELECT * from  x  where id is null;
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
   One-Time Filter: false
(2 rows)

tradein_clients=> SELECT * from  x  where id is null;
 id
----
(0 rows)
tradein_clients=> SET constraint_exclusion to off;
SET
tradein_clients=> SELECT * from  x  where id is null;
  id
------
 NULL
(1 row)

tradein_clients=>

Regds
mallah.