From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | joel(at)openarc(dot)net |
Cc: | Joel Reed <joelreed(at)openarc(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: string_agg hanging? |
Date: | 2011-02-22 07:38:23 |
Message-ID: | AANLkTim5Z9NLhyU3UJgknCKWMCyugz6y7=1=fW-QrJ4x@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)openarc(dot)net>:
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitriy Igrishin | 2011-02-22 08:27:18 | Re: why is there no TRIGGER ON SELECT ? |
Previous Message | Itnal, Prakash (NSN - IN/Bangalore) | 2011-02-22 07:26:23 | Restart Issue in Cluster environment |