Re: Partition insert trigger using C language

On Thu, Jan 10, 2013 at 5:22 PM, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com
> wrote:

> On 10.01.2013 21:11, Matheus de Oliveira wrote:
>
>> On Thu, Jan 10, 2013 at 4:54 PM, Heikki Linnakangas<hlinnakangas(at)**
>> vmware.com <hlinnakangas(at)vmware(dot)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