Link to plan http://explain.depesz.com/s/kHa On Thu, Jun 3, 2010 at 11:43 AM, Andy Colson <andy@xxxxxxxxxxxxxxx> 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 > > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
