Search Postgresql Archives

partitioned table query question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

Mason

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux