|
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@xxxxxxxxxxxxx |
