On Thu, Jan 10, 2013 at 5:22 PM, Heikki Linnakangas <hlinnakan...@vmware.com > wrote:
> On 10.01.2013 21:11, Matheus de Oliveira wrote: > >> On Thu, Jan 10, 2013 at 4:54 PM, Heikki Linnakangas<hlinnakangas@** >> vmware.com <hlinnakan...@vmware.com> >> >>> wrote: >>> >> >> 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? >> > > Right, a few hundred saved plans would probably still be ok. And if that > ever becomes a problem, you could keep the plans in a LRU list and only > keep the last 100 plans or so. > > I have made a small modification to keep the plans, and it got from 33957.768ms to 43782.376ms. I'm not sure if I did something wrong/stupid on the code [1], or if something else broke my test. I can't rerun the test today, but I'll do that as soon as I have time. [1] https://github.com/matheusoliveira/pg_partitioning_tests/blob/master/src/spi/partition_insert_trigger_spi.c -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nĂvel F! www.dextra.com.br/postgres