Message-ID: <4C07F7C9.3060401@squeakycode.net>
Date: Thu, 03 Jun 2010 13:43:21 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.1.9) Gecko/20100317 Lightning/1.0b1 Thunderbird/3.0.4
MIME-Version: 1.0
To: Anj Adu <fotographs@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: slow query performance
References: <AANLkTilbM1iWSrrchOB9x1_W2CKQSTzCow6BznV1DxNk@mail.gmail.com>
In-Reply-To: <AANLkTilbM1iWSrrchOB9x1_W2CKQSTzCow6BznV1DxNk@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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