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_2009 CHECK('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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
