Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 08CAF9FA25C for ; Tue, 18 Sep 2007 17:07:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 83894-06 for ; Tue, 18 Sep 2007 17:06:56 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from tomts20-srv.bellnexxia.net (tomts20.bellnexxia.net [209.226.175.74]) by postgresql.org (Postfix) with ESMTP id BC0579FA056 for ; Tue, 18 Sep 2007 17:06:56 -0300 (ADT) Received: from serenity ([70.52.75.201]) by tomts20-srv.bellnexxia.net (InterMail vM.5.01.06.13 201-253-122-130-113-20050324) with ESMTP id <20070918200654.WVIB8273.tomts20-srv.bellnexxia.net@serenity> for ; Tue, 18 Sep 2007 16:06:54 -0400 From: "Carlo Stonebanks" To: References: <24950.1190086191@sss.pgh.pa.us> <983F6A492717496499CC6115662FA01D@serenity> <059701c7f9fd$d0f99c50$cb00a8c0@tridecap.com> In-Reply-To: <059701c7f9fd$d0f99c50$cb00a8c0@tridecap.com> Subject: Nested loops row estimates always too high Date: Tue, 18 Sep 2007 16:07:33 -0400 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0D04_01C7FA0E.074B25C0" X-Mailer: Microsoft Office Outlook 11 Thread-Index: Acf5pDajAl5EqG7LS9mGyqqTBZhiFwAFsd8wABBl/DAAC+/WIA== X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16480 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/313 X-Sequence-Number: 26858 This is a multi-part message in MIME format. ------=_NextPart_000_0D04_01C7FA0E.074B25C0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I am noticing that my queries are spending a lot of time in nested loops. The table/index row estimates are not bad, but the nested loops can be off by a factor of 50. In any case, they are always too high. If this is always occurring, is this an indication of a general configuration problem? Carlo select pp.provider_id, pp.provider_practice_id, nearby.distance from mdx_core.provider_practice as pp join mdx_core.facility as f on f.facility_id = pp.facility_id join (select * from mdx_core.zips_in_mile_range('08820', 10) where zip > '') as nearby on f.default_country_code = 'US' and f.default_postal_code = nearby.zip and pp.facility_address_id is NULL union select pp.provider_id, pp.provider_practice_id, nearby.distance from mdx_core.provider_practice as pp join mdx_core.facility_address as fa on fa.facility_address_id = pp.facility_address_id join mdx_core.address as a on a.address_id = fa.address_id join (select * from mdx_core.zips_in_mile_range('08820', 10) where zip > '') as nearby on a.country_code = 'US' and a.postal_code = nearby.zip Unique (cost=67605.91..67653.18 rows=4727 width=16) (actual time=8634.618..8637.918 rows=907 loops=1) -> Sort (cost=67605.91..67617.73 rows=4727 width=16) (actual time=8634.615..8635.651 rows=907 loops=1) Sort Key: provider_id, provider_practice_id, distance -> Append (cost=0.00..67317.41 rows=4727 width=16) (actual time=176.056..8632.429 rows=907 loops=1) -> Nested Loop (cost=0.00..38947.07 rows=3143 width=16) (actual time=176.054..7867.962 rows=872 loops=1) -> Nested Loop (cost=0.00..11520.79 rows=8121 width=12) (actual time=169.372..3041.010 rows=907 loops=1) -> Function Scan on zips_in_mile_range (cost=0.00..15.00 rows=333 width=40) (actual time=151.479..151.671 rows=66 loops=1) Filter: (zip > ''::text) -> Index Scan using facility_country_postal_code_idx on facility f (cost=0.00..34.25 rows=24 width=15) (actual time=4.969..43.740 rows=14 loops=66) Index Cond: ((f.default_country_code = 'US'::bpchar) AND ((f.default_postal_code)::text = zips_in_mile_range.zip)) -> Index Scan using provider_practice_facility_idx on provider_practice pp (cost=0.00..3.36 rows=1 width=12) (actual time=4.915..5.316 rows=1 loops=907) Index Cond: (f.facility_id = pp.facility_id) Filter: (facility_address_id IS NULL) -> Nested Loop (cost=0.00..28323.07 rows=1584 width=16) (actual time=170.310..762.472 rows=35 loops=1) -> Nested Loop (cost=0.00..7791.77 rows=1579 width=12) (actual time=170.289..612.579 rows=36 loops=1) -> Nested Loop (cost=0.00..2595.96 rows=712 width=12) (actual time=167.017..354.261 rows=29 loops=1) -> Function Scan on zips_in_mile_range (cost=0.00..15.00 rows=333 width=40) (actual time=150.188..150.312 rows=66 loops=1) Filter: (zip > ''::text) -> Index Scan using address_country_postal_code_address_idx on address a (cost=0.00..7.73 rows=2 width=17) (actual time=2.483..3.086 rows=0 loops=66) Index Cond: ((a.country_code = 'US'::bpchar) AND ((a.postal_code)::text = zips_in_mile_range.zip)) -> Index Scan using facility_address_address_idx on facility_address fa (cost=0.00..7.15 rows=12 width=8) (actual time=7.652..8.901 rows=1 loops=29) Index Cond: (a.address_id = fa.address_id) -> Index Scan using provider_practice_facility_address_idx on provider_practice pp (cost=0.00..12.80 rows=16 width=12) (actual time=4.156..4.158 rows=1 loops=36) Index Cond: (fa.facility_address_id = pp.facility_address_id) Total runtime: 8639.066 ms ------=_NextPart_000_0D04_01C7FA0E.074B25C0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I am noticing that my queries are spending a lot of time in = nested loops. The table/index row estimates are not bad, but the nested loops = can be off by a factor of 50. In any case, they are always too = high.

 

