From: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
To: postgres sql <pgsql-sql@postgresql.org>
Subject: Re: (possible) bug with constraint exclusion
Date: Fri, 11 Jan 2008 12:50:13 +0530
Message-ID: <a97c77030801102320g63f4fa08j163b67494dfe188@mail.gmail.com> (raw)
In-Reply-To: <a97c77030801102258y3d501045u6b31afe0541997db@mail.gmail.com>
References: <a97c77030801102258y3d501045u6b31afe0541997db@mail.gmail.com>
Update the phenomenon does not exists in 8.2.0 but exists in 8.2.5.
On Jan 11, 2008 12:28 PM, Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote:
> 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: <a97c77030801102320g63f4fa08j163b67494dfe188@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