Problem with constraint exclusion on partitions - Mailing list pgsql-general

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