Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id BB487632D1B for ; Mon, 11 May 2009 11:43:24 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 57055-08-3 for ; Mon, 11 May 2009 11:43:21 -0300 (ADT) Received: from mx3.hub.org (mx3.hub.org [206.223.169.73]) by mail.postgresql.org (Postfix) with ESMTP id 59D03635182 for ; Mon, 11 May 2009 11:43:21 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.kickstone.com (smtp.kickstone.com [87.194.183.70]) by mx3.hub.org (Postfix) with ESMTP id A2D5437B40B for ; Mon, 11 May 2009 11:43:20 -0300 (ADT) Received: from madras2.square-pi.com ([192.168.2.131]:1813 helo=MADRAS2) by mail.kickstone.com with esmtpa (Exim 4.69) (envelope-from ) id 1M3Wiq-00028m-HW for pgsql-sql@postgresql.org; Mon, 11 May 2009 15:43:16 +0100 Message-ID: From: "John Lister" To: Subject: Query planning question Date: Mon, 11 May 2009 15:43:16 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_003D_01C9D24F.33770FE0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200905/48 X-Sequence-Number: 32540 This is a multi-part message in MIME format. ------=_NextPart_000_003D_01C9D24F.33770FE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Doing the following query=20 select distinct m.id, m.name=20 from manufacturer_manufacturer m=20 join product_product p on (p.manufacturer_id=3Dm.id)=20 join retailer_offer o on (o.product_id=3Dp.id) where o.retailer_id=3DXXX and o.active results in one of 2 query plans depending upon the value of XXX.=20 The first ignores the index on products and does a hash join which is = very slow, the second uses the index and does a nested loop which is = fast.=20 Am I right in assuming the planner thinks a sequential scan is quicker = than 10k index hits, would tweaking the costs fix this or would i be = better updating the stats for the product_id and manufacturer_id fields? "Unique (cost=3D318308.62..321110.94 rows=3D1029 width=3D13) (actual = time=3D5057.271..5296.973 rows=3D699 loops=3D1)" " -> Sort (cost=3D318308.62..319242.73 rows=3D373642 width=3D13) = (actual time=3D5057.270..5196.780 rows=3D455733 loops=3D1)" " Sort Key: m.id, m.name" " Sort Method: external merge Disk: 11032kB" " -> Hash Join (cost=3D110196.74..283725.63 rows=3D373642 = width=3D13) (actual time=3D1706.287..3451.352 rows=3D455733 loops=3D1)" " Hash Cond: (p.manufacturer_id =3D m.id)" " -> Hash Join (cost=3D110163.59..278554.90 rows=3D373642 = width=3D4) (actual time=3D1705.652..3230.879 rows=3D455733 loops=3D1)" " Hash Cond: (o.product_id =3D p.id)" " -> Bitmap Heap Scan on retailer_offer o = (cost=3D9418.68..157960.21 rows=3D373642 width=3D4) (actual = time=3D120.277..382.208 rows=3D455733 loops=3D1)" " Recheck Cond: ((retailer_id =3D 1347) AND = active)" " -> Bitmap Index Scan on = idx_retaileroffer_retailerid (cost=3D0.00..9325.27 rows=3D373642 = width=3D0) (actual time=3D79.503..79.503 rows=3D455829 loops=3D1)" " Index Cond: (retailer_id =3D 1347)" " -> Hash (cost=3D59067.07..59067.07 rows=3D2540307 = width=3D8) (actual time=3D1584.994..1584.994 rows=3D2540324 loops=3D1)" " -> Seq Scan on product_product p = (cost=3D0.00..59067.07 rows=3D2540307 width=3D8) (actual = time=3D0.008..698.313 rows=3D2540324 loops=3D1)" " -> Hash (cost=3D20.29..20.29 rows=3D1029 width=3D13) = (actual time=3D0.627..0.627 rows=3D1029 loops=3D1)" " -> Seq Scan on manufacturer_manufacturer m = (cost=3D0.00..20.29 rows=3D1029 width=3D13) (actual time=3D0.007..0.278 = rows=3D1029 loops=3D1)" "Total runtime: 5310.663 ms" "Unique (cost=3D43237.52..43266.80 rows=3D1029 width=3D13) (actual = time=3D190.978..196.625 rows=3D276 loops=3D1)" " -> Sort (cost=3D43237.52..43247.28 rows=3D3903 width=3D13) (actual = time=3D190.977..192.431 rows=3D11298 loops=3D1)" " Sort Key: m.id, m.name" " Sort Method: quicksort Memory: 1037kB" " -> Hash Join (cost=3D134.14..43004.70 rows=3D3903 width=3D13) = (actual time=3D5.006..155.188 rows=3D11298 loops=3D1)" " Hash Cond: (p.manufacturer_id =3D m.id)" " -> Nested Loop (cost=3D100.99..42917.88 rows=3D3903 = width=3D4) (actual time=3D4.363..146.421 rows=3D11298 loops=3D1)" " -> Bitmap Heap Scan on retailer_offer o = (cost=3D100.99..13663.63 rows=3D3903 width=3D4) (actual = time=3D4.345..29.871 rows=3D11298 loops=3D1)" " Recheck Cond: ((retailer_id =3D 1710) AND = active)" " -> Bitmap Index Scan on = idx_retaileroffer_retailerid (cost=3D0.00..100.02 rows=3D3903 = width=3D0) (actual time=3D2.368..2.368 rows=3D11380 loops=3D1)" " Index Cond: (retailer_id =3D 1710)" " -> Index Scan using product_product_pkey on = product_product p (cost=3D0.00..7.48 rows=3D1 width=3D8) (actual = time=3D0.008..0.009 rows=3D1 loops=3D11298)" " Index Cond: (p.id =3D o.product_id)" " -> Hash (cost=3D20.29..20.29 rows=3D1029 width=3D13) = (actual time=3D0.634..0.634 rows=3D1029 loops=3D1)" " -> Seq Scan on manufacturer_manufacturer m = (cost=3D0.00..20.29 rows=3D1029 width=3D13) (actual time=3D0.009..0.275 = rows=3D1029 loops=3D1)" "Total runtime: 196.716 ms" Thanks -- Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/ ------=_NextPart_000_003D_01C9D24F.33770FE0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Doing the following query
 
