On Dec 24, 2012, at 9:07 PM, Charles Gomes <charlesrg@xxxxxxxxxxx> wrote: > By the way, I've just re-wrote the code to target the partitions individually and I've got almost 4 times improvement. > Shouldn't it be faster to process the trigger, I would understand if there was no CPU left, but there is lots of cpu to chew. I saw your 20% idle cpu and raise eyebrows. > It seems that there will be no other way to speedup unless the insert code is partition aware. > > ---------------------------------------- >> From: charlesrg@xxxxxxxxxxx >> To: jeff.janes@xxxxxxxxx >> CC: ondrej.ivanic@xxxxxxxxx; pgsql-performance@xxxxxxxxxxxxxx >> Subject: Re: Performance on Bulk Insert to Partitioned Table >> Date: Mon, 24 Dec 2012 10:51:12 -0500 >> >> ________________________________ >>> Date: Sun, 23 Dec 2012 14:55:16 -0800 >>> Subject: Re: Performance on Bulk Insert to Partitioned Table >>> From: jeff.janes@xxxxxxxxx >>> To: charlesrg@xxxxxxxxxxx >>> CC: ondrej.ivanic@xxxxxxxxx; pgsql-performance@xxxxxxxxxxxxxx >>> >>> 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. >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
