Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 20F36B5DBCC for ; Mon, 20 Jun 2011 06:03:34 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 50432-05 for ; Mon, 20 Jun 2011 09:03:28 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.csc.lt (mail.csc.lt [193.138.191.5]) by mail.postgresql.org (Postfix) with ESMTP id 40D0DB5DBC2 for ; Mon, 20 Jun 2011 06:03:25 -0300 (ADT) Received: (qmail 4896 invoked by uid 210); 20 Jun 2011 09:03:21 -0000 Received: from mail.nsoft.lt by mail (envelope-from , uid 201) with qmail-scanner-2.05st (clamdscan: 0.97/13214. spamassassin: 3.2.1. perlscan: 2.05st. Clear:RC:1(77.240.249.53):. Processed in 0.011175 secs); 20 Jun 2011 09:03:21 -0000 Received: from mail.nsoft.lt (77.240.249.53) by mail.csc.lt with SMTP; 20 Jun 2011 09:03:21 -0000 Received: from [192.168.16.113] (unknown [77.240.249.53]) (using TLSv1 with cipher DHE-RSA-CAMELLIA256-SHA (256/256 bits)) (No client certificate requested) by mail.nsoft.lt (Postfix) with ESMTPS id B4EAA8E2AB for ; Mon, 20 Jun 2011 12:03:19 +0300 (EEST) Message-ID: <4DFF0CD9.9010805@nsoft.lt> Date: Mon, 20 Jun 2011 12:03:21 +0300 From: Julius Tuskenis Organization: UAB nSoft User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; lt-LT; rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: generating a large XML document References: <4DFA5AA4.8090001@nsoft.lt> <4DFEEA70.6000009@nsoft.lt> <4DFEF6E9.90404@nsoft.lt> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201106/176 X-Sequence-Number: 44109 2011.06.20 10:58, Pavel Stehule rašė: > string_agg is more effective now. The solution is only radical > refactoring of xmlagg function. Thank you, Pavel for letting me know of string_agg. explain analyze SELECT string_agg( XMLELEMENT ( NAME "bar", XMLELEMENT ( NAME "code", tick_barcode), XMLELEMENT ( NAME "stat", status), CASE WHEN type IS NOT NULL THEN XMLELEMENT ( NAME "tip", type) ELSE NULL END, CASE WHEN sec_name IS NOT NULL THEN XMLELEMENT ( NAME "sec", sec_name) ELSE NULL END, CASE WHEN row_name IS NOT NULL THEN XMLELEMENT ( NAME "row", row_name) ELSE NULL END, CASE WHEN seat_name IS NOT NULL THEN XMLELEMENT ( NAME "plc", seat_name) ELSE NULL END, CASE WHEN substr(tick_barcode,length(tick_barcode),1)= '1' THEN XMLELEMENT ( NAME "groups", XMLELEMENT ( NAME "group", 1) ) ELSE NULL END )::text, NULL )::xml FROM tex.fnk_access_control_tickets(8560, 0); "Aggregate (cost=12.75..12.77 rows=1 width=238) (actual time=1025.502..1025.502 rows=1 loops=1)" " -> Function Scan on fnk_access_control_tickets (cost=0.25..10.25 rows=1000 width=238) (actual time=495.703..503.999 rows=16292 loops=1)" "Total runtime: 1036.775 ms" Its over 10 times faster than using xmlagg. -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050