I have a DB "DB_One" with a main table called t_d_gate_outwith 8 indexes on it. I created another partitioned database t_d_gate_out(name it "DB_Two"). It was divided into the month and year (example of the child table: t_d_gate_out09-2013), which has two indexes (d _gate_outand a new column for each child: i_trx_own)
This is my function to create and insert child tables:
CREATE OR REPLACE FUNCTION ctm_test.gateout_partition_function()
RETURNS trigger AS
$BODY$
DECLARE new_time text;
tablename text;
seqname text;
seqname_schema text;
bulantahun text;
bulan text;
bulan2 text;
tahun text;
enddate text;
result record;
BEGIN new_time := to_char(NEW.d_gate_out,'MM-YYYY');
bulan:=to_char(NEW.d_gate_out,'MM');
bulan2:=extract(month from NEW.d_gate_out);
tahun:=to_char(NEW.d_gate_out,'YYYY');
bulantahun := new_time;
tablename := 't_d_gate_out'||bulantahun;
seqname := 't_d_gate_out'||bulantahun||'_seq';
seqname_schema := 'ctm_test.t_d_gate_out'||bulantahun||'_seq';
PERFORM 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE
c.relkind = 'r' AND c.relname = tablename AND n.nspname = 'ctm_test';
IF NOT FOUND THEN EXECUTE 'CREATE TABLE ctm_test.' || quote_ident(tablename) || ' ( i_trx_own
serial PRIMARY KEY, CHECK (extract(month from d_gate_out)=' || bulan2 || ' AND extract(year from
d_gate_out)=' || tahun || ')) INHERITS (ctm_test.t_d_gate_out)';
EXECUTE 'ALTER TABLE ctm_test.' || quote_ident(tablename) || ' OWNER TO postgres'; EXECUTE 'GRANT
ALL ON TABLE ctm_test.' || quote_ident(tablename) || ' TO postgres';
EXECUTE 'CREATE INDEX ' || quote_ident(tablename||'_indx1') || ' ON ctm_test.' || quote_ident
(tablename) || ' (i_trx_own);CREATE INDEX ' || quote_ident(tablename||'_indx2') || ' ON ctm_test.' || quote_ident
(tablename) || ' (d_gate_out)'; END IF;
EXECUTE 'INSERT INTO ctm_test.' || quote_ident(tablename) || ' VALUES ($1.*)' USING NEW; RETURN
NULL; END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION ctm_test.gateout_partition_function()
OWNER TO postgres;
And this is my TRIGGER:
CREATE TRIGGER gateout_master_trigger
BEFORE INSERT
ON ctm_test.t_d_gate_out
FOR EACH ROW
EXECUTE PROCEDURE ctm_test.gateout_partition_function();
After entering about 200 thousand rows, I tried to compare the speed of viewing data between these two databases. The query I used for comparison:
select * from ctm_test."t_d_gate_out"
where d_gate_out BETWEEN '2013-10-01' AND '2013-10-31'
, , . , .
?