Обсуждение: partition insert performance
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.
Thank you
Sriram
Thank you
Sriram
On Mon, Jun 15, 2009 at 7:47 PM, Anj Adu <fotographs@gmail.com> wrote:
There will be some performance degradation, but you'll have to test measure it and decide if it is acceptable to you vs. the maintenance overhead.
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.
There will be some performance degradation, but you'll have to test measure it and decide if it is acceptable to you vs. the maintenance overhead.
Best regards,
--
Lets call it Postgres
EnterpriseDB http://www.enterprisedb.com
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device
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 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 anyOn 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.
null values in your inserts.
:) Using COALESCE isn't that bad.
--
Lets call it Postgres
EnterpriseDB http://www.enterprisedb.com
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device
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
Thanks to all for your advice. I will stay away from dynamic sql. The current implementation of static date comparisons in the trigger is lightning fast..(we have over 50 million inserts per day). I will bite the maintenance overhead as I cannot compromise on performance.
On Mon, Jun 15, 2009 at 11:35 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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 anyquote_nullable() would really be the right thing for inserts. However,
>>> null values in your inserts.
>>
>> :) Using COALESCE isn't that bad.
> In my experience it's WAY more than just coalesce.
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