From: Julius Tuskenis <julius@nsoft.lt>
To: pgsql-performance@postgresql.org
Subject: Re: generating a large XML document
Date: Mon, 20 Jun 2011 12:03:21 +0300
Message-ID: <4DFF0CD9.9010805@nsoft.lt> (raw)
In-Reply-To: <BANLkTim2+pQQz7wCaSbt5M3YrfGQtSVK0A@mail.gmail.com>
References: <4DFA5AA4.8090001@nsoft.lt>
<4DFEEA70.6000009@nsoft.lt>
<BANLkTi=q_yXPWCjCZ37Mtf6n+qORN7Vtbg@mail.gmail.com>
<4DFEF6E9.90404@nsoft.lt>
<BANLkTim-XZQ3_rDP0WSo3iY9Moi8k-tsvQ@mail.gmail.com>
<BANLkTim2+pQQz7wCaSbt5M3YrfGQtSVK0A@mail.gmail.com>
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
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: julius@nsoft.lt
Subject: Re: generating a large XML document
In-Reply-To: <4DFF0CD9.9010805@nsoft.lt>
* 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