Re: slow query performance

Link to plan

http://explain.depesz.com/s/kHa

On Thu, Jun 3, 2010 at 11:43 AM, Andy Colson <andy(at)squeakycode(dot)net> wrote:
> On 6/3/2010 12:47 PM, Anj Adu wrote:
>>
>> I cant seem to pinpoint why this query is slow . No full table scans
>> are being done. The hash join is taking maximum time. The table
>> dev4_act_action has only 3 rows.
>>
>> box is a 2 cpu quad core intel 5430 with 32G RAM... Postgres 8.4.0
>> 1G work_mem
>> 20G effective_cache
>> random_page_cost=1
>> default_statistics_target=1000
>>
>> The larget table  in the inner query is dev4_act_dy_fact which is
>> partitioned into 3 partitions per month. Each partition has about 25
>> million rows.
>> The rest of the tables are very small (100- 1000 rows)
>>
>> explain analyze
>> select ipconvert(srctest_num),CASE targetpt::character varying
>>             WHEN NULL::text THEN serv.targetsrv
>>             ELSE targetpt::character varying
>>         END AS targetsrv, sesstype,hits as cons,bytes, srcz.srcarea as
>> srcz, dstz.dstarea as dstz from
>> (
>> select srctest_num, targetpt,targetsrv_id, sesstype_id, sum(total) as
>> hits, sum(bin) + sum(bout) as bts, sourcearea_id, destinationarea_id
>>  from dev4_act_dy_fact a, dev4_act_action act where thedate between
>> '2010-05-22' and '2010-05-22'
>>  and a.action_id = act.action_id and action in
>> ('rejected','sess_rejected')
>>  and guardid_id in (select guardid_id from dev4_act_guardid where
>> guardid like 'cust00%')
>>  and node_id=(select node_id from dev4_act_node where node='10.90.100.2')
>>  group by srctest_num,targetpt,targetsrv_id,sesstype_id,
>> sourcearea_id, destinationarea_id
>>   order by (sum(bin) + sum(bout)) desc
>>  limit 1000
>>  ) a left outer join dev4_act_dstarea dstz on a.destinationarea_id =
>> dstz.dstarea_id
>>  left outer join dev4_act_srcarea srcz on a.sourcearea_id =
>> srcz.srcarea_id
>>  left outer join  dev4_act_targetsrv serv on a.targetsrv_id =
>> serv.targetsrv_id
>>  left outer join dev4_sesstype proto on a.sesstype_id = proto.sesstype_id
>>  order by bytes desc
>>
>>
>
>
> Wow, the word wrap on that makes it hard to read... can you paste it here
> and send us a link?
>
> http://explain.depesz.com
>
>