From: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
To: postgres admin <pgsql-sql@postgresql.org>
Subject: (possible) bug with constraint exclusion
Date: Fri, 11 Jan 2008 12:28:09 +0530
Message-ID: <a97c77030801102258y3d501045u6b31afe0541997db@mail.gmail.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah.rajesh@gmail.com
Subject: Re: (possible) bug with constraint exclusion
In-Reply-To: <a97c77030801102258y3d501045u6b31afe0541997db@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox