X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8894E9DCC00 for ; Tue, 21 Feb 2006 01:13:56 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90799-09 for ; Tue, 21 Feb 2006 01:13:55 -0400 (AST) X-Greylist: delayed 22:31:16.009559 by SQLgrey- Received: from mail.auptyma.com (64-60-124-12.cust.telepacific.net [64.60.124.12]) by postgresql.org (Postfix) with ESMTP id D7E929DCBFC for ; Tue, 21 Feb 2006 01:13:53 -0400 (AST) Received: from demo1 (64-60-124-12.cust.telepacific.net [64.60.124.12]) (authenticated bits=0) by mail.auptyma.com (8.13.1/8.13.1) with ESMTP id k1L4q5jk023289 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NO) for ; Mon, 20 Feb 2006 20:52:05 -0800 Message-ID: <028401c636a5$907fb4b0$3100000a@demo1> From: "Virag Saksena" To: Subject: Cost Issue - How do I force a Hash Join Date: Mon, 20 Feb 2006 21:13:34 -0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0281_01C63662.81EB6360" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.7.5 (mail.auptyma.com [10.0.0.47]); Mon, 20 Feb 2006 20:52:05 -0800 (PST) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.081 required=5 tests=[AWL=0.080, HTML_MESSAGE=0.001] X-Spam-Score: 0.081 X-Spam-Level: X-Archive-Number: 200602/323 X-Sequence-Number: 17309 This is a multi-part message in MIME format. ------=_NextPart_000_0281_01C63662.81EB6360 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have query where I do two inline queries (which involves grouping) = and then join them with an outer join. The individual queries run in 50-300 ms. However the optimizer is = choosing a nested loop to join them rather than a Hash join causing the complete query to take 500+ seconds. It expects that it will = get 1 row out from each of the sources, but here is gets several thousand rows. Is there any way I can get a hash join used on the outer join, while = preserving the nested loops. explain analyze select o1.objaddr, o1.fieldname, o1.objsig, o1.totmem, o1.cnt, o2.totmem, o2.cnt from ( select min(ao.objaddr) as objaddr, count(*) as cnt, sum(ao.totmem) as totmem, ao.objsig, ar.fieldname, ao.objtype from jam_heapobj ao, jam_heaprel ar where ar.heap_id =3D 1 and ar.parentaddr =3D 0 and ar.fieldname =3D = 'K' and ao.heap_id =3D ar.heap_id and ao.objaddr =3D ar.childaddr group by ao.objsig, ar.fieldname, ao.objtype) o1 left outer join (select min(bo.objaddr) as objaddr, count(*) as cnt, sum(bo.totmem) as totmem, bo.objsig, br.fieldname, bo.objtype from jam_heapobj bo, jam_heaprel br where br.heap_id =3D 0 and br.parentaddr =3D 0 and br.fieldname =3D = 'K' and bo.heap_id =3D br.heap_id and bo.objaddr =3D br.childaddr group by bo.objsig, br.fieldname, bo.objtype) o2 on ( o2.objsig =3D o1.objsig and o2.objtype =3D o1.objtype and o2.fieldname =3D o1.fieldname) order by o1.totmem - coalesce(o2.totmem,0) desc; Sort (cost=3D16305.41..16305.42 rows=3D1 width=3D562) (actual = time=3D565997.769..566016.255 rows=3D6115 loops=3D1) Sort Key: (o1.totmem - COALESCE(o2.totmem, 0::bigint)) ->Nested Loop Left Join (cost=3D16305.22..16305.40 rows=3D1 = width=3D562) (actual time=3D612.631..565896.047 rows=3D6115 loops=3D1) Join Filter: ((("inner".objsig)::text =3D ("outer".objsig)::text) = AND (("inner".objtype)::text =3D ("outer".objtype)::text) AND = (("inner".fieldname)::text =3D ("outer".fieldname)::text)) ->Subquery Scan o1 (cost=3D12318.12..12318.15 rows=3D1 width=3D514) = (actual time=3D309.659..413.311 rows=3D6115 loops=3D1) ->HashAggregate (cost=3D12318.12..12318.14 rows=3D1 width=3D54) = (actual time=3D309.649..367.206 rows=3D6115 loops=3D1) ->Nested Loop (cost=3D0.00..12317.90 rows=3D10 width=3D54) = (actual time=3D0.243..264.116 rows=3D6338 loops=3D1) ->Index Scan using jam_heaprel_n1 on jam_heaprel ar = (cost=3D0.00..12275.00 rows=3D7 width=3D19) (actual time=3D0.176..35.780 = rows=3D6338 loops=3D1) Index Cond: ((heap_id =3D 1) AND (parentaddr =3D 0)) Filter: ((fieldname)::text =3D 'K'::text) ->Index Scan using jam_heapobj_u1 on jam_heapobj ao = (cost=3D0.00..6.10 rows=3D2 width=3D51) (actual time=3D0.019..0.022 = rows=3D1 loops=3D6338) Index Cond: ((ao.heap_id =3D 1) AND (ao.objaddr =3D = "outer".childaddr)) ->Subquery Scan o2 (cost=3D3987.10..3987.13 rows=3D1 width=3D514) = (actual time=3D0.062..75.171 rows=3D6038 loops=3D6115) ->HashAggregate (cost=3D3987.10..3987.12 rows=3D1 width=3D54) = (actual time=3D0.056..36.469 rows=3D6038 loops=3D6115) ->Nested Loop (cost=3D0.00..3987.05 rows=3D2 width=3D54) (actual = time=3D0.145..257.876 rows=3D6259 loops=3D1) ->Index Scan using jam_heaprel_n1 on jam_heaprel br = (cost=3D0.00..3974.01 rows=3D3 width=3D19) (actual time=3D0.074..35.124 = rows=3D6259 loops=3D1) Index Cond: ((heap_id =3D 0) AND (parentaddr =3D 0)) Filter: ((fieldname)::text =3D 'K'::text) ->Index Scan using jam_heapobj_u1 on jam_heapobj bo = (cost=3D0.00..4.33 rows=3D1 width=3D51) (actual time=3D0.018..0.022 = rows=3D1 loops=3D6259) Index Cond: ((bo.heap_id =3D 0) AND (bo.objaddr =3D = "outer".childaddr)) Total runtime: 566044.187 ms (21 rows) Regards, Virag ------=_NextPart_000_0281_01C63662.81EB6360 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
    I have query where I = do two=20 inline queries (which involves grouping) and then join them with an = outer=20 join.