select distinct m.id, m.name =
   =20 from manufacturer_manufacturer m
    join = product_product p=20 on (p.manufacturer_id=3Dm.id)
    join retailer_offer = o on=20 (o.product_id=3Dp.id)
        = where=20 o.retailer_id=3DXXX and o.active
 
results in one of 2 query plans = depending upon the=20 value of XXX.
The first ignores the index on products and does a = hash join=20 which is very slow, the second uses the index and does a nested loop = which is=20 fast.
 
Am I right in assuming the planner = thinks a=20 sequential scan is quicker than 10k index hits, would tweaking the costs = fix=20 this or would i be better updating the stats for the product_id and=20 manufacturer_id fields?
 
"Unique  = (cost=3D318308.62..321110.94 rows=3D1029=20 width=3D13) (actual time=3D5057.271..5296.973 rows=3D699 = loops=3D1)"
" =20 ->  Sort  (cost=3D318308.62..319242.73 rows=3D373642 = width=3D13) (actual=20 time=3D5057.270..5196.780 rows=3D455733=20 loops=3D1)"
"        Sort Key: = m.id,=20 m.name"
"        Sort = Method: =20 external merge  Disk:=20 11032kB"
"        ->  Hash = Join  (cost=3D110196.74..283725.63 rows=3D373642 width=3D13) = (actual=20 time=3D1706.287..3451.352 rows=3D455733=20 loops=3D1)"
"         &nb= sp;   =20 Hash Cond: (p.manufacturer_id =3D=20 m.id)"
"          &n= bsp;  =20 ->  Hash Join  (cost=3D110163.59..278554.90 rows=3D373642 = width=3D4)=20 (actual time=3D1705.652..3230.879 rows=3D455733=20 loops=3D1)"
"         &nb= sp;         =20 Hash Cond: (o.product_id =3D=20 p.id)"
"          &n= bsp;        =20 ->  Bitmap Heap Scan on retailer_offer o  = (cost=3D9418.68..157960.21=20 rows=3D373642 width=3D4) (actual time=3D120.277..382.208 rows=3D455733=20 loops=3D1)"
"         &nb= sp;           &nbs= p;   =20 Recheck Cond: ((retailer_id =3D 1347) AND=20 active)"
"          =             &= nbsp;  =20 ->  Bitmap Index Scan on idx_retaileroffer_retailerid =20 (cost=3D0.00..9325.27 rows=3D373642 width=3D0) (actual = time=3D79.503..79.503 rows=3D455829=20 loops=3D1)"
"         &nb= sp;           &nbs= p;         =20 Index Cond: (retailer_id =3D=20 1347)"
"          &n= bsp;        =20 ->  Hash  (cost=3D59067.07..59067.07 rows=3D2540307 = width=3D8) (actual=20 time=3D1584.994..1584.994 rows=3D2540324=20 loops=3D1)"
"         &nb= sp;           &nbs= p;   =20 ->  Seq Scan on product_product p  (cost=3D0.00..59067.07=20 rows=3D2540307 width=3D8) (actual time=3D0.008..698.313 rows=3D2540324=20 loops=3D1)"
"         &nb= sp;   =20 ->  Hash  (cost=3D20.29..20.29 rows=3D1029 width=3D13) = (actual=20 time=3D0.627..0.627 rows=3D1029=20 loops=3D1)"
"         &nb= sp;         =20 ->  Seq Scan on manufacturer_manufacturer m  = (cost=3D0.00..20.29=20 rows=3D1029 width=3D13) (actual time=3D0.007..0.278 rows=3D1029 = loops=3D1)"
"Total=20 runtime: 5310.663 ms"
 

