Fwd: Table Partitioning

From: "Nurlan Mukhanov" <nurike(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Fwd: Table Partitioning
Date: 2007-08-10 20:58:29
Message-ID: 1527ac090708101358p5eb6be11k43f23243bce54cd6@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello All.

I have a table with ca. 100.000.000 records. The main idea is make
Partitioning for this table (1000 or 10000 tables).
Let's take for example.

CREATE TABLE test
(
id integer,
data date not null default now()
)
WITHOUT OIDS;

CREATE TABLE test00 ( CHECK ( id%100 = 0 ) ) INHERITS (test);
CREATE TABLE test01 ( CHECK ( id%100 = 1 ) ) INHERITS (test);
...
CREATE TABLE test09 ( CHECK ( id%100 = 9 ) ) INHERITS (test);

-- RULES

CREATE OR REPLACE RULE test00 AS
ON INSERT TO test WHERE (NEW.id%100) = 0
DO INSTEAD INSERT INTO test00 (id) VALUES (NEW.id);

CREATE OR REPLACE RULE test01 AS
ON INSERT TO test WHERE (NEW.id%100) = 1
DO INSTEAD INSERT INTO test01 (id) VALUES (NEW.id);

...

CREATE OR REPLACE RULE test09 AS
ON INSERT TO test WHERE (NEW.id%100) = 9
DO INSTEAD INSERT INTO test09 (id) VALUES (NEW.id);

So the main algorithm is to take last digits of ID and put to special
table. Yes, it is work correct. But when I make a selection query
database ask all table instead of one.

"Aggregate (cost=134.17..134.18 rows=1 width=0)"
" -> Append (cost=4.33..133.94 rows=90 width=0)"
" -> Bitmap Heap Scan on test01 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test01_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test02 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test02_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test03 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test03_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test04 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test04_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test05 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test05_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test06 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test06_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test07 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test07_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test08 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test08_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test09 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test09_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"

If change CHECK to

CREATE TABLE test00 ( CHECK ( id = 0 ) ) INHERITS (test);

CREATE TABLE test01 ( CHECK ( id = 1 ) ) INHERITS (test);

... etc - everything work correct, only one table is asked for data.

But how to implement my idea if ID is always increment and have range
from 1 to BIGINT?
How it is possible or is there any variants to store different IDs in
separated tables when CHECK condition will be used during SELECT or
DELETE queries?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message smiley2211 2007-08-10 21:43:13 Re: How to ENABLE SQL capturing???
Previous Message Joel Fradkin 2007-08-10 20:36:46 Re: Dell Hardware Recommendations