From: Merlin Moncure <mmoncure@gmail.com>
To: Davide Berra <d.berra@esitelsrl.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: xmlconcat performance
Date: Fri, 1 Mar 2013 08:09:25 -0600
Message-ID: <CAHyXU0zo9UGStdVWazyX1ASFixFpuc25n4-k28qBsdW8YpvmPQ@mail.gmail.com> (raw)
In-Reply-To: <51306444.1000302@esitelsrl.it>
References: <512738B3.4070309@esitelsrl.it>
<CAHyXU0y-ZfDSjzJhz7tFMdf-WYVAWOP7Lm2oryxevJexGqX7SA@mail.gmail.com>
<51306444.1000302@esitelsrl.it>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On Fri, Mar 1, 2013 at 2:18 AM, Davide Berra <d.berra@esitelsrl.it> wrote:
> Il 28/02/2013 18:48, Merlin Moncure ha scritto:
>
>> On Fri, Feb 22, 2013 at 3:21 AM, Davide Berra <d.berra@esitelsrl.it>
>> wrote:
>>>
>>> I got a problem with the performance of a PL/PGsql stored procedure
>>> outputting an xml.
>>>
>>> Server version: PostgreSQL 8.3.6 on i686-pc-linux-gnu, compiled by GCC
>>> gcc
>>> (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46)
>>> CPU: Intel(R) Core(TM) i3 CPU 540 @ 3.07GHz
>>> RAM installed: 4GB
>>> Hard Disk: Seagate 500Gb SATA 2
>>>
>>> This is a simplified content of the function showing the xmlconcat
>>> behaviour.
>>>
>>> CREATE OR REPLACE FUNCTION test_function (v_limit int)
>>> RETURNS xml AS
>>> $BODY$
>>> DECLARE
>>> v_xml xml;
>>> BEGIN
>>>
>>> FOR i IN 1..v_limit LOOP
>>> v_xml := xmlconcat(v_xml, xmlelement(name content, 'aaaaaaa'));
>>> END LOOP;
>>>
>>> RETURN v_xml ;
>>> END
>>> $BODY$
>>> LANGUAGE 'plpgsql' SECURITY DEFINER ;
>>>
>>>
>>> As long as the v_limit parameter grows (and then the size of the output
>>> xml,
>>> the time needed increase exponentially.
>>> Look at this examples:
>>>
>>> pang=# explain analyze select test_function(1000);
>>> QUERY PLAN
>>>
>>> --------------------------------------------------------------------------------------
>>> Result (cost=0.00..0.26 rows=1 width=0) (actual time=65.430..65.431
>>> rows=1
>>> loops=1)
>>> Total runtime: 65.457 ms
>>> (2 rows)
>>>
>>> pang=# explain analyze select test_function(5000);
>>> QUERY PLAN
>>>
>>> ----------------------------------------------------------------------------------------
>>> Result (cost=0.00..0.26 rows=1 width=0) (actual time=473.318..473.318
>>> rows=1 loops=1)
>>> Total runtime: 473.340 ms
>>> (2 rows)
>>>
>>> pang=# explain analyze select test_function(15000);
>>> QUERY PLAN
>>>
>>> ------------------------------------------------------------------------------------------
>>> Result (cost=0.00..0.26 rows=1 width=0) (actual
>>> time=4044.903..4044.904
>>> rows=1 loops=1)
>>> Total runtime: 4044.928 ms
>>> (2 rows)
>>>
>>> pang=# explain analyze select test_function(50000);
>>> QUERY PLAN
>>>
>>> --------------------------------------------------------------------------------------------
>>> Result (cost=0.00..0.26 rows=1 width=0) (actual
>>> time=94994.337..94994.369
>>> rows=1 loops=1)
>>> Total runtime: 94994.396 ms
>>> (2 rows)
>>>
>>> I already tried to update to 8.3.23 service version but i didn't see any
>>> improvement.
>>>
>>> Do you have any suggestion about how to increase the performance of
>>> xmlconcat?
>>>
>>> My need is to use stored procedures that calls xmlconcat more than 50000
>>> times, but it is unacceptable 94 seconds to complete the job.
>>>
>>> Thanks in advance
>>
>> typically for high performance string manipulation you have to do
>> things on more purely textual level and manipulate through arrays to
>> get really good performance. iterative string concatenation is
>> typically wrong approach -- you have to think in set terms.
>>
>> also your database version is obsolete -- time to start thinking about
>> upgrade.
>>
>> merlin
>>
> Thank you for the reply Merlin but i don't fully get what you mean. (sorry,
> i'm not a PostgreSQL expert)
> How would you change the above example function in order to improve
> performance?
> What do you mean with "manipulate through arrays"?
well arrays, or simple aggregation. for example:
select string_agg(v, '') from (select 'aaaaaaa'::text as v from
generate_series(1,50000)) q;
runs in ~ 30 ms.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mmoncure@gmail.com, d.berra@esitelsrl.it
Subject: Re: xmlconcat performance
In-Reply-To: <CAHyXU0zo9UGStdVWazyX1ASFixFpuc25n4-k28qBsdW8YpvmPQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox