Problem with constraint exclusion on partitions

Hello,

I'm having an issue getting the query planner to skip child tables based on
my check constraints. I have

constraint_exclusion = partition

in my config file, and I think I have everything set up correctly.

The parent table shows the child tables:

cloud_test2=# \d+ engine_sessions

...

Child tables: data.engine_sessions_2008,

data.engine_sessions_2009,

data.engine_sessions_2010,

.

data.engine_sessions_201411,

data.engine_sessions_201412

And the check constraints looks right on the child tables:

cloud_test2=# \d data.engine_sessions_2008

...

Check constraints:

"engine_sessions_2008_check" CHECK (date_created >= '2008-01-01
00:00:00-05'::timestamp with time zone AND date_created < '2009-01-01
00:00:00-05'::timestamp with time zone)

(date_created is a timestamp with time zone not null default now())

Just looking at the explain output- when I select where "date_created >
now() - interval '24 hours'", the query planner does a sequential scan on
all the child tables:

cloud_test2=# explain analyze select * from engine_sessions where
date_created > now() - interval '24 hours';

Append (cost=0.00..59268.32 rows=354 width=97) (actual
time=250.421..255.227 rows=42 loops=1)

-> Seq Scan on engine_sessions (cost=0.00..0.00 rows=1 width=96)
(actual time=0.000..0.000 rows=0 loops=1)

Filter: (date_created > (now() - '24:00:00'::interval))

-> Seq Scan on engine_sessions_2008 (cost=0.00..3384.94 rows=11
width=96) (actual time=12.086..12.086 rows=0 loops=1)

Filter: (date_created > (now() - '24:00:00'::interval))

Rows Removed by Filter: 106568

...

-> Seq Scan on engine_sessions_201411 (cost=0.00..1607.85 rows=5
width=97) (actual time=5.586..5.586 rows=0 loops=1)

Filter: (date_created > (now() - '24:00:00'::interval))

Rows Removed by Filter: 46620

-> Seq Scan on engine_sessions_201412 (cost=0.00..1378.07 rows=180
width=97) (actual time=0.006..4.810 rows=42 loops=1)

Filter: (date_created > (now() - '24:00:00'::interval))

Rows Removed by Filter: 39915

Total runtime: 255.322 ms

(58 rows)

But when I take the output of "now() - interval '24 hours'":

cloud_test2=# select now() - interval '24 hours';

?column?

-------------------------------

2014-12-18 21:28:47.926603-05

(1 row)

And use that directly, it works fine:

cloud_test2=# explain analyze select * from engine_sessions where
date_created > '2014-12-18 21:28:47.926603-05';

QUERY PLAN

----------------------------------------------------------------------------
-------------------------------------------------

Append (cost=0.00..1178.34 rows=181 width=97) (actual time=0.004..3.135
rows=42 loops=1)

-> Seq Scan on engine_sessions (cost=0.00..0.00 rows=1 width=96)
(actual time=0.000..0.000 rows=0 loops=1)

Filter: (date_created > '2014-12-18 21:28:47.926603-05'::timestamp
with time zone)

-> Seq Scan on engine_sessions_201412 (cost=0.00..1178.34 rows=180
width=97) (actual time=0.003..3.130 rows=42 loops=1)

Filter: (date_created > '2014-12-18 21:28:47.926603-05'::timestamp
with time zone)

Rows Removed by Filter: 39915

Total runtime: 3.151 ms

(7 rows)

The types match:

cloud_test2=# select pg_typeof(now() - interval '24 hours');

pg_typeof

--------------------------

timestamp with time zone

Is there something I'm missing?

Thanks!

Mike