Re: Performance on Bulk Insert to Partitioned Table

* 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