Partitioned tables constraint_exclusion

I'm not sure if this is a bug, missing feature, misunderstanding on my part?

I checked the TODO list and couldn't find anything on it.

I currently have a 750 million row table, indexes are > 10 GB, so trying
to partition it.

The basic -

constraint_exclusion + exact match = OK
constraint_exclusion + ( var + var )::case = Not OK

Weslee

I tried to break it down to a simple case -
(kid_200601 should never show up in the plan)

mytest=# create table master ( var_text text not null, var_ts timestamp
with time zone not null, unique ( var_ts ) );
NOTICE: CREATE TABLE / UNIQUE will create implicit index
"master_var_ts_key" for table "master"
CREATE TABLE
mytest=# create table kid_200601 ( check ( var_ts >= '2006-01-01
00:00:00' AND var_ts < '2006-02-01 00:00:00' ) ) inherits ( master ) ;
CREATE TABLE
mytest=# create table kid_200602 ( check ( var_ts >= '2006-02-01
00:00:00' AND var_ts < '2006-03-01 00:00:00' ) ) inherits ( master ) ;
CREATE TABLE
mytest=# create table kid_200603 ( check ( var_ts >= '2006-03-01
00:00:00' AND var_ts < '2006-04-01 00:00:00' ) ) inherits ( master ) ;
CREATE TABLE
mytest=# explain select count(*) from master where var_ts > '2006-02-22
00:00:00' ;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Aggregate (cost=71.94..71.95 rows=1 width=0)
-> Append (cost=7.09..69.18 rows=1101 width=0)
-> Bitmap Heap Scan on master (cost=7.09..21.68 rows=367 width=0)
Recheck Cond: (var_ts > '2006-02-22
00:00:00+00'::timestamp with time zone)
-> Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.00 rows=367 width=0)
Index Cond: (var_ts > '2006-02-22
00:00:00+00'::timestamp with time zone)
-> Seq Scan on kid_200602 master (cost=0.00..23.75 rows=367
width=0)
Filter: (var_ts > '2006-02-22 00:00:00+00'::timestamp
with time zone)
-> Seq Scan on kid_200603 master (cost=0.00..23.75 rows=367
width=0)
Filter: (var_ts > '2006-02-22 00:00:00+00'::timestamp
with time zone)
(10 rows)

mytest=# select now() ;
now
-------------------------------
2007-03-26 16:02:29.360435+00
(1 row)

mytest=# explain select count(*) from master where var_ts > ( now() - '1
month'::interval )::timestamptz ;
QUERY PLAN
----------------------------------------------------------------------------------------------
Aggregate (cost=114.94..114.95 rows=1 width=0)
-> Append (cost=7.10..111.27 rows=1468 width=0)
-> Bitmap Heap Scan on master (cost=7.10..23.52 rows=367 width=0)
Recheck Cond: (var_ts > (now() - '1 mon'::interval))
-> Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.01 rows=367 width=0)
Index Cond: (var_ts > (now() - '1 mon'::interval))
-> Seq Scan on kid_200601 master (cost=0.00..29.25 rows=367
width=0)
Filter: (var_ts > (now() - '1 mon'::interval))
-> Seq Scan on kid_200602 master (cost=0.00..29.25 rows=367
width=0)
Filter: (var_ts > (now() - '1 mon'::interval))
-> Seq Scan on kid_200603 master (cost=0.00..29.25 rows=367
width=0)
Filter: (var_ts > (now() - '1 mon'::interval))
(12 rows)

mytest=# show constraint_exclusion ;
constraint_exclusion
----------------------
on
(1 row)

mytest=# explain select count(*) from master where var_ts > (
'2007-03-26 16:03:27.370627+00'::timestamptz - '1 month'::interval
)::timestamptz ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=105.77..105.78 rows=1 width=0)
-> Append (cost=7.10..102.10 rows=1468 width=0)
-> Bitmap Heap Scan on master (cost=7.10..22.60 rows=367 width=0)
Recheck Cond: (var_ts > ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
-> Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.01 rows=367 width=0)
Index Cond: (var_ts > ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
-> Seq Scan on kid_200601 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts > ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
-> Seq Scan on kid_200602 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts > ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
-> Seq Scan on kid_200603 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts > ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
(12 rows)

mytest=#