Re: Performance on Bulk Insert to Partitioned Table

________________________________
> Date: Sun, 23 Dec 2012 14:55:16 -0800
> Subject: Re: [PERFORM] Performance on Bulk Insert to Partitioned Table
> From: jeff(dot)janes(at)gmail(dot)com
> To: charlesrg(at)outlook(dot)com
> CC: ondrej(dot)ivanic(at)gmail(dot)com; pgsql-performance(at)postgresql(dot)org
>
> On Thursday, December 20, 2012, Charles Gomes wrote:
> True, that's the same I feel, I will be looking to translate the
> trigger to C if I can find good examples, that should accelerate.
>
> I think your performance bottleneck is almost certainly the dynamic
> SQL. Using C to generate that dynamic SQL isn't going to help much,
> because it is still the SQL engine that has to parse, plan, and execute
> it.
>
> Are the vast majority if your inserts done on any given day for records
> from that same day or the one before; or are they evenly spread over
> the preceding year? If the former, you could use static SQL in IF and
> ELSIF for those days, and fall back on the dynamic SQL for the
> exceptions in the ELSE block. Of course that means you have to update
> the trigger every day.
>
>
> Using rules would be totally bad as I'm partitioning daily and after
> one year having 365 lines of IF won't be fun to maintain.
>
> Maintaining 365 lines of IF is what Perl was invented for. That goes
> for triggers w/ static SQL as well as for rules.
>
> If you do the static SQL in a trigger and the dates of the records are
> evenly scattered over the preceding year, make sure your IFs are nested
> like a binary search, not a linear search. And if they are mostly for
> "today's" date, then make sure you search backwards.
>
> Cheers,
>
> Jeff

Jeff, I've changed the code from dynamic to:

CREATE OR REPLACE FUNCTION quotes_insert_trigger()
RETURNS trigger AS $$
DECLARE
r_date text;
BEGIN
r_date = to_char(new.received_time, 'YYYY_MM_DD');
case r_date
    when '2012_09_10' then
        insert into quotes_2012_09_10 values (NEW.*) using new;
        return;
    when '2012_09_11' then
        insert into quotes_2012_09_11 values (NEW.*) using new;
        return;
    when '2012_09_12' then
        insert into quotes_2012_09_12 values (NEW.*) using new;
        return;
    when '2012_09_13' then
        insert into quotes_2012_09_13 values (NEW.*) using new;
        return;
    when '2012_09_14' then
        insert into quotes_2012_09_14 values (NEW.*) using new;
        return;
    when '2012_09_15' then
        insert into quotes_2012_09_15 values (NEW.*) using new;
        return;
    when '2012_09_16' then
        insert into quotes_2012_09_16 values (NEW.*) using new;
        return;
    when '2012_09_17' then
        insert into quotes_2012_09_17 values (NEW.*) using new;
        return;
    when '2012_09_18' then
        insert into quotes_2012_09_18 values (NEW.*) using new;
        return;
    when '2012_09_19' then
        insert into quotes_2012_09_19 values (NEW.*) using new;
        return;
    when '2012_09_20' then
        insert into quotes_2012_09_20 values (NEW.*) using new;
        return;
    when '2012_09_21' then
        insert into quotes_2012_09_21 values (NEW.*) using new;
        return;
    when '2012_09_22' then
        insert into quotes_2012_09_22 values (NEW.*) using new;
        return;
    when '2012_09_23' then
        insert into quotes_2012_09_23 values (NEW.*) using new;
        return;
    when '2012_09_24' then
        insert into quotes_2012_09_24 values (NEW.*) using new;
        return;
end case
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

However I've got no speed improvement.
I need to keep two weeks worth of partitions at a time, that's why all the WHEN statements.
Wish postgres could automate the partition process natively like the other sql db.

Thank you guys for your help.