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.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
