2012/12/27 Jeff Janes <jeff.janes@xxxxxxxxx>:
>
> 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
