partitioned table query question

From: "Mason Hale" <masonhale(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: partitioned table query question
Date: 2007-12-08 04:51:28
Message-ID: 8bca3aa10712072051t3c75129dn3b3df643a32a4b6d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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.27rows=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.27rows=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.27rows=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.27rows=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.27rows=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.27rows=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.27rows=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.27rows=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.27rows=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.27rows=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.27rows=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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Smith 2007-12-08 06:10:28 Re: record-based log shipping
Previous Message Alex Vinogradovs 2007-12-08 03:54:12 Re: record-based log shipping

Browse pgsql-hackers by date

  From Date Subject
Next Message Gregory Stark 2007-12-08 05:45:53 Re: pg_controldata doesn't report 64/32bit?
Previous Message Jonah H. Harris 2007-12-08 03:50:24 Re: [DOCS] "distributed checkpoint"