Re: Performance on Bulk Insert to Partitioned Table

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 <tivv00(at)gmail(dot)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 <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
>

--
Best regards,
Vitalii Tymchyshyn