Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UB6xy-0003vb-Os for pgsql-performance@arkaria.postgresql.org; Thu, 28 Feb 2013 17:08:22 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UB6xx-000135-Fr for pgsql-performance@arkaria.postgresql.org; Thu, 28 Feb 2013 17:08:21 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8opR-0007oJ-3l for pgsql-performance@postgresql.org; Fri, 22 Feb 2013 09:22:05 +0000 Received: from mr004msb.fastweb.it ([85.18.95.88]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8opN-0004xJ-Fy for pgsql-performance@postgresql.org; Fri, 22 Feb 2013 09:22:04 +0000 Received: from [192.168.4.105] (10.52.72.142) by mr004msb.fastweb.it (8.5.140.03) (authenticated as d.berra@esitelsrl.it) id 50F41D91008AAD74 for pgsql-performance@postgresql.org; Fri, 22 Feb 2013 10:21:58 +0100 Message-ID: <512738B3.4070309@esitelsrl.it> Date: Fri, 22 Feb 2013 10:21:55 +0100 From: Davide Berra User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20130215 Thunderbird/17.0.3 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: xmlconcat performance Content-Type: multipart/alternative; boundary="------------090606070309070004010901" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------090606070309070004010901 Content-Type: text/plain; charset=ISO-8859-15; format=flowed Content-Transfer-Encoding: 7Bit 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 --------------090606070309070004010901 Content-Type: text/html; charset=ISO-8859-15 Content-Transfer-Encoding: Quoted-Printable

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=A0 @ 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)
=A0=A0=A0 RETURNS xml AS
$BODY$
DECLARE
=A0=A0=A0 v_xml xml;
BEGIN

=A0=A0=A0 FOR i IN 1..v_limit LOOP
=A0=A0=A0=A0=A0=A0=A0 v_xml :=3D xmlconcat(v_xml, xmlelement(name c= ontent, 'aaaaaaa'));
=A0=A0=A0 END LOOP;

=A0=A0=A0 RETURN v_xml ;
END
$BODY$
=A0=A0=A0 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=3D# explain analyze select test_function(1000);
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN
-------------------------------------------------------------------------= -------------
=A0Result=A0 (cost=3D0.00..0.26 rows=3D1 width=3D0) (actual time=3D65.430..65.431 rows=3D1 loops=3D1)
=A0Total runtime: 65.457 ms
(2 rows)

pang=3D# explain analyze select test_function(5000);
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN
-------------------------------------------------------------------------= ---------------
=A0Result=A0 (cost=3D0.00..0.26 rows=3D1 width=3D0) (actual time=3D473.318..473.318 rows=3D1 loops=3D1)
=A0Total runtime: 473.340 ms
(2 rows)

pang=3D# explain analyze select test_function(15000);
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN
-------------------------------------------------------------------------= -----------------
=A0Result=A0 (cost=3D0.00..0.26 rows=3D1 width=3D0) (actual time=3D4044.903..4044.904 rows=3D1 loops=3D1)
=A0Total runtime: 4044.928 ms
(2 rows)

pang=3D# explain analyze select test_function(50000);
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN
-------------------------------------------------------------------------= -------------------
=A0Result=A0 (cost=3D0.00..0.26 rows=3D1 width=3D0) (actual time=3D94994.337..94994.369 rows=3D1 loops=3D1)
=A0Total runtime: 94994.396 ms
(2 rows)

I already tried to update to 8.3.23 service version but i didn't see any improvement.=A0

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

--------------090606070309070004010901--