Re: Generic function for partitioning function?

[Jeff Janes <jeff.janes@xxxxxxxxx>]

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

>