Re: partitioned table query question
От | Erik Jones |
---|---|
Тема | Re: partitioned table query question |
Дата | |
Msg-id | 416AC6E4-0E30-4269-AAA0-B4E385A2441B@myemma.com обсуждение исходный текст |
Ответ на | partitioned table query question ("Mason Hale" <masonhale@gmail.com>) |
Ответы |
Re: partitioned table query question
(Vivek Khera <vivek@khera.org>)
Re: partitioned table query question (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: