BTW: If "select count(*) from new" is fast, you can even choose the strategy in trigger depending on insert size.
2012/12/28 Vitalii Tymchyshyn <tiv...@gmail.com> > There is switch-like sql case: > 39.6.2.4. Simple CASE > > CASE search-expression > WHEN expression [, expression [ ... ]] THEN > statements > [ WHEN expression [, expression [ ... ]] THEN > statements > ... ] > [ ELSE > statements ] > END CASE; > > It should work like C switch statement. > > Also, for bulk insert, have you tried "for each statement" triggers > instead of "for each row"? > This would look like a lot of inserts and would not be fast in > single-row-insert case, but can give you benefit for huge inserts. > It should look like > insert into quotes_2012_09_10 select * from new where > cast(new.received_time as date) = '2012-09-10' ; > insert into quotes_2012_09_11 select * from new where > cast(new.received_time as date) = '2012-09-11' ; > ... > > 2012/12/27 Stephen Frost <sfr...@snowman.net> > >> * Jeff Janes (jeff.ja...@gmail.com) wrote: >> > If the main goal is to make it faster, I'd rather see all of plpgsql get >> > faster, rather than just a special case of partitioning triggers. For >> > example, right now a CASE <expression> statement with 100 branches is >> about >> > the same speed as an equivalent list of 100 elsif. So it seems to be >> doing >> > a linear search, when it could be doing a hash that should be a lot >> faster. >> >> That's a nice thought, but I'm not sure that it'd really be practical. >> CASE statements in plpgsql are completely general and really behave more >> like an if/elsif tree than a C-style switch() statement or similar. For >> one thing, the expression need not use the same variables, could be >> complex multi-variable conditionals, etc. >> >> Figuring out that you could build a dispatch table for a given CASE >> statement and then building it, storing it, and remembering to use it, >> wouldn't be cheap. >> >> On the other hand, I've actually *wanted* a simpler syntax on occation. >> I have no idea if there'd be a way to make it work, but this would be >> kind of nice: >> >> CASE OF x -- or whatever >> WHEN 1 THEN blah blah >> WHEN 2 THEN blah blah >> WHEN 3 THEN blah blah >> END >> >> which would be possible to build into a dispatch table by looking at the >> type of x and the literals used in the overall CASE statement. Even so, >> there would likely be some number of WHEN conditions required before >> it'd actually be more efficient to use, though perhaps getting rid of >> the expression evaluation (if that'd be possible) would make up for it. >> >> Thanks, >> >> Stephen >> > > > > -- > Best regards, > Vitalii Tymchyshyn > -- Best regards, Vitalii Tymchyshyn
