From: Davide Berra <d.berra@esitelsrl.it>
To: pgsql-performance@postgresql.org
Subject: xmlconcat performance
Date: Fri, 22 Feb 2013 10:21:55 +0100
Message-ID: <512738B3.4070309@esitelsrl.it> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: d.berra@esitelsrl.it
Subject: Re: xmlconcat performance
In-Reply-To: <512738B3.4070309@esitelsrl.it>
* 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