Skip site navigation (1) Skip section navigation (2)

Re: generating a large XML document

From: Julius Tuskenis <julius(at)nsoft(dot)lt>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: generating a large XML document
Date: 2011-06-20 09:03:21
Message-ID: 4DFF0CD9.9010805@nsoft.lt (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

pgsql-performance by date

Next:From: Claudio FreireDate: 2011-06-20 09:45:00
Subject: Re: how to know slowly query in lock postgre
Previous:From: Pavel StehuleDate: 2011-06-20 07:58:15
Subject: Re: generating a large XML document

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group