If this is always occurring, is this an indication of a general configuration problem?

 

Carlo

 

      = select

         = pp.provider_id,

         pp.provider_practice_id,

         = nearby.distance

      from mdx_core.provider_practice = as pp

      join mdx_core.facility as = f

      on f.facility_id =3D = pp.facility_id

      join (select * from = mdx_core.zips_in_mile_range('08820', 10) where zip > '') as nearby

      on f.default_country_code =3D = 'US'

         and f.default_postal_code =3D nearby.zip

         and pp.facility_address_id is NULL

      union = select

         = pp.provider_id,

         = pp.provider_practice_id,

         = nearby.distance

      from mdx_core.provider_practice = as pp

      join mdx_core.facility_address as = fa

      on fa.facility_address_id =3D pp.facility_address_id

      join mdx_core.address as = a

      on a.address_id =3D = fa.address_id

      join (select * from mdx_core.zips_in_mile_range('08820', 10) where zip > '') as = nearby

      on a.country_code =3D = 'US'

      and a.postal_code =3D = nearby.zip

 

Unique  (cost=3D67605.91..67653.18 rows=3D4727 width=3D16) = (actual time=3D8634.618..8637.918 rows=3D907 = loops=3D1)

  ->  Sort  (cost=3D67605.91..67617.73 = rows=3D4727 width=3D16) (actual time=3D8634.615..8635.651 rows=3D907 = loops=3D1)

        Sort Key: = provider_id, provider_practice_id, distance

        ->  = Append  (cost=3D0.00..67317.41 rows=3D4727 width=3D16) (actual = time=3D176.056..8632.429 rows=3D907 loops=3D1)

  =             -= >  Nested Loop  = (cost=3D0.00..38947.07 rows=3D3143 width=3D16) (actual time=3D176.054..7867.962 rows=3D872 = loops=3D1)

           = ;         ->  Nested Loop  (cost=3D0.00..11520.79 rows=3D8121 width=3D12) (actual = time=3D169.372..3041.010 rows=3D907 loops=3D1)

          =             &= nbsp;   ->  Function Scan on zips_in_mile_range  (cost=3D0.00..15.00 rows=3D333 = width=3D40) (actual time=3D151.479..151.671 rows=3D66 = loops=3D1)

           = ;            =          Filter: (zip > ''::text)

           = ;            =    ->  Index Scan using facility_country_postal_code_idx on = facility f  (cost=3D0.00..34.25 rows=3D24 width=3D15) (actual = time=3D4.969..43.740 rows=3D14 loops=3D66)

           = ;            =          Index Cond: ((f.default_country_code =3D 'US'::bpchar) AND ((f.default_postal_code)::text =3D = zips_in_mile_range.zip))

           = ;         ->  Index Scan using provider_practice_facility_idx on provider_practice pp  (cost=3D0.00..3.36 rows=3D1 width=3D12) = (actual time=3D4.915..5.316 rows=3D1 loops=3D907)

           = ;               I= ndex Cond: (f.facility_id =3D pp.facility_id)

           = ;            =    Filter: (facility_address_id IS NULL)

           = ;   ->  Nested Loop  (cost=3D0.00..28323.07 rows=3D1584 width=3D16) (actual = time=3D170.310..762.472 rows=3D35 loops=3D1)

           = ;         ->  Nested Loop  (cost=3D0.00..7791.77 rows=3D1579 width=3D12) (actual = time=3D170.289..612.579 rows=3D36 loops=3D1)

           = ;            =    ->  Nested Loop  (cost=3D0.00..2595.96 rows=3D712 width=3D12) (actual = time=3D167.017..354.261 rows=3D29 loops=3D1)

           = ;                    &= nbsp;->  Function Scan on zips_in_mile_range  (cost=3D0.00..15.00 rows=3D333 = width=3D40) (actual time=3D150.188..150.312 rows=3D66 = loops=3D1)

           = ;            =             &= nbsp;  Filter: (zip > ''::text)

           = ;            =          ->  Index Scan using address_country_postal_code_address_idx on = address a  (cost=3D0.00..7.73 rows=3D2 width=3D17) (actual = time=3D2.483..3.086 rows=3D0 loops=3D66)

           = ;            =             &= nbsp;  Index Cond: ((a.country_code =3D 'US'::bpchar) AND = ((a.postal_code)::text =3D zips_in_mile_range.zip))

           = ;            =    ->  Index Scan using facility_address_address_idx on = facility_address fa  (cost=3D0.00..7.15 rows=3D12 width=3D8) (actual = time=3D7.652..8.901 rows=3D1 loops=3D29)

           = ;            =          Index Cond: (a.address_id =3D = fa.address_id)

           = ;         ->  Index Scan using provider_practice_facility_address_idx on provider_practice pp  (cost=3D0.00..12.80 rows=3D16 width=3D12) = (actual time=3D4.156..4.158 rows=3D1 loops=3D36)

           = ;            =    Index Cond: (fa.facility_address_id =3D = pp.facility_address_id)

Total runtime: 8639.066 ms

------=_NextPart_000_0D04_01C7FA0E.074B25C0--