Partitioned tables constraint_exclusion - Mailing list pgsql-hackers

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.95rows=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.95rows=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
Scanon 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.25rows=367
 
width=0)              Filter: (var_ts > (now() - '1 mon'::interval))        ->  Seq Scan on kid_200603 master
(cost=0.00..29.25rows=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.50rows=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.50rows=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.50rows=367
 
width=0)              Filter: (var_ts > ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
(12 rows)

mytest=#