Re: Can dml realize the partition table's rule and make good execution plan?

On 11/12/2012 10:39 AM, 高健 wrote:
> The selection used where condition for every partition table, which
> is not what I want. my rule is just for id column value.
> And my select sql statement's where condition is also for id column value.
After re-reading your question I see what you're getting at. You want
the query planner to rewrite it as if it were:

explain select * from ptest where id=5000
UNION ALL
select * from ptest WHERE id=6000000

and produce a plan like this:

regress=> explain select * from ptest where id=5000 UNION ALL select *
from ptest WHERE id=6000000;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------
Result (cost=0.00..25.58 rows=10 width=62)
-> Append (cost=0.00..25.58 rows=10 width=62)
-> Result (cost=0.00..12.74 rows=5 width=62)
-> Append (cost=0.00..12.74 rows=5 width=62)
-> Seq Scan on ptest (cost=0.00..0.00 rows=1
width=62)
Filter: (id = 5000)
-> Bitmap Heap Scan on ctest01 ptest
(cost=4.28..12.74 rows=4 width=62)
Recheck Cond: (id = 5000)
-> Bitmap Index Scan on ctest01_id_idx
(cost=0.00..4.28 rows=4 width=0)
Index Cond: (id = 5000)
-> Result (cost=0.00..12.74 rows=5 width=62)
-> Append (cost=0.00..12.74 rows=5 width=62)
-> Seq Scan on ptest (cost=0.00..0.00 rows=1
width=62)
Filter: (id = 6000000)
-> Bitmap Heap Scan on ctest02 ptest
(cost=4.28..12.74 rows=4 width=62)
Recheck Cond: (id = 6000000)
-> Bitmap Index Scan on ctest02_id_idx
(cost=0.00..4.28 rows=4 width=0)
Index Cond: (id = 6000000)
(18 rows)

ie to scan ctest01 using ctest01_id_idx for 500, and ctest02 using
ctest02_id_idx for
6000000, then combine the results.

If so: I'm not aware of any way to make the planner aware that that's
possible. It'd be an interesting enhancement, to apply constraint
exclusion to values pushed down into partitions, rather than simply to
include or exclude partitions based on constraint exclusion.

--
Craig Ringer