Table Partitioning vs non-Partitioned Table with many indexes

Question

I have a DB "DB_One" with a Master Table called t_d_gate_out with 8 indexes on it. I created another DB with partitioned t_d_gate_out (let's call it "DB_Two"). It was partitioned by month and year (example of child table: t_d_gate_out09-2013) which has two indexes (d_gate_out and new column on each child: i_trx_own)

This is my function of creating and inserting 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 inserting about 200k rows, I was trying to compare the speed of viewing data between those 2 DBs. The query I used to compare:

select * from ctm_test."t_d_gate_out"
where d_gate_out BETWEEN '2013-10-01' AND '2013-10-31'

The result after I tried to execute that query for several times, the execution times were ALMOST THE SAME. So the partitioned table was not effective to view data faster.
Was I doing the partition correctly?

Answer 1

200K rows isn't very many.

Partitioning mainly helps when your table is larger than RAM. and most of your queries can be served from a single table.