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 :
point_date
as atimestamp with time zone
. In yourCHECK
constraint, you have usedtimestamp without time zone
. Then, in your query, you just usepoint_date >= '2015-08-25 00:00:00'::timestamp
, atimestamp
type. Make sure all the types are consistent, and then if that doesn't work, we'll dig a bit deeper.