I partitioned my table day by day. The child tables are look like points_20150830. Every day, I run a cron job to create table and I insert records without using any trigger function. I insert records by calculating day of timestamp.
However, when I run a select query, query plan shows that every child table visited without checking constraints. Also I enabled constraints via "constraint_exclusion = on" in postgresql.conf
I think that the problem may be related to timestamp with timezone but I could not find any solution.
Thanks for your help, here are details
Master table :
CREATE TABLE points ( point_key bytea, users_id integer, point_date timestamp with time zone ); CREATE INDEX points_point_date ON points USING btree (point_date); CREATE INDEX points_users_id ON points USING btree (users_id);
Child table :
CREATE TABLE points_20150701 ( CONSTRAINT points_20150701_point_date_check CHECK (((point_date >= '2015-07-01 00:00:00'::timestamp without time zone) AND (point_date < '2015-07-02 00:00:00'::timestamp without time zone))) ) INHERITS (points); CREATE INDEX points_20150701_point_date ON points_20150701 USING btree (point_date); CREATE INDEX points_20150701_users_id ON points_20150701 USING btree (users_id);
Query that I perform :
EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(point_key) as points FROM points WHERE users_id = 100 AND point_date >= '2015-08-25 00:00:00'::timestamp AND point_date <= '2015-08-31 23:59:59'::timestamp
Result of the query :