Thread: partition insert performance

On Mon, Jun 15, 2009 at 8:17 AM, Anj Adu<fotographs@gmail.com> wrote:
> We currently use triggers with static if..then..else statements to determine
> which partition the data goes into. Would there be a performance impact if
> we were to use dynamic sql to achieve the same ?. The issue with static
> triggers is the maintenance overhead of having to redefine the triggers
> periodically. The dynamic sql is simple and involves building a sql string
> with just a to_char(date_Field) to determine which partition the data goes
> into and the trigger will never have to be re-defined.

If you're using plpgsql prepare for a world of pain if you've got any
null values in your inserts.

On Mon, Jun 15, 2009 at 11:35 AM, Gurjeet Singh<singh.gurjeet@gmail.com> wrote:
> On Mon, Jun 15, 2009 at 10:57 PM, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> On Mon, Jun 15, 2009 at 8:17 AM, Anj Adu<fotographs@gmail.com> wrote:
>> > We currently use triggers with static if..then..else statements to
>> > determine
>> > which partition the data goes into. Would there be a performance impact
>> > if
>> > we were to use dynamic sql to achieve the same ?. The issue with static
>> > triggers is the maintenance overhead of having to redefine the triggers
>> > periodically. The dynamic sql is simple and involves building a sql
>> > string
>> > with just a to_char(date_Field) to determine which partition the data
>> > goes
>> > into and the trigger will never have to be re-defined.
>>
>> If you're using plpgsql prepare for a world of pain if you've got any
>> null values in your inserts.
>
> :) Using COALESCE isn't that bad.

In my experience it's WAY more than just coalesce.

Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Mon, Jun 15, 2009 at 11:35 AM, Gurjeet Singh<singh.gurjeet@gmail.com> wrote:
>> On Mon, Jun 15, 2009 at 10:57 PM, Scott Marlowe <scott.marlowe@gmail.com>
>> wrote:
>>> If you're using plpgsql prepare for a world of pain if you've got any
>>> null values in your inserts.
>>
>> :) Using COALESCE isn't that bad.

> In my experience it's WAY more than just coalesce.

quote_nullable() would really be the right thing for inserts.  However,
I think the short answer to the OP's question is that dynamic SQL will
lose big-time performancewise.

            regards, tom lane

Hi,

Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Mon, Jun 15, 2009 at 11:35 AM, Gurjeet Singh<singh.gurjeet@gmail.com> wrote:
>> On Mon, Jun 15, 2009 at 10:57 PM, Scott Marlowe <scott.marlowe@gmail.com>
>> wrote:
>>> If you're using plpgsql prepare for a world of pain if you've got any
>>> null values in your inserts.
>>
>> :) Using COALESCE isn't that bad.
>
> In my experience it's WAY more than just coalesce.

There's this trick:

    v_sql := 'INSERT INTO schema.parent_' || to_char(NEW.date, 'YYYYMM') ||
             '     SELECT (' || quote_literal(textin(record_out(NEW))) || '::schema.parent).*;';

    EXECUTE v_sql;

This will handle dynamic SQL and NULLs correctly, but as said already
won't match static SQL in term of performances. Just saying for people
searching simplifications.

Regards,
--
dim