Message-ID: <4DFA5AA4.8090001@nsoft.lt>
Date: Thu, 16 Jun 2011 22:33:56 +0300
From: Julius Tuskenis <julius@nsoft.lt>
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: generating a large XML document
Content-Type: multipart/mixed; boundary="------------070006050903050704020309"

This is a multi-part message in MIME format.
--------------070006050903050704020309
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hello, postgresql guru's

When developing ticket export mechanism for our ticketing system I 
thought the wise thing would be to generate the result (XML file) in 
stored function. This worked fine for small datasets, but for the larger 
ones I see it takes much longer to generate the document than to select 
the data needed for it.

Here are several "explain alalyze"s :

explain analyze
SELECT * from tex.fnk_access_control_tickets(8560, 0);

"Function Scan on fnk_access_control_tickets  (cost=0.25..10.25 
rows=1000 width=246) (actual time=479.276..484.429 rows=16292 loops=1)"
"Total runtime: 486.774 ms"

explain analyze
SELECT
     --xmlagg(
       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
        )
     -- )
     FROM tex.fnk_access_control_tickets(8560, 0);

"Function Scan on fnk_access_control_tickets  (cost=0.25..17.75 
rows=1000 width=238) (actual time=476.446..924.785 rows=16292 loops=1)"
"Total runtime: 928.768 ms"


explain analyze
SELECT
     xmlagg(
       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
        )
      )
     FROM tex.fnk_access_control_tickets(8560, 0);

"Aggregate  (cost=12.75..12.77 rows=1 width=238) (actual 
time=16110.847..16110.848 rows=1 loops=1)"
"  ->  Function Scan on fnk_access_control_tickets  (cost=0.25..10.25 
rows=1000 width=238) (actual time=500.029..520.974 rows=16292 loops=1)"
"Total runtime: 16111.264 ms"

It seems the aggregate combining the elements is to blame... What I did 
next was rewriting it in stored function using FOR loop and combining it 
in a text variable. Sadly the result was the same (approximately 16 
seconds).

 From that I had to conclude that text := text + some_text operation is 
an expensive one, but now I have no ideas left how to solve the problem. 
Any notices, recommendations, advices are very welcome.

I've also tried google'ing on XML creation in Postgresql, but found no 
warnings or even mentioning xmlagg could cause a headache. I have 
nowhere to turn for help now, so please advice...

Not sure if that will help, but anyway:
Server:
PostgreSQL 9.0.3 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real 
(Debian 4.4.5-10) 4.4.5, 32-bit
see the result of select * from pg_settings  in attachment if needed.
Client:
Windows XP, pgAdmin 1.12.3


Thank you in advance.

-- 
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050


--------------070006050903050704020309
Content-Type: text/plain;
 name="pg_settings.txt"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="pg_settings.txt"

