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

[高健 <luckyjackgao@xxxxxxxxx>]

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

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