________________________________ > 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
