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