The individual queries run in 50-300 = ms. However=20 the optimizer is choosing a nested loop to join them rather than a Hash=20 join
causing the complete query to take 500+ = seconds. It=20 expects that it will get 1 row out from each of the sources, but here is = gets
several thousand rows.
 
Is there any way I can get a hash join = used on the=20 outer join, while preserving the nested loops.
 
 
explain analyze
select o1.objaddr, = o1.fieldname,=20 o1.objsig,
o1.totmem, o1.cnt,
o2.totmem, o2.cnt
from
( = select=20 min(ao.objaddr) as objaddr, count(*) as=20 cnt,
         sum(ao.totmem) = as=20 totmem, ao.objsig, ar.fieldname, ao.objtype
    from=20 jam_heapobj ao, jam_heaprel ar
   where ar.heap_id =3D = 1  and=20 ar.parentaddr =3D 0 and ar.fieldname =3D 'K'
     = and=20 ao.heap_id =3D ar.heap_id and ao.objaddr =3D = ar.childaddr
   group by=20 ao.objsig, ar.fieldname, ao.objtype) o1
left outer join
(select=20 min(bo.objaddr) as objaddr, count(*) as=20 cnt,
        sum(bo.totmem) as = totmem,=20 bo.objsig, br.fieldname, bo.objtype
   from jam_heapobj bo, = jam_heaprel br
  where br.heap_id =3D 0  and br.parentaddr = =3D 0 and=20 br.fieldname =3D 'K'
    and bo.heap_id =3D br.heap_id = and=20 bo.objaddr =3D br.childaddr
group by bo.objsig, br.fieldname, = bo.objtype)=20 o2
on ( o2.objsig =3D o1.objsig and o2.objtype =3D = o1.objtype
 and=20 o2.fieldname =3D o1.fieldname)
 order by o1.totmem - = coalesce(o2.totmem,0)=20 desc;
 
 Sort (cost=3D16305.41..16305.42 = rows=3D1=20 width=3D562) (actual time=3D565997.769..566016.255 rows=3D6115 = loops=3D1)
  Sort=20 Key: (o1.totmem - COALESCE(o2.totmem, 0::bigint))
  ->Nested = Loop=20 Left Join (cost=3D16305.22..16305.40 rows=3D1 width=3D562) (actual=20 time=3D612.631..565896.047 rows=3D6115 loops=3D1)
    = Join Filter:=20 ((("inner".objsig)::text =3D ("outer".objsig)::text) AND = (("inner".objtype)::text=20 =3D ("outer".objtype)::text) AND (("inner".fieldname)::text =3D=20 ("outer".fieldname)::text))
    ->Subquery Scan o1=20 (cost=3D12318.12..12318.15 rows=3D1 width=3D514) (actual = time=3D309.659..413.311=20 rows=3D6115 loops=3D1)
      = ->HashAggregate=20 (cost=3D12318.12..12318.14 rows=3D1 width=3D54) (actual = time=3D309.649..367.206=20 rows=3D6115 loops=3D1)
        = ->Nested=20 Loop (cost=3D0.00..12317.90 rows=3D10 width=3D54) (actual = time=3D0.243..264.116=20 rows=3D6338 = loops=3D1)
         =20 ->Index Scan using jam_heaprel_n1 on jam_heaprel ar = (cost=3D0.00..12275.00=20 rows=3D7 width=3D19) (actual time=3D0.176..35.780 rows=3D6338=20 loops=3D1)
          = ; =20 Index Cond: ((heap_id =3D 1) AND (parentaddr =3D=20 0))
           = =20 Filter: ((fieldname)::text =3D=20 'K'::text)
          = ->Index=20 Scan using jam_heapobj_u1 on jam_heapobj ao (cost=3D0.00..6.10 rows=3D2 = width=3D51)=20 (actual time=3D0.019..0.022 rows=3D1=20 loops=3D6338)
         &n= bsp; =20 Index Cond: ((ao.heap_id =3D 1) AND (ao.objaddr =3D=20 "outer".childaddr))
    ->Subquery Scan o2=20 (cost=3D3987.10..3987.13 rows=3D1 width=3D514) (actual = time=3D0.062..75.171 rows=3D6038=20 loops=3D6115)
      ->HashAggregate=20 (cost=3D3987.10..3987.12 rows=3D1 width=3D54) (actual = time=3D0.056..36.469 rows=3D6038=20 loops=3D6115)
        ->Nested = Loop=20 (cost=3D0.00..3987.05 rows=3D2 width=3D54) (actual time=3D0.145..257.876 = rows=3D6259=20 loops=3D1)
          = ->Index=20 Scan using jam_heaprel_n1 on jam_heaprel br (cost=3D0.00..3974.01 = rows=3D3 width=3D19)=20 (actual time=3D0.074..35.124 rows=3D6259=20 loops=3D1)
          = ; =20 Index Cond: ((heap_id =3D 0) AND (parentaddr =3D=20 0))
           = =20 Filter: ((fieldname)::text =3D=20 'K'::text)
          = ->Index=20 Scan using jam_heapobj_u1 on jam_heapobj bo (cost=3D0.00..4.33 rows=3D1 = width=3D51)=20 (actual time=3D0.018..0.022 rows=3D1=20 loops=3D6259)
         &n= bsp; =20 Index Cond: ((bo.heap_id =3D 0) AND (bo.objaddr =3D=20 "outer".childaddr))
 Total runtime: 566044.187 ms
(21=20 rows)
Regards,
 
Virag
------=_NextPart_000_0281_01C63662.81EB6360--