Re: Performance on Bulk Insert to Partitioned Table

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 <sfrost(at)snowman(dot)net>

> * Jeff Janes (jeff(dot)janes(at)gmail(dot)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