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@xxxxxxxxx > Date: Thu, 27 Dec 2012 19:46:12 +0100 > Subject: Re: Performance on Bulk Insert to Partitioned Table > To: jeff.janes@xxxxxxxxx > CC: charlesrg@xxxxxxxxxxx; ondrej.ivanic@xxxxxxxxx; pgsql-performance@xxxxxxxxxxxxxx > > 2012/12/27 Jeff Janes <jeff.janes@xxxxxxxxx>: > > On Wednesday, December 26, 2012, Pavel Stehule wrote: > >> > >> 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. > > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
