On 12/21/2012 08:39 AM, Merlin Moncure wrote:
> On Fri, Dec 21, 2012 at 10:25 AM, Charles Gomes <charlesrg@outlook.com> wrote:
>> Hello guys,
>>
>> I've been finding performance issues when using a trigger to modify inserts on a partitioned table.
>> If using the trigger the total time goes from 1 Hour to 4 hours.
>>
>> The trigger is pretty simple:
>>
>> CREATE OR REPLACE FUNCTION quotes_insert_trigger()
>> RETURNS trigger AS $$
>> BEGIN
>> EXECUTE 'INSERT INTO quotes_'|| to_char(new.received_time,'YYYY_MM_DD') ||' VALUES (($1).*)' USING NEW ;
>> RETURN NULL;
>> END;
>> $$
>> LANGUAGE plpgsql;
>>
>> I've seen that some of you guys have worked on writing triggers in C.
>>
>> Does anyone have had an experience writing a trigger for partitioning in C ?
>>
>> If you have some code to paste so I can start from I will really appreciate.
>
> Honestly I'd leave the trigger alone and modify the client code in
> performance sensitive places to insert directly to the correct
> partition table.
I second that recommendation -- your performance will be much, much, better.
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support