On Thu, Jan 10, 2013 at 5:51 PM, Charles Gomes < charles.go...@benchmarksolutions.com> wrote:
> ** ** > > *From:* pgsql-performance-ow...@postgresql.org [mailto: > pgsql-performance-ow...@postgresql.org] *On Behalf Of *Matheus de Oliveira > *Sent:* Thursday, January 10, 2013 2:12 PM > *To:* Heikki Linnakangas > *Cc:* pgsql-performance; Charles Gomes > *Subject:* Re: [PERFORM] Partition insert trigger using C language**** > > ** ** > > ** ** > > On Thu, Jan 10, 2013 at 4:54 PM, Heikki Linnakangas < > hlinnakan...@vmware.com> wrote:**** > >> On 10.01.2013 20:45, Matheus de Oliveira wrote: >> >> 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. >> >> >> >> The right way to do this with SPI is to prepare each insert-statement on >> first invocation (SPI_prepare + SPI_keepplan), and reuse the plan after >> that (SPI_execute_with_args). >> >> If you construct and plan the query on every invocation, it's not >> surprising that it's no different from PL/pgSQL performance. >> >> >> Yeah. I thought about that, but the problem was that I assumed the >> INSERTs came with random date, so in the worst scenario I would have to >> keep the plans of all of the child partitions. Am I wrong? >> >> But thinking better, even with hundreds of partitions, it wouldn't use to >> much memory/resource, would it? >> >> In fact, I didn't give to much attention to SPI method, because the other >> one is where we can have more fun, =P. >> >> Anyway, I'll change the code (maybe now), and see if it gets closer to >> the other method (that uses heap_insert), and will post back the results >> here. >> > >> > > **** > > 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**** > > ** > Humm... Looking at your code, I saw no big difference from mine. The only thing I saw is that you don't fire triggers, but it would be even faster this way. Another thing that could cause that is the number of partitions, I tried only with 12. Could you make a test suite? Or try to run with my function in your scenario? It would be easy to make it get the partitions by day [1]. [1] https://gist.github.com/4509782 Regards, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres