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

Hi Craig:
Thank you for your reply.
I checked for constratint_exclusion , in my sample, on and partition is
same(I have no data on parent table). it really works for me.
I tried and found that constraint_exclusion can work in simple ways , but
not for complicated conditions such as "id=a or id=b".
And the union all of two simple query really produced a lower cost.

postgres=# show constraint_exclusion;
constraint_exclusion
----------------------
partition
(1 row)

postgres=# explain select * from ptest where id=5000;
QUERY PLAN

-------------------------------------------------------------------------------------------------
Result (cost=0.00..13.75 rows=2 width=36)
-> Append (cost=0.00..13.75 rows=2 width=36)
-> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62)
Filter: (id = 5000)
-> Index Scan using ctest01_id_idx on ctest01 ptest
(cost=0.00..13.75 rows=1 width=9)
Index Cond: (id = 5000)
(6 rows)

postgres=#

postgres=# explain select * from ptest where id=600000;
QUERY PLAN

-------------------------------------------------------------------------------------------------
Result (cost=0.00..13.75 rows=2 width=36)
-> Append (cost=0.00..13.75 rows=2 width=36)
-> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62)
Filter: (id = 600000)
-> Index Scan using ctest01_id_idx on ctest01 ptest
(cost=0.00..13.75 rows=1 width=9)
Index Cond: (id = 600000)
(6 rows)

postgres=#

postgres=# explain select * from ptest where id=5000
UNION ALL
select * from ptest where id=6000000;
QUERY PLAN

-------------------------------------------------------------------------------------------------------------
Result (cost=0.00..27.55 rows=4 width=36)
-> Append (cost=0.00..27.55 rows=4 width=36)
-> Result (cost=0.00..13.75 rows=2 width=36)
-> Append (cost=0.00..13.75 rows=2 width=36)
-> Seq Scan on ptest (cost=0.00..0.00 rows=1
width=62)
Filter: (id = 5000)
-> Index Scan using ctest01_id_idx on ctest01 ptest
(cost=0.00..13.75 rows=1 width=9)
Index Cond: (id = 5000)
-> Result (cost=0.00..13.75 rows=2 width=36)
-> Append (cost=0.00..13.75 rows=2 width=36)
-> Seq Scan on ptest (cost=0.00..0.00 rows=1
width=62)
Filter: (id = 6000000)
-> Index Scan using ctest02_id_idx on ctest02 ptest
(cost=0.00..13.75 rows=1 width=9)
Index Cond: (id = 6000000)
(14 rows)

postgres=#

2012/11/12 Craig Ringer <craig(at)2ndquadrant(dot)com>

> 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
>