PostgreSQL Table Partitioning – Troubleshooting Common Issues

postgresql

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

Best Answer

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.

Related Question