On Thu, Jan 10, 2013 at 5:22 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
On 10.01.2013 21:11, Matheus de Oliveira 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
