Re: Can dml realize the partition table's rule and make good execution plan? - Mailing list pgsql-general
| From | 高健 |
|---|---|
| Subject | Re: Can dml realize the partition table's rule and make good execution plan? |
| Date | |
| Msg-id | CAL454F1w3XK1Mvq1sYs_CGPfGkbP3cg81bituXXeqHxu8ssaxg@mail.gmail.com Whole thread Raw |
| In response to | Re: Can dml realize the partition table's rule and make good execution plan? (Craig Ringer <craig@2ndQuadrant.com>) |
| Responses |
Re: Can dml realize the partition table's rule and make
good execution plan?
(Craig Ringer <craig@2ndQuadrant.com>)
|
| List | pgsql-general |
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@2ndquadrant.com>
On 11/12/2012 10:39 AM, 高健 wrote:> The selection used where condition for every partition table, whichAfter re-reading your question I see what you're getting at. You want
> 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.
the query planner to rewrite it as if it were:UNION ALL
explain select * from ptest where id=5000
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=1Filter: (id = 5000)
width=62)-> 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=1Filter: (id = 6000000)
width=62)-> 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)ie to scan ctest01 using ctest01_id_idx for 500, and ctest02 using
(18 rows)
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
pgsql-general by date: