Обсуждение: Problem with constraint exclusion on partitions
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
Mike Pultz wrote > Is there something I'm missing? What version are you using? Now() is a volatile function so the planner cannot omit partitions. Replace that with a constant and now it can. David J. -- View this message in context: http://postgresql.nabble.com/Problem-with-constraint-exclusion-on-partitions-tp5831541p5831548.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hey David, > What version are you using? Sorry, I'm on 9.3.4. >Now() is a volatile function so the planner cannot omit partitions. > >Replace that with a constant and now it can. I'm not sure what you mean- now() is a stable function: cloud_test2=# \df+ now() Schema | Name | Result data type | Argument data types | Type | Security | Volatility | Owner | Language | Source code | Description ------------+------+--------------------------+---------------------+------- -+----------+------------+-------+----------+-------------+----------------- --------- pg_catalog | now | timestamp with time zone | | normal | invoker | stable | pgres | internal | now | current transaction time it's the timestamp at the start of the transaction- so the planner should have a set value for all rows. Am I missing something else? Mike
Hey David,
> What version are you using?
Sorry, I'm on 9.3.4.
>Now() is a volatile function so the planner cannot omit partitions.
>
>Replace that with a constant and now it can.
I'm not sure what you mean- now() is a stable function:
it's the timestamp at the start of the transaction- so the planner should
have a set value for all rows.
Am I missing something else?
View this message in context: Re: Problem with constraint exclusion on partitions
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
David G Johnston wrote > On Saturday, December 20, 2014, Mike Pultz [via PostgreSQL] < > ml-node+s1045698n5831551h5@.nabble >> wrote: > >> Hey David, >> >> > What version are you using? >> >> Sorry, I'm on 9.3.4. >> >> >Now() is a volatile function so the planner cannot omit partitions. >> > >> >Replace that with a constant and now it can. >> >> I'm not sure what you mean- now() is a stable function: > > > My mistake but unless it is immutable the planner cannot evaluate it, it > has to defer to the executor. The executor can evaluate it a single time > for the query but it is still left with the original execution plan given > to it by the planner. > > >> it's the timestamp at the start of the transaction- so the planner should >> have a set value for all rows. > > >> Am I missing something else? >> >> > Precise responsibility and timing mechanics between the planner and > executor which I cannot give adequate exposition on off the top of my > head... > > David J. http://www.postgresql.org/docs/9.3/interactive/ddl-partitioning.html Note the third-to-last paragraph. David J. -- View this message in context: http://postgresql.nabble.com/Problem-with-constraint-exclusion-on-partitions-tp5831541p5831553.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.