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?
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 |