ImFsbG93X3N5c3RlbV90YWJsZV9tb2RzIjsib2ZmIg0KImFwcGxpY2F0aW9uX25hbWUiOyJw
Z0FkbWluIElJSSAtIFF1ZXJ5IFRvb2wiDQoiYXJjaGl2ZV9jb21tYW5kIjsiKGRpc2FibGVk
KSINCiJhcmNoaXZlX21vZGUiOyJvZmYiDQoiYXJjaGl2ZV90aW1lb3V0IjsiMCINCiJhcnJh
eV9udWxscyI7Im9uIg0KImF1dGhlbnRpY2F0aW9uX3RpbWVvdXQiOyI2MCINCiJhdXRvdmFj
dXVtIjsib2ZmIg0KImF1dG92YWN1dW1fYW5hbHl6ZV9zY2FsZV9mYWN0b3IiOyIwLjEiDQoi
YXV0b3ZhY3V1bV9hbmFseXplX3RocmVzaG9sZCI7IjUwIg0KImF1dG92YWN1dW1fZnJlZXpl
X21heF9hZ2UiOyIyMDAwMDAwMDAiDQoiYXV0b3ZhY3V1bV9tYXhfd29ya2VycyI7IjMiDQoi
YXV0b3ZhY3V1bV9uYXB0aW1lIjsiNjAiDQoiYXV0b3ZhY3V1bV92YWN1dW1fY29zdF9kZWxh
eSI7IjIwIg0KImF1dG92YWN1dW1fdmFjdXVtX2Nvc3RfbGltaXQiOyItMSINCiJhdXRvdmFj
dXVtX3ZhY3V1bV9zY2FsZV9mYWN0b3IiOyIwLjIiDQoiYXV0b3ZhY3V1bV92YWN1dW1fdGhy
ZXNob2xkIjsiNTAiDQoiYmFja3NsYXNoX3F1b3RlIjsic2FmZV9lbmNvZGluZyINCiJiZ3dy
aXRlcl9kZWxheSI7IjIwMCINCiJiZ3dyaXRlcl9scnVfbWF4cGFnZXMiOyIxMDAiDQoiYmd3
cml0ZXJfbHJ1X211bHRpcGxpZXIiOyIyIg0KImJsb2NrX3NpemUiOyI4MTkyIg0KImJvbmpv
dXIiOyJvZmYiDQoiYm9uam91cl9uYW1lIjsiIg0KImJ5dGVhX291dHB1dCI7ImVzY2FwZSIN
CiJjaGVja19mdW5jdGlvbl9ib2RpZXMiOyJvbiINCiJjaGVja3BvaW50X2NvbXBsZXRpb25f
dGFyZ2V0IjsiMC41Ig0KImNoZWNrcG9pbnRfc2VnbWVudHMiOyIzIg0KImNoZWNrcG9pbnRf
dGltZW91dCI7IjMwMCINCiJjaGVja3BvaW50X3dhcm5pbmciOyIzMCINCiJjbGllbnRfZW5j
b2RpbmciOyJVTklDT0RFIg0KImNsaWVudF9taW5fbWVzc2FnZXMiOyJub3RpY2UiDQoiY29t
bWl0X2RlbGF5IjsiMCINCiJjb21taXRfc2libGluZ3MiOyI1Ig0KImNvbnN0cmFpbnRfZXhj
bHVzaW9uIjsicGFydGl0aW9uIg0KImNwdV9pbmRleF90dXBsZV9jb3N0IjsiMC4wMDUiDQoi
Y3B1X29wZXJhdG9yX2Nvc3QiOyIwLjAwMjUiDQoiY3B1X3R1cGxlX2Nvc3QiOyIwLjAxIg0K
ImN1cnNvcl90dXBsZV9mcmFjdGlvbiI7IjAuMSINCiJjdXN0b21fdmFyaWFibGVfY2xhc3Nl
cyI7IiINCiJEYXRlU3R5bGUiOyJJU08sIE1EWSINCiJkYl91c2VyX25hbWVzcGFjZSI7Im9m
ZiINCiJkZWFkbG9ja190aW1lb3V0IjsiMTAwMCINCiJkZWJ1Z19hc3NlcnRpb25zIjsib2Zm
Ig0KImRlYnVnX3ByZXR0eV9wcmludCI7Im9uIg0KImRlYnVnX3ByaW50X3BhcnNlIjsib2Zm
Ig0KImRlYnVnX3ByaW50X3BsYW4iOyJvZmYiDQoiZGVidWdfcHJpbnRfcmV3cml0dGVuIjsi
b2ZmIg0KImRlZmF1bHRfc3RhdGlzdGljc190YXJnZXQiOyIxMDAiDQoiZGVmYXVsdF90YWJs
ZXNwYWNlIjsiIg0KImRlZmF1bHRfdGV4dF9zZWFyY2hfY29uZmlnIjsicGdfY2F0YWxvZy5l
bmdsaXNoIg0KImRlZmF1bHRfdHJhbnNhY3Rpb25faXNvbGF0aW9uIjsicmVhZCBjb21taXR0
ZWQiDQoiZGVmYXVsdF90cmFuc2FjdGlvbl9yZWFkX29ubHkiOyJvZmYiDQoiZGVmYXVsdF93
aXRoX29pZHMiOyJvZmYiDQoiZWZmZWN0aXZlX2NhY2hlX3NpemUiOyIxNjM4NCINCiJlZmZl
Y3RpdmVfaW9fY29uY3VycmVuY3kiOyIxIg0KImVuYWJsZV9iaXRtYXBzY2FuIjsib24iDQoi
ZW5hYmxlX2hhc2hhZ2ciOyJvbiINCiJlbmFibGVfaGFzaGpvaW4iOyJvbiINCiJlbmFibGVf
aW5kZXhzY2FuIjsib24iDQoiZW5hYmxlX21hdGVyaWFsIjsib24iDQoiZW5hYmxlX21lcmdl
am9pbiI7Im9uIg0KImVuYWJsZV9uZXN0bG9vcCI7Im9uIg0KImVuYWJsZV9zZXFzY2FuIjsi
b24iDQoiZW5hYmxlX3NvcnQiOyJvbiINCiJlbmFibGVfdGlkc2NhbiI7Im9uIg0KImVzY2Fw
ZV9zdHJpbmdfd2FybmluZyI7Im9uIg0KImV4dHJhX2Zsb2F0X2RpZ2l0cyI7IjAiDQoiZnJv
bV9jb2xsYXBzZV9saW1pdCI7IjgiDQoiZnN5bmMiOyJvbiINCiJmdWxsX3BhZ2Vfd3JpdGVz
Ijsib24iDQoiZ2VxbyI7Im9uIg0KImdlcW9fZWZmb3J0IjsiNSINCiJnZXFvX2dlbmVyYXRp
b25zIjsiMCINCiJnZXFvX3Bvb2xfc2l6ZSI7IjAiDQoiZ2Vxb19zZWVkIjsiMCINCiJnZXFv
X3NlbGVjdGlvbl9iaWFzIjsiMiINCiJnZXFvX3RocmVzaG9sZCI7IjEyIg0KImdpbl9mdXp6
eV9zZWFyY2hfbGltaXQiOyIwIg0KImhvdF9zdGFuZGJ5Ijsib2ZmIg0KImlnbm9yZV9zeXN0
ZW1faW5kZXhlcyI7Im9mZiINCiJpbnRlZ2VyX2RhdGV0aW1lcyI7Im9uIg0KIkludGVydmFs
U3R5bGUiOyJwb3N0Z3JlcyINCiJqb2luX2NvbGxhcHNlX2xpbWl0IjsiOCINCiJrcmJfY2Fz
ZWluc191c2VycyI7Im9mZiINCiJrcmJfc3J2bmFtZSI7InBvc3RncmVzIg0KImxjX2NvbGxh
dGUiOyJlbl9VUy5VVEYtOCINCiJsY19jdHlwZSI7ImVuX1VTLlVURi04Ig0KImxjX21lc3Nh
Z2VzIjsiQyINCiJsY19tb25ldGFyeSI7IkMiDQoibGNfbnVtZXJpYyI7IkMiDQoibGNfdGlt
ZSI7IkMiDQoibGlzdGVuX2FkZHJlc3NlcyI7IioiDQoibG9fY29tcGF0X3ByaXZpbGVnZXMi
OyJvZmYiDQoibG9jYWxfcHJlbG9hZF9saWJyYXJpZXMiOyIiDQoibG9nX2F1dG92YWN1dW1f
bWluX2R1cmF0aW9uIjsiLTEiDQoibG9nX2NoZWNrcG9pbnRzIjsib2ZmIg0KImxvZ19jb25u
ZWN0aW9ucyI7Im9mZiINCiJsb2dfZGVzdGluYXRpb24iOyJzdGRlcnIiDQoibG9nX2Rpc2Nv
bm5lY3Rpb25zIjsib2ZmIg0KImxvZ19kdXJhdGlvbiI7Im9mZiINCiJsb2dfZXJyb3JfdmVy
Ym9zaXR5IjsiZGVmYXVsdCINCiJsb2dfZXhlY3V0b3Jfc3RhdHMiOyJvZmYiDQoibG9nX2hv
c3RuYW1lIjsib2ZmIg0KImxvZ19saW5lX3ByZWZpeCI7IiVoICVtICVzICINCiJsb2dfbG9j
a193YWl0cyI7Im9uIg0KImxvZ19taW5fZHVyYXRpb25fc3RhdGVtZW50IjsiMTUwMDAiDQoi
bG9nX21pbl9lcnJvcl9zdGF0ZW1lbnQiOyJlcnJvciINCiJsb2dfbWluX21lc3NhZ2VzIjsi
d2FybmluZyINCiJsb2dfcGFyc2VyX3N0YXRzIjsib2ZmIg0KImxvZ19wbGFubmVyX3N0YXRz
Ijsib2ZmIg0KImxvZ19yb3RhdGlvbl9hZ2UiOyIxNDQwIg0KImxvZ19yb3RhdGlvbl9zaXpl
IjsiMTAyNDAiDQoibG9nX3N0YXRlbWVudCI7Im5vbmUiDQoibG9nX3N0YXRlbWVudF9zdGF0
cyI7Im9mZiINCiJsb2dfdGVtcF9maWxlcyI7Ii0xIg0KImxvZ190aW1lem9uZSI7ImxvY2Fs
dGltZSINCiJsb2dfdHJ1bmNhdGVfb25fcm90YXRpb24iOyJvZmYiDQoibG9nZ2luZ19jb2xs
ZWN0b3IiOyJvZmYiDQoibWFpbnRlbmFuY2Vfd29ya19tZW0iOyIxNjM4NCINCiJtYXhfY29u
bmVjdGlvbnMiOyIxNTAiDQoibWF4X2ZpbGVzX3Blcl9wcm9jZXNzIjsiMTAwMCINCiJtYXhf
ZnVuY3Rpb25fYXJncyI7IjEwMCINCiJtYXhfaWRlbnRpZmllcl9sZW5ndGgiOyI2MyINCiJt
YXhfaW5kZXhfa2V5cyI7IjMyIg0KIm1heF9sb2Nrc19wZXJfdHJhbnNhY3Rpb24iOyI2NCIN
CiJtYXhfcHJlcGFyZWRfdHJhbnNhY3Rpb25zIjsiMCINCiJtYXhfc3RhY2tfZGVwdGgiOyIy
MDQ4Ig0KIm1heF9zdGFuZGJ5X2FyY2hpdmVfZGVsYXkiOyIzMDAwMCINCiJtYXhfc3RhbmRi
eV9zdHJlYW1pbmdfZGVsYXkiOyIzMDAwMCINCiJtYXhfd2FsX3NlbmRlcnMiOyIwIg0KInBh
c3N3b3JkX2VuY3J5cHRpb24iOyJvbiINCiJwbHBnc3FsLnZhcmlhYmxlX2NvbmZsaWN0Ijsi
ZXJyb3IiDQoicG9ydCI7IjU0MzIiDQoicG9zdF9hdXRoX2RlbGF5IjsiMCINCiJwcmVfYXV0
aF9kZWxheSI7IjAiDQoicmFuZG9tX3BhZ2VfY29zdCI7IjQiDQoic2VhcmNoX3BhdGgiOyJw
dWJsaWMsIHRleCINCiJzZWdtZW50X3NpemUiOyIxMzEwNzIiDQoic2VxX3BhZ2VfY29zdCI7
IjEiDQoic2VydmVyX2VuY29kaW5nIjsiVVRGOCINCiJzZXJ2ZXJfdmVyc2lvbiI7IjkuMC4z
Ig0KInNlcnZlcl92ZXJzaW9uX251bSI7IjkwMDAzIg0KInNlc3Npb25fcmVwbGljYXRpb25f
cm9sZSI7Im9yaWdpbiINCiJzaGFyZWRfYnVmZmVycyI7IjE2Mzg0Ig0KInNpbGVudF9tb2Rl
Ijsib2ZmIg0KInNxbF9pbmhlcml0YW5jZSI7Im9uIg0KInNzbCI7Im9mZiINCiJzc2xfcmVu
ZWdvdGlhdGlvbl9saW1pdCI7IjUyNDI4OCINCiJzdGFuZGFyZF9jb25mb3JtaW5nX3N0cmlu
Z3MiOyJvZmYiDQoic3RhdGVtZW50X3RpbWVvdXQiOyIwIg0KInN1cGVydXNlcl9yZXNlcnZl
ZF9jb25uZWN0aW9ucyI7IjciDQoic3luY2hyb25pemVfc2Vxc2NhbnMiOyJvbiINCiJzeW5j
aHJvbm91c19jb21taXQiOyJvbiINCiJzeXNsb2dfZmFjaWxpdHkiOyJsb2NhbDAiDQoic3lz
bG9nX2lkZW50IjsicG9zdGdyZXMiDQoidGNwX2tlZXBhbGl2ZXNfY291bnQiOyI5Ig0KInRj
cF9rZWVwYWxpdmVzX2lkbGUiOyI3MjAwIg0KInRjcF9rZWVwYWxpdmVzX2ludGVydmFsIjsi
NzUiDQoidGVtcF9idWZmZXJzIjsiMTAyNCINCiJ0ZW1wX3RhYmxlc3BhY2VzIjsiIg0KIlRp
bWVab25lIjsibG9jYWx0aW1lIg0KInRpbWV6b25lX2FiYnJldmlhdGlvbnMiOyJEZWZhdWx0
Ig0KInRyYWNlX25vdGlmeSI7Im9mZiINCiJ0cmFjZV9yZWNvdmVyeV9tZXNzYWdlcyI7Imxv
ZyINCiJ0cmFjZV9zb3J0Ijsib2ZmIg0KInRyYWNrX2FjdGl2aXRpZXMiOyJvbiINCiJ0cmFj
a19hY3Rpdml0eV9xdWVyeV9zaXplIjsiMTAyNCINCiJ0cmFja19jb3VudHMiOyJvbiINCiJ0
cmFja19mdW5jdGlvbnMiOyJub25lIg0KInRyYW5zYWN0aW9uX2lzb2xhdGlvbiI7InJlYWQg
Y29tbWl0dGVkIg0KInRyYW5zYWN0aW9uX3JlYWRfb25seSI7Im9mZiINCiJ0cmFuc2Zvcm1f
bnVsbF9lcXVhbHMiOyJvZmYiDQoidW5peF9zb2NrZXRfZ3JvdXAiOyIiDQoidW5peF9zb2Nr
ZXRfcGVybWlzc2lvbnMiOyI1MTEiDQoidXBkYXRlX3Byb2Nlc3NfdGl0bGUiOyJvbiINCiJ2
YWN1dW1fY29zdF9kZWxheSI7IjAiDQoidmFjdXVtX2Nvc3RfbGltaXQiOyIyMDAiDQoidmFj
dXVtX2Nvc3RfcGFnZV9kaXJ0eSI7IjIwIg0KInZhY3V1bV9jb3N0X3BhZ2VfaGl0IjsiMSIN
CiJ2YWN1dW1fY29zdF9wYWdlX21pc3MiOyIxMCINCiJ2YWN1dW1fZGVmZXJfY2xlYW51cF9h
Z2UiOyIwIg0KInZhY3V1bV9mcmVlemVfbWluX2FnZSI7IjUwMDAwMDAwIg0KInZhY3V1bV9m
cmVlemVfdGFibGVfYWdlIjsiMTUwMDAwMDAwIg0KIndhbF9ibG9ja19zaXplIjsiODE5MiIN
CiJ3YWxfYnVmZmVycyI7IjgiDQoid2FsX2tlZXBfc2VnbWVudHMiOyIwIg0KIndhbF9sZXZl
bCI7Im1pbmltYWwiDQoid2FsX3NlZ21lbnRfc2l6ZSI7IjIwNDgiDQoid2FsX3NlbmRlcl9k
ZWxheSI7IjIwMCINCiJ3YWxfc3luY19tZXRob2QiOyJmZGF0YXN5bmMiDQoid2FsX3dyaXRl
cl9kZWxheSI7IjIwMCINCiJ3b3JrX21lbSI7IjEwMjQiDQoieG1sYmluYXJ5IjsiYmFzZTY0
Ig0KInhtbG9wdGlvbiI7ImNvbnRlbnQiDQoiemVyb19kYW1hZ2VkX3BhZ2VzIjsib2ZmIg0K

--------------070006050903050704020309--