________________________________ > From: matioli.matheus@xxxxxxxxx > Date: Thu, 10 Jan 2013 16:45:43 -0200 > Subject: Partition insert trigger using C language > To: pgsql-performance@xxxxxxxxxxxxxx > CC: charlesrg@xxxxxxxxxxx > > Hi All, > > Inspired by Charles' thread and the work of Emmanuel [1], I have made > some experiments trying to create a trigger to make partitioning using > C language. > > The first attempt was not good, I tried to use SPI [2] to create a > query to insert into the correct child table, but it took almost no > improvement compared with the PL/pgSQL code. > > Then, I used the Emmanuel's code and digged into the PG source code > (basically at copy.c) to create a trigger function that insert to the > partitioned table direct (using heap_insert instead of SQL) [3], and > the improvement was about 100% (not 4/5 times like got by Emmanuel ). > The function has no other performance trick, like caching the relations > or something like that. > > The function does partition based on month/year, but it's easy to > change to day/month/year or something else. And, of course, it's not > ready for production, as I'm not sure if it can break things. > > The tests were made using a PL/pgSQL code to insert 1 milion rows, and > I don't know if this is a real-life-like test (probably not). And there > is a table partitioned by month, with a total of 12 partitions (with > the insertions randomly distributed through all 2012). > > I put the trigger and the experiments on a repository at GitHub: > > https://github.com/matheusoliveira/pg_partitioning_tests > > I don't know if this is the right list for the topic, and I thought the > old one has to many messages, so I created this one to show this > tirgger sample and see if someone has a comment about it. > > PS: I'd be glad if someone could revise the code to make sure it don't > brake in some corner case. I'm made some tests [4], but not sure if > they covered everything. > PS2: It surely will not work on old versions of PostgreSQL, perhaps not > even 9.1 (not tested). > > > [1] http://archives.postgresql.org/pgsql-hackers/2008-12/msg01221.php > and > http://archives.postgresql.org/pgsql-performance/2012-12/msg00189.php > [2] > https://github.com/matheusoliveira/pg_partitioning_tests/blob/master/src/spi/partition_insert_trigger_spi.c > [3] > https://github.com/matheusoliveira/pg_partitioning_tests/blob/master/src/partition_insert_trigger.c > [4] > https://github.com/matheusoliveira/pg_partitioning_tests/tree/master/test/regress > > > Regards, > -- > Matheus de Oliveira > Analista de Banco de Dados > Dextra Sistemas - MPS.Br nível F! > www.dextra.com.br/postgres<http://www.dextra.com.br/postgres/> Interesting that you got an improvement. In my case I get almost no improvement at all: PL/SQL – Dynamic Trigger 4:15:54 PL/SQL - CASE / WHEN Statements 4:12:29 PL/SQL - IF Statements 4:12:39 C Trigger 4:10:49 Here is my code, I’m using heap insert and updating the indexes. With a similar approach of yours. The trigger is aware of http://www.charlesrg.com/~charles/pgsql/partition2.c -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
