PostgreSQL 9.3 Partitions Not Working

Question

I have a table, e, with range partitions setup daily. The main table and partition tables have been defined like so.

CREATE TABLE e (
  toi TIMESTAMP WITH TIME ZONE
WITH (
 OIDS=TRUE
);

CREATE TABLE e_20150501
CONSTRAINT e_20150501_toi_chk CHECK (toi >= ‘2015-05-01’::DATE AND toi < ‘2015-  05-02’::DATE)
INHERITS (e)
WITH (
  OIDS=TRUE
);

For some reason, no matter how I construct my WHERE clause in SELECTs, I can’t seem to get a plan that doesn't Seq Scan every single partition table?

I’ve tried …

WHERE toi::DATE >= ‘2015-05-01’::DATE AND toi::DATE <= ‘2015-05-02’::DATE;

WHERE toi >= to_timestamp(‘2015-05-01 12:30:57’, ‘YYYY-MM-DD HH24:MI:SS’) AND toi <= to_timestamp(‘2015-05-02 12:30:57’, ‘YYYY-MM-DD HH24:MI:SS’);

What am I missing?

Answer 1

You are missing SET constraint_exclusion = on; without it set in your table definition all queries will scan all table partitions.

see the manual here section 5.9.4. Partitioning and Constraint Exclusion

Answer 2

The fist comment on my original post was correct.

You are mixing dates and timestamps. You should define your check constraint to use a timestamp as well: CHECK (toi >= timestamp '2015-05-01 00:00:00' ... And then use timestamps in your query as well. – a_horse_with_no_name