Re: Performance on Bulk Insert to Partitioned Table

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.

I understand, but I am not happy with it. CASE is relative complex.
There is SQL CASE too, and this is third variant of CASE. Maybe some
simple CASE statements can be supported by parser and there should be
local optimization (probably only for numeric - without casting) But
it needs relative lot of new code? Will be this code accepted?

Regards

Pavel

>
> Thanks,
>
> Stephen