From: Andy Colson <andy@squeakycode.net>
To: Anj Adu <fotographs@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: slow query performance
Date: Thu, 03 Jun 2010 13:43:21 -0500
Message-ID: <4C07F7C9.3060401@squeakycode.net> (raw)
In-Reply-To: <AANLkTilbM1iWSrrchOB9x1_W2CKQSTzCow6BznV1DxNk@mail.gmail.com>
References: <AANLkTilbM1iWSrrchOB9x1_W2CKQSTzCow6BznV1DxNk@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net, fotographs@gmail.com
Subject: Re: slow query performance
In-Reply-To: <4C07F7C9.3060401@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox