Re: partitioned table query question

On Dec 7, 2007, at 10:51 PM, Mason Hale wrote:

> I'm implementing table partitioning on 8.2.5 -- I've got the tables  
> set up to partition based on the % 10 value of a key.
>
> My problem is that I can't get the planner to take advantage of the  
> partitioning without also adding a key % 10 to the where clause.
> Is there any way around that?
>
> My child table definitions are:
>
> CREATE TABLE topic_version_page_0 (
>    CHECK (topic_version_id % 10 = 0::integer )
> ) inherits (topic_version_page);
>
> ...
>
> CREATE TABLE topic_version_page_9 (
>    CHECK (topic_version_id % 10 = 9::integer )
> ) inherits (topic_version_page);
>
>
> I've also created indexes and constraints for each child table, and  
> an insert trigger on the master table (topic_version_page).
>
> If I include a 'topic_version_id % 10 = [some value]' in my query,  
> then the partitioning shows up in the query plan:
>
> test=> explain select * from topic_version_page where  
> topic_version_id % 10 = (102 % 10) and topic_version_id = 102;
>                                                               QUERY  
> PLAN
> ---------------------------------------------------------------------- 
> ----------------------------------------------------------------
>  Result  (cost=4.27..19.23 rows=2 width=194)
>    ->  Append  (cost= 4.27..19.23 rows=2 width=194)
>          ->  Bitmap Heap Scan on topic_version_page   
> (cost=4.27..9.62 rows=1 width=194)
>                Recheck Cond: (topic_version_id = 102)
>                Filter: ((topic_version_id % 10) = 2)
>                ->  Bitmap Index Scan on  
> index_topic_version_page_on_topic_version_id_and_created_at   
> (cost=0.00..4.27 rows=2 width=0)
>                      Index Cond: (topic_version_id = 102)
>          ->  Bitmap Heap Scan on topic_version_page_2  
> topic_version_page  (cost=4.27..9.62 rows=1 width=194)
>                Recheck Cond: (topic_version_id = 102)
>                Filter: ((topic_version_id % 10) = 2)
>                ->  Bitmap Index Scan on  
> index_topic_version_page_2_on_topic_version_id_and_page_id   
> (cost=0.00..4.27 rows=2 width=0)
>                      Index Cond: (topic_version_id = 102)
> (12 rows)
>
> But if I don't explicitly include a  'topic_version_id % 10' -- the  
> plan gets much worse, checking every table (see below).
>
>
> test=> explain select * from topic_version_page where  
> topic_version_id = 102;
>                                                               QUERY  
> PLAN
> ---------------------------------------------------------------------- 
> ----------------------------------------------------------------
>  Result  (cost=4.27..105.68 rows=22 width=194)
>    ->  Append  (cost= 4.27..105.68 rows=22 width=194)
>          ->  Bitmap Heap Scan on topic_version_page   
> (cost=4.27..9.61 rows=2 width=194)
>                Recheck Cond: (topic_version_id = 102)
>                ->  Bitmap Index Scan on  
> index_topic_version_page_on_topic_version_id_and_created_at  (cost=  
> 0.00..4.27 rows=2 width=0)
>                      Index Cond: (topic_version_id = 102)
>          ->  Bitmap Heap Scan on topic_version_page_0  
> topic_version_page  (cost=4.27..9.61 rows=2 width=194)
>                Recheck Cond: (topic_version_id = 102)
>                ->  Bitmap Index Scan on  
> index_topic_version_page_0_on_topic_version_id_and_page_id   
> (cost=0.00..4.27 rows=2 width=0)
>                      Index Cond: (topic_version_id = 102)
>          ->  Bitmap Heap Scan on topic_version_page_1  
> topic_version_page  (cost=4.27..9.61 rows=2 width=194)
>                Recheck Cond: (topic_version_id = 102)
>                ->  Bitmap Index Scan on  
> index_topic_version_page_1_on_topic_version_id_and_page_id  (cost=  
> 0.00..4.27 rows=2 width=0)
>                      Index Cond: (topic_version_id = 102)
>          ->  Bitmap Heap Scan on topic_version_page_2  
> topic_version_page  (cost=4.27..9.61 rows=2 width=194)
>                Recheck Cond: (topic_version_id = 102)
>                ->  Bitmap Index Scan on  
> index_topic_version_page_2_on_topic_version_id_and_page_id   
> (cost=0.00..4.27 rows=2 width=0)
>                      Index Cond: (topic_version_id = 102)
>          ->  Bitmap Heap Scan on topic_version_page_3  
> topic_version_page  (cost=4.27..9.61 rows=2 width=194)
>                Recheck Cond: (topic_version_id = 102)
>                ->  Bitmap Index Scan on  
> index_topic_version_page_3_on_topic_version_id_and_page_id  (cost=  
> 0.00..4.27 rows=2 width=0)
>                      Index Cond: (topic_version_id = 102)
>          ->  Bitmap Heap Scan on topic_version_page_4  
> topic_version_page  (cost=4.27..9.61 rows=2 width=194)
>                Recheck Cond: (topic_version_id = 102)
>                ->  Bitmap Index Scan on  
> index_topic_version_page_4_on_topic_version_id_and_page_id   
> (cost=0.00..4.27 rows=2 width=0)
>                      Index Cond: (topic_version_id = 102)
>          ->  Bitmap Heap Scan on topic_version_page_5  
> topic_version_page  (cost=4.27..9.61 rows=2 width=194)
>                Recheck Cond: (topic_version_id = 102)
>                ->  Bitmap Index Scan on  
> index_topic_version_page_5_on_topic_version_id_and_page_id  (cost=  
> 0.00..4.27 rows=2 width=0)
>                      Index Cond: (topic_version_id = 102)
>          ->  Bitmap Heap Scan on topic_version_page_6  
> topic_version_page  (cost=4.27..9.61 rows=2 width=194)
>                Recheck Cond: (topic_version_id = 102)
>                ->  Bitmap Index Scan on  
> index_topic_version_page_6_on_topic_version_id_and_page_id   
> (cost=0.00..4.27 rows=2 width=0)
>                      Index Cond: (topic_version_id = 102)
>          ->  Bitmap Heap Scan on topic_version_page_7  
> topic_version_page  (cost=4.27..9.61 rows=2 width=194)
>                Recheck Cond: (topic_version_id = 102)
>                ->  Bitmap Index Scan on  
> index_topic_version_page_7_on_topic_version_id_and_page_id  (cost=  
> 0.00..4.27 rows=2 width=0)
>                      Index Cond: (topic_version_id = 102)
>          ->  Bitmap Heap Scan on topic_version_page_8  
> topic_version_page  (cost=4.27..9.61 rows=2 width=194)
>                Recheck Cond: (topic_version_id = 102)
>                ->  Bitmap Index Scan on  
> index_topic_version_page_8_on_topic_version_id_and_page_id   
> (cost=0.00..4.27 rows=2 width=0)
>                      Index Cond: (topic_version_id = 102)
>          ->  Bitmap Heap Scan on topic_version_page_9  
> topic_version_page  (cost=4.27..9.61 rows=2 width=194)
>                Recheck Cond: (topic_version_id = 102)
>                ->  Bitmap Index Scan on  
> index_topic_version_page_9_on_topic_version_id_and_page_id  (cost=  
> 0.00..4.27 rows=2 width=0)
>                      Index Cond: (topic_version_id = 102)
> (46 rows)
>
>
> Is there anyway to get the benefit of partitioning without adding a  
> additional 'topic_version_id % 10' condition to every query that  
> touches this table?
>
> Thanks in advance.

You beat me to the punch on this one.  I was wanting to use modulo  
operations for bin style partitioning as well, but this makes things  
pretty awkward as well as unintuitive.  So, to the postgres gurus:    
What are the limitations of check constraints when used with  
constraint exclusion?  Is this really the intended behavior?

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com