From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Gregory Haase <haaseg(at)onefreevoice(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Generic function for partitioning function? |
Date: | 2013-09-04 02:10:49 |
Message-ID: | CAMkU=1wJGY_-9cxBt3BCqP-L=2TcwM4=FHuHo8sG-oN-KQJHxw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2013-09-04 02:12:56 | Re: store multiple rows with the SELECT INTO statement |
Previous Message | Adrian Klaver | 2013-09-04 02:06:49 | Re: store multiple rows with the SELECT INTO statement |