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? ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
