Hello, I'm having an issue getting the query planner to skip child tables based on my check constraints. I have constraint_exclusion = partition in my config file, and I think I have everything set up correctly. The parent table shows the child tables: cloud_test2=# \d+ engine_sessions ... Child tables: data.engine_sessions_2008, data.engine_sessions_2009, data.engine_sessions_2010, … data.engine_sessions_201411, data.engine_sessions_201412 And the check constraints looks right on the child tables: cloud_test2=# \d data.engine_sessions_2008 ... Check constraints: "engine_sessions_2008_check" CHECK (date_created >= '2008-01-01 00:00:00-05'::timestamp with time zone AND date_created < '2009-01-01 00:00:00-05'::timestamp with time zone) (date_created is a timestamp with time zone not null default now()) Just looking at the explain output- when I select where "date_created > now() - interval '24 hours'", the query planner does a sequential scan on all the child tables: cloud_test2=# explain analyze select * from engine_sessions where date_created > now() - interval '24 hours'; Append (cost=0.00..59268.32 rows=354 width=97) (actual time=250.421..255.227 rows=42 loops=1) -> Seq Scan on engine_sessions (cost=0.00..0.00 rows=1 width=96) (actual time=0.000..0.000 rows=0 loops=1) Filter: (date_created > (now() - '24:00:00'::interval)) -> Seq Scan on engine_sessions_2008 (cost=0.00..3384.94 rows=11 width=96) (actual time=12.086..12.086 rows=0 loops=1) Filter: (date_created > (now() - '24:00:00'::interval)) Rows Removed by Filter: 106568 ... -> Seq Scan on engine_sessions_201411 (cost=0.00..1607.85 rows=5 width=97) (actual time=5.586..5.586 rows=0 loops=1) Filter: (date_created > (now() - '24:00:00'::interval)) Rows Removed by Filter: 46620 -> Seq Scan on engine_sessions_201412 (cost=0.00..1378.07 rows=180 width=97) (actual time=0.006..4.810 rows=42 loops=1) Filter: (date_created > (now() - '24:00:00'::interval)) Rows Removed by Filter: 39915 Total runtime: 255.322 ms (58 rows) But when I take the output of "now() - interval '24 hours'": cloud_test2=# select now() - interval '24 hours'; ?column? ------------------------------- 2014-12-18 21:28:47.926603-05 (1 row) And use that directly, it works fine: cloud_test2=# explain analyze select * from engine_sessions where date_created > '2014-12-18 21:28:47.926603-05'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Append (cost=0.00..1178.34 rows=181 width=97) (actual time=0.004..3.135 rows=42 loops=1) -> Seq Scan on engine_sessions (cost=0.00..0.00 rows=1 width=96) (actual time=0.000..0.000 rows=0 loops=1) Filter: (date_created > '2014-12-18 21:28:47.926603-05'::timestamp with time zone) -> Seq Scan on engine_sessions_201412 (cost=0.00..1178.34 rows=180 width=97) (actual time=0.003..3.130 rows=42 loops=1) Filter: (date_created > '2014-12-18 21:28:47.926603-05'::timestamp with time zone) Rows Removed by Filter: 39915 Total runtime: 3.151 ms (7 rows) The types match: cloud_test2=# select pg_typeof(now() - interval '24 hours'); pg_typeof -------------------------- timestamp with time zone Is there something I'm missing? Thanks! Mike |
