Re: Performance on Bulk Insert to Partitioned Table - Mailing list pgsql-performance

Pavel,

I've been trying to port the work of Emmanuel
http://archives.postgresql.org/pgsql-hackers/2008-12/msg01221.php


His implementation is pretty straight forward. Simple trigger doing constrain checks with caching for bulk inserts.
So far that's what I got http://www.widesol.com/~charles/pgsql/partition.c
I had some issues as He uses HeapTuples and on 9.2 I see a Slot.


----------------------------------------
> From: pavel.stehule@gmail.com
> Date: Thu, 27 Dec 2012 19:46:12 +0100
> Subject: Re: [PERFORM] Performance on Bulk Insert to Partitioned Table
> To: jeff.janes@gmail.com
> CC: charlesrg@outlook.com; ondrej.ivanic@gmail.com; pgsql-performance@postgresql.org
>
> 2012/12/27 Jeff Janes <jeff.janes@gmail.com>:
> > On Wednesday, December 26, 2012, Pavel Stehule wrote:
> >>
> >> 2012/12/27 Jeff Janes <jeff.janes@gmail.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.
>
> a bottleneck is not in PL/pgSQL directly. It is in PostgreSQL
> expression executor. Personally I don't see any simple optimization -
> maybe some variant of JIT (for expression executor) should to improve
> performance.
>
> Any other optimization require significant redesign PL/pgSQL what is
> job what I don't would do now - personally, it is not work what I
> would to start by self, because using plpgsql triggers for
> partitioning is bad usage of plpgsql - and I believe so after native
> implementation any this work will be useless. Design some generic C
> trigger or really full implementation is better work.
>
> More, there is still expensive INSERT statement - forwarding tuple on
> C level should be significantly faster - because it don't be generic.
>
> >
> >
> >>
> >>
> >> 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.)
>
> Anybody who starts work on native implementation will have my support
> (it is feature that lot of customers needs). I have customers that can
> support development and I believe so there are others. Actually It
> needs only one tenacious man, because it is work for two years.
>
> Regards
>
> Pavel
>
> >
> >
> > Cheers,
> >
> > Jeff
> >>
> >>
> >
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance