Performance on Bulk Insert to Partitioned Table

On Wednesday, December 26, 2012, Pavel Stehule wrote:

> 2012/12/27 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:
> >
> > More automated would be nice (i.e. one operation to make both the check
> > constraints and the trigger, so they can't get out of sync), but would
> not
> > necessarily mean faster.
>
>
<snip some benchmarking>

Native implementation should significantly effective evaluate

expressions, mainly simple expressions - (this is significant for
> large number of partitions) and probably can do tuple forwarding
> faster than is heavy INSERT statement (is question if is possible
> decrease some overhead with more sophisticate syntax (by removing
> record expand).
>

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.

>
> So native implementation can carry significant speed up - mainly if we
> can distribute tuples without expression evaluating (evaluated by
> executor)
>

Making partitioning inserts native does open up other opportunities to make
it faster, and also to make it administratively easier; but do we want to
try to tackle both of those goals simultaneously? I think the
administrative aspects would come first. (But I doubt I will be the one to
implement either, so my vote doesn't count for much here.)

Cheers,

Jeff

>
>