Hello,
Constraint exclusion (CE) is very useful for partitioned tables,
but prepared statements interfere with CE if the parameter
contains partition keys, because CE is a planner-time optimization
but the actual parameters are given at executor-time.
I have an idea to use constraint exclusion and prepared statements
together -- converting check constraints into One-Time Filter.
For example, when we have "test" table partitioned by test_{year}:
CREATE TABLE test PARTITIONED BY PARTITION test_2008 CHECK('2008-01-01' <= t AND t < '2009-01-01') PARTITION
test_2009CHECK('2009-01-01' <= t AND t < '2010-01-01') PARTITION test_2010 CHECK('2010-01-01' <= t AND t <
'2011-01-01')
and prepare a statement that have a partitioned key in the parameter:
PREPARE p(timestamp) AS SELECT * FROM test WHERE $1 <= t AND t < $1 + '1 mon';
Then planner converts check constraints into One-Time Filter.
Plan will be the following:
EXPLAIN EXECUTE p('2008-07-01');
------------------------------------------------------------------------- Append -> Result One-Time Filter:
(('2008-01-01'<= $1) AND ($1 < '2009-01-01')) -> Index Scan on test_2008_t_key Index Cond:
(($1<= t) AND (t < ($1 + '1 mon'))) -> Result One-Time Filter: (('2009-01-01' <= $1) AND ($1 <
'2010-01-01')) -> Index Scan on test_2009_t_key Index Cond: (($1 <= t) AND (t < ($1 + '1
mon'))) -> Result One-Time Filter: (('2010-01-01' <= $1) AND ($1 < '2011-01-01')) -> Index Scan on
test_2010_t_key Index Cond: (($1 <= t) AND (t < ($1 + '1 mon')))
We can avoid internal scans when One-Time Filter returns false for each
partition. So we can reuse the plan and receive benefit from CE.
Is this concept ok and worth trying?
If it is reasonable, I'll try it. Comments welcome.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center