Hello these queries are different. I checked a string_agg and it working with me. So I am expecting, so your query has a performance problem when aggregate function is used. please, recheck so indexes are used you can try "set enable_seqscan" to off, maybe set hashagg to off please, send a execute plans - see statement EXPLAIN Regards Pavel Stehule 2011/2/21 Joel Reed <joelreed@xxxxxxxxxxx>: > Hoping someone will be kind enough to share how to write a query that uses > 9.0's string_agg with a subselect, something like... > > select m.id,m.subject,m.from_address,m.date_sent,m.size,string_agg((select > address as to_address from recipient r inner join message_recipient mr on > r.id=mr.recipient_id and mr.message_id=m.id and mr.recipient_type='To'), ', > ') from message m, recipient r group by m.id, m.subject, m.from_address, > m.date_sent, m.size limit 20; > > Unforunately, that just seems to hang. So I'm using... > > select > m.id,m.subject,m.from_address,m.date_sent,m.size,array_to_string(ARRAY(select > r.address from recipient r inner join message_recipient mr on > r.id=mr.recipient_id and message_id=m.id and mr.recipient_type='To'), ',') > as to_addresses from message m, recipient r limit 20; > > Which returns in under 4 seconds. Am I doing something wrong with the > string_agg query to make it hang? > > Thanks in advance - I greatly appreciate any help you can offer. > > jr > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
