Re: Fwd: Table Partitioning - Mailing list pgsql-performance
From | Henrik |
---|---|
Subject | Re: Fwd: Table Partitioning |
Date | |
Msg-id | 6EE7C97E-D542-495C-8DE5-0A04B38E6476@mac.se Whole thread Raw |
In response to | Fwd: Table Partitioning ("Nurlan Mukhanov" <nurike@gmail.com>) |
List | pgsql-performance |
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
pgsql-performance by date: