On Tuesday, September 3, 2013, Gregory Haase wrote: > I am working on a date-based partitioning framework and I would really > like to have a single function that could be used as trigger for any table > that needs to be partitioned by day. I am working in a rails environment, > so every table has a created_at datetime field. > > I created my generic function: > > create or replace function day_partition_insert_trigger() > returns trigger as $$ > declare > ins_tbl varchar; > begin > ins_tbl := TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || '_' || > to_char(NEW.created_at,'YYYYMMDD'); > execute 'insert into '|| ins_tbl ||' select ($1).*' using NEW; > return null; > end; > $$ language plpgsql; > > ... >
> I began to wonder if there would be a performance degradation, so I > changed the testdailytwo trigger function the typical if, elsif described > in the partitioning documentation and then ran pgbench against both tables. > > I noticed that with 7 partitions, the if, elsif was slightly faster (~8%). > However, when adding 30 partitions, the if, elsif version became slower. > I'd sort of expected this. > Did you try an if, elsif, version structured like a binary search rather than a linear search? Also, did you try them with a \copy rather than insert in a loop? Cheers, Jeff >
