PostgreSQL table partitioning problem

Question

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 :

http://explain.depesz.com/s/VDI0

• Chris: In your master table, you have point_date as a timestamp with time zone. In your CHECK constraint, you have used timestamp without time zone. Then, in your query, you just use point_date >= '2015年08月25日 00:00:00'::timestamp, a timestamp type. Make sure all the types are consistent, and then if that doesn't work, we'll dig a bit deeper.
• onesvat: I changed accordingly, but query plan did not change
• onesvat: I changed SELECT query to ::timestamp with time zone, the problem is now fixed. Thank you

Answer 1

I'm going to add my answer, which was correct in the comments, as the answer here.

Be specific about your types!

In your master table, you have point_date as a timestamp with time zone.

In your CHECK constraint, you have used timestamp without time zone.

Then, in your query, you just use point_date >= '2015-08-25 00:00:00'::timestamp, thus using a timestamp type in your WHERE predicate.

To fix your problems, make sure that all of these entries are referring to a consistent type. In your comments above, you indicated that you changed all entries to refer to the type timestamp with time zone, and that it fixed your issues.