Обсуждение: Convert check constraints into One-Time_Filter on prepared statements
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
ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes: > 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'))) > > 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. It's tempting to go straight for the special case where we can detect that the constraints are mutually exclusive and we can build a lookup table or do a binary search to find the single partition which is included. But the this would handle the general case and would be a huge help. I've looked at plans where partitioning represented a huge slowdown and the time seemed to just disappear down the hole. All the time was being spent in just the index lookup startup and probe for dozens of partitions which returned no rows. In combination with the ordered append node -- which is still sitting in my experimental directory -- this would basically "fix" partitioning. The planner would recognize that the result is ordered and merge-joinable and it wouldn't waste any time doing any index probes. There would be a much smaller waste of time setting up the degenerate heap merge and checking all those conditions in the executor. These things will matter when we get to scaling up to hundreds of plan nodes but the situation would still be much better than today. The other reason I think it's still worth doing this even though it's not as effective as covering the special case of mutually exclusive partitions is that there's still a place for this even once we have a more mature partitioning scheme. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning