Re: Fwd: Table Partitioning

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