"Unique  (cost=3D43237.52..43266.80 rows=3D1029 = width=3D13) (actual=20 time=3D190.978..196.625 rows=3D276 loops=3D1)"
"  ->  = Sort =20 (cost=3D43237.52..43247.28 rows=3D3903 width=3D13) (actual = time=3D190.977..192.431=20 rows=3D11298 loops=3D1)"
"        = Sort Key:=20 m.id, m.name"
"        Sort = Method: =20 quicksort  Memory: = 1037kB"
"       =20 ->  Hash Join  (cost=3D134.14..43004.70 rows=3D3903 = width=3D13) (actual=20 time=3D5.006..155.188 rows=3D11298=20 loops=3D1)"
"         &nb= sp;   =20 Hash Cond: (p.manufacturer_id =3D=20 m.id)"
"          &n= bsp;  =20 ->  Nested Loop  (cost=3D100.99..42917.88 rows=3D3903 = width=3D4) (actual=20 time=3D4.363..146.421 rows=3D11298=20 loops=3D1)"
"         &nb= sp;         =20 ->  Bitmap Heap Scan on retailer_offer o  = (cost=3D100.99..13663.63=20 rows=3D3903 width=3D4) (actual time=3D4.345..29.871 rows=3D11298=20 loops=3D1)"
"         &nb= sp;           &nbs= p;   =20 Recheck Cond: ((retailer_id =3D 1710) AND=20 active)"
"          =             &= nbsp;  =20 ->  Bitmap Index Scan on idx_retaileroffer_retailerid =20 (cost=3D0.00..100.02 rows=3D3903 width=3D0) (actual time=3D2.368..2.368 = rows=3D11380=20 loops=3D1)"
"         &nb= sp;           &nbs= p;         =20 Index Cond: (retailer_id =3D=20 1710)"
"          &n= bsp;        =20 ->  Index Scan using product_product_pkey on product_product = p =20 (cost=3D0.00..7.48 rows=3D1 width=3D8) (actual time=3D0.008..0.009 = rows=3D1=20 loops=3D11298)"
"         = ;            =     =20 Index Cond: (p.id =3D=20 o.product_id)"
"         =     =20 ->  Hash  (cost=3D20.29..20.29 rows=3D1029 width=3D13) = (actual=20 time=3D0.634..0.634 rows=3D1029=20 loops=3D1)"
"         &nb= sp;         =20 ->  Seq Scan on manufacturer_manufacturer m  = (cost=3D0.00..20.29=20 rows=3D1029 width=3D13) (actual time=3D0.009..0.275 rows=3D1029 = loops=3D1)"
"Total=20 runtime: 196.716 ms"
 
 
 
Thanks
 

--
 
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/<= BR>
 
------=_NextPart_000_003D_01C9D24F.33770FE0--