Re: slow query performance

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