10 aug 2007 kl. 22:58 skrev Nurlan Mukhanov:
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
I'm not sure this will make any difference but are you using SET
constraint_exclusion = on; ?
.
"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.
Are your first check algorithm causing overlaps?
Cheers,
henrik
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?
---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
