Content-Type: text/plain; charset=iso-8859-2
Mime-Version: 1.0 (Mac OS X Mail 6.2 \(1499\))
Subject: Re: Performance on Bulk Insert to Partitioned Table
From: Evgeny Shishkin <itparanoia@gmail.com>
In-Reply-To: <BLU002-W11801845E16CDDD1F773877AB3B0@phx.gbl>
Date: Mon, 24 Dec 2012 21:11:07 +0400
Cc: Jeff Janes <jeff.janes@gmail.com>,
	=?iso-8859-2?Q?Ondrej_Ivani=E8?= <ondrej.ivanic@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <CA08C4B0-CD63-4C1C-B709-0AD888524CCD@gmail.com>
References: <BLU002-W2777115565E1CBCF867857AB370@phx.gbl>,
	<CAM6mieLLD+WBx_NXubUihbDbHzb46tjxYWNWg_VgjV9omwOVcw@mail.gmail.com>,
	<BLU002-W73F8F8D02096BD549F2809AB370@phx.gbl>,
	<CAMkU=1xbdrp-HsPQ5kJZDxDTWa8NnvLiYB9kXiCYN8X9WSGv8Q@mail.gmail.com>,
	<BLU002-W20060321FFADD1AA11E2021AB3B0@phx.gbl>
	<BLU002-W11801845E16CDDD1F773877AB3B0@phx.gbl>
To: Charles Gomes <charlesrg@outlook.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On Dec 24, 2012, at 9:07 PM, Charles Gomes <charlesrg@outlook.com> wrote:

> By the way, I've just re-wrote the code to target the partitions individu=
ally and I've got almost 4 times improvement.
> Shouldn't it be faster to process the trigger, I would understand if ther=
e was no CPU left, but there is lots of cpu to chew.

I saw your 20% idle cpu and raise eyebrows.

> It seems that there will be no other way to speedup unless the insert cod=
e is partition aware.
>=20
> ----------------------------------------
>> From: charlesrg@outlook.com
>> To: jeff.janes@gmail.com
>> CC: ondrej.ivanic@gmail.com; pgsql-performance@postgresql.org
>> Subject: Re: [PERFORM] Performance on Bulk Insert to Partitioned Table
>> Date: Mon, 24 Dec 2012 10:51:12 -0500
>>=20
>> ________________________________
>>> Date: Sun, 23 Dec 2012 14:55:16 -0800
>>> Subject: Re: [PERFORM] Performance on Bulk Insert to Partitioned Table
>>> From: jeff.janes@gmail.com
>>> To: charlesrg@outlook.com
>>> CC: ondrej.ivanic@gmail.com; pgsql-performance@postgresql.org
>>>=20
>>> On Thursday, December 20, 2012, Charles Gomes wrote:
>>> True, that's the same I feel, I will be looking to translate the
>>> trigger to C if I can find good examples, that should accelerate.
>>>=20
>>> I think your performance bottleneck is almost certainly the dynamic
>>> SQL. Using C to generate that dynamic SQL isn't going to help much,
>>> because it is still the SQL engine that has to parse, plan, and execute
>>> it.
>>>=20
>>> Are the vast majority if your inserts done on any given day for records
>>> from that same day or the one before; or are they evenly spread over
>>> the preceding year? If the former, you could use static SQL in IF and
>>> ELSIF for those days, and fall back on the dynamic SQL for the
>>> exceptions in the ELSE block. Of course that means you have to update
>>> the trigger every day.
>>>=20
>>>=20
>>> Using rules would be totally bad as I'm partitioning daily and after
>>> one year having 365 lines of IF won't be fun to maintain.
>>>=20
>>> Maintaining 365 lines of IF is what Perl was invented for. That goes
>>> for triggers w/ static SQL as well as for rules.
>>>=20
>>> If you do the static SQL in a trigger and the dates of the records are
>>> evenly scattered over the preceding year, make sure your IFs are nested
>>> like a binary search, not a linear search. And if they are mostly for
>>> "today's" date, then make sure you search backwards.
>>>=20
>>> Cheers,
>>>=20
>>> Jeff
>>=20
>> Jeff, I've changed the code from dynamic to:
>>=20
>> CREATE OR REPLACE FUNCTION quotes_insert_trigger()
>> RETURNS trigger AS $$
>> DECLARE
>> r_date text;
>> BEGIN
>> r_date =3D to_char(new.received_time, 'YYYY_MM_DD');
>> case r_date
>>    when '2012_09_10' then
>>        insert into quotes_2012_09_10 values (NEW.*) using new;
>>        return;
>>    when '2012_09_11' then
>>        insert into quotes_2012_09_11 values (NEW.*) using new;
>>        return;
>>    when '2012_09_12' then
>>        insert into quotes_2012_09_12 values (NEW.*) using new;
>>        return;
>>    when '2012_09_13' then
>>        insert into quotes_2012_09_13 values (NEW.*) using new;
>>        return;
>>    when '2012_09_14' then
>>        insert into quotes_2012_09_14 values (NEW.*) using new;
>>        return;
>>    when '2012_09_15' then
>>        insert into quotes_2012_09_15 values (NEW.*) using new;
>>        return;
>>    when '2012_09_16' then
>>        insert into quotes_2012_09_16 values (NEW.*) using new;
>>        return;
>>    when '2012_09_17' then
>>        insert into quotes_2012_09_17 values (NEW.*) using new;
>>        return;
>>    when '2012_09_18' then
>>        insert into quotes_2012_09_18 values (NEW.*) using new;
>>        return;
>>    when '2012_09_19' then
>>        insert into quotes_2012_09_19 values (NEW.*) using new;
>>        return;
>>    when '2012_09_20' then
>>        insert into quotes_2012_09_20 values (NEW.*) using new;
>>        return;
>>    when '2012_09_21' then
>>        insert into quotes_2012_09_21 values (NEW.*) using new;
>>        return;
>>    when '2012_09_22' then
>>        insert into quotes_2012_09_22 values (NEW.*) using new;
>>        return;
>>    when '2012_09_23' then
>>        insert into quotes_2012_09_23 values (NEW.*) using new;
>>        return;
>>    when '2012_09_24' then
>>        insert into quotes_2012_09_24 values (NEW.*) using new;
>>        return;
>> end case
>> RETURN NULL;
>> END;
>> $$
>> LANGUAGE plpgsql;
>>=20
>>=20
>> However I've got no speed improvement.
>> I need to keep two weeks worth of partitions at a time, that's why all t=
he WHEN statements.
>> Wish postgres could automate the partition process natively like the oth=
er sql db.
>>=20
>> Thank you guys for your help.
>>=20
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.or=
g)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance=20=09=09=20=09=20=
=20=20=09=09=20=20
>=20
> --=20
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance