Re: Performance on Bulk Insert to Partitioned Table

2012/12/28 Vitalii Tymchyshyn <tivv00(at)gmail(dot)com>:
> Why so? Basic form "case lvalue when rvalue then out ... end" is much like
> switch.
> The "case when condition then out ... end" is different, more complex beast,
> but first one is essentially a switch. If it is now trnasformed into
> "case when lvalue = rvalue1 then out1 when lvalue=rvalue2 then out2 ... end"
> then this can be optimized and this would benefit many users, not only ones
> that use partitioning.

please, look to plpgsql source code. PL/pgSQL is too simply and has
not own arithmetic unit - all is transformed to SELECTs, has not any
optimization. But is really short and maintainable.

These SELECTs are evaluated only when it is necessary - but it is
evaluated by PostgreSQL expression executor - not by PL/pgSQL directly
- PL/pgSQL cannot process constant by self.

So any enhancing needs PL/pgSQL redesign and I am not sure, so this
use case has accurate benefit, because expression bottleneck is only
one part of partitioning triggers bottleneck. More - if you need
really fast code, you can use own code in C - and it be 10x times
faster than any optimized PL/pgSQL code. And using C triggers in
PostgreSQL is not terrible work.

Using plpgsql row triggers for partitioning is not good idea - it is
just work around from my perspective, and we should to solve source of
problem - missing native support.

Regards

Pavel Stehule

>
>
> 2012/12/28 Stephen Frost <sfrost(at)snowman(dot)net>
>>
>> Vitalii,
>>
>> * Vitalii Tymchyshyn (tivv00(at)gmail(dot)com) wrote:
>> > There is switch-like sql case:
>> [...]
>> > It should work like C switch statement.
>>
>> It does and it doesn't. It behaves generally like a C switch statement,
>> but is much more flexible and therefore can't be optimized like a C
>> switch statement can be.
>>
>> Thanks,
>>
>> Stephen
>
>
>
>
> --
> Best regards,
> Vitalii Tymchyshyn