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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
