array_agg performance - Mailing list pgsql-general
| From | Spotts, Christopher |
|---|---|
| Subject | array_agg performance |
| Date | |
| Msg-id | 18D75C5016995C42BDFE90D1EF1FB1BD064D5410@atl-intexch.tcore.com Whole thread Raw |
| Responses |
Re: array_agg performance
|
| List | pgsql-general |
Using postgres 8.4.1, I thought the upgrade from 8.4.0 would help with array_agg.
Using ARRAY() with a subselect is yielding results hundreds times faster than array_agg even though its plan looks much worse…
And the entire system is seriously slugglish and near non-responsive while running this with array_agg. Eventually I just have to kill it, after running for several hours array_agg one still won’t finish.
Any ideas as to why? Queries and plans below.
SELECT
trip_id,
ARRAY(select customer_upload_id from details_child_current as d2 where d1.trip_id = d2.trip_id) as c1,
array_to_string(
ARRAY(SELECT
customer_upload_id::text||'=>"'||
replace(
'detail_id=>'||case when detail_id is null then 'null' else quote_ident(detail_id::text) end||
',cp=>'||case when cp is null then 'null' else quote_ident(cp::text) end||
',ap=>'||case when ap is null then 'null' else quote_ident(ap::text) end||
',re=>'||case when re is null then 'null' else quote_ident(re::text) end||
',fleetid=>'||case when fleetid is null then 'null' else quote_ident(fleetid::text) end
,'"',$$\"$$)||'"'
FROM details_child_current as d2 where d1.trip_id = d2.trip_id)
,',')::hstore
as c2
FROM
details_child_current as d1;
"Seq Scan on details_child_current d1 (cost=0.00..6295788.14 rows=376423 width=8)"
" SubPlan 1"
" -> Index Scan using details_current_trip_id on details_child_current d2 (cost=0.00..8.31 rows=1 width=4)"
" Index Cond: ($0 = trip_id)"
" SubPlan 2"
" -> Index Scan using details_current_trip_id on details_child_current d2 (cost=0.00..8.38 rows=1 width=45)"
" Index Cond: ($0 = trip_id)"
SELECT
trip_id,
array_agg(customer_upload_id) as c1,
array_to_string(
array_agg(customer_upload_id::text||'=>"'||
replace(
'detail_id=>'||case when detail_id is null then 'null' else quote_ident(detail_id::text) end||
',cp=>'||case when cp is null then 'null' else quote_ident(cp::text) end||
',ap=>'||case when ap is null then 'null' else quote_ident(ap::text) end||
',re=>'||case when re is null then 'null' else quote_ident(re::text) end||
',fleetid=>'||case when fleetid is null then 'null' else quote_ident(fleetid::text) end
,'"',$$\"$$)||'"'
)
,',')::hstore
as c2
FROM
details_child_current as d1
group by trip_id;
"GroupAggregate (cost=0.00..73447.71 rows=346009 width=53)"
" -> Index Scan using details_current_trip_id on details_child_current d1 (cost=0.00..38618.70 rows=376423 width=53)"
Chris Spotts
Programmer / Analyst
Transcore
christopher.spotts@transcore.com
pgsql-general by date: