Skip site navigation (1) Skip section navigation (2)

Re: Nested Loop

From: "Gauri Kanekar" <meetgaurikanekar(at)gmail(dot)com>
To: "Michael Fuhr" <mike(at)fuhr(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Nested Loop
Date: 2007-03-26 15:03:34
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

this are the Confg Setting
max_connections = 100                   # (change requires restart)
shared_buffers = 300MB
work_mem = 256MB
max_fsm_pages = 400000
max_fsm_relations = 500
wal_buffers = 512
checkpoint_segments = 20
checkpoint_timeout = 900
enable_bitmapscan = on
enable_seqscan = off
enable_tidscan = on
random_page_cost = 2
cpu_index_tuple_cost = 0.001
effective_cache_size = 800MB
join_collapse_limit = 1                 # JOINs
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'                     # locale for system error
lc_monetary = 'en_US.UTF-8'                     # locale for monetary
lc_numeric = 'en_US.UTF-8'                      # locale for number
lc_time = 'en_US.UTF-8'                         # locale for time formatting

all other are the default values.

 HashAggregate  (cost=1116330.73..1116432.34 rows=6774 width=128) (actual
time=438565.297..440455.386 rows=646881 loops=1)
   ->  Hash Join  (cost=10802.93..1116093.64 rows=6774 width=128) (actual
time=1904.797..377717.036 rows=10438694 loops=1)
         Hash Cond: ( = rc.k)
         ->  Hash Join  (cost=10651.73..1115840.83 rows=6774 width=105)
(actual time=1890.765..347169.113 rows=10438694 loops=1)
               Hash Cond: (rm.chk =
               ->  Hash Join  (cost=9835.35..1114905.90 rows=6774 width=83)
(actual time=1873.463..317623.437 rows=10438694 loops=1)
                     Hash Cond: (rm.ckey = rc.k)
                     ->  Hash Join  (cost=615.77..1105533.91 rows=6774
width=85) (actual time=1842.309..288198.666 rows=10438694 loops=1)
                           Hash Cond: ( = rs.k)
                           ->  Hash Join  (cost=77.32..1104885.39 rows=6774
width=58) (actual time=1831.908..259147.154 rows=10438694 loops=1)
                                 Hash Cond: (rm.advk = ra.k)
                                 ->  Nested Loop
(cost=0.00..1104714.83rows=6801 width=44) (actual time=
1820.153..229779.814 rows=10945938 loops=1)
                                       Join Filter: (rm.nk = rn.k)
                                       ->  Index Scan using r_idx on rn
(cost=0.00..4.27 rows=1 width=4) (actual time=0.093..0.095 rows=1 loops=1)
                                             Index Cond: (id = 607)
                                       ->  Nested Loop  (cost=
0.00..1104370.50 rows=27205 width=48) (actual
time=7.920..202878.054rows=10945998 loops=1)
                                             ->  Index Scan using
rpts_ldt_idx on rd  (cost=0.00..4.27 rows=1 width=12) (actual time=
0.097..0.352 rows=30 loops=1)
                                                   Index Cond: ((sdt >=
'2006-12-01 00:00:00'::timestamp without time zone) AND (sd <= '2006-12-30
00:00:00'::timestamp without time zone))
                                             ->  Index Scan using rmidx on
rm  (cost=0.00..1100192.24 rows=333919 width=44) (actual time=
3.109..5835.861 rows=364867 loops=30)
                                                   Index Cond: (rmdkey =
                                 ->  Hash  (cost=68.15..68.15 rows=734
width=22) (actual time=11.692..11.692 rows=734 loops=1)
                                       ->  Index Scan using radvki on radvt
(cost=0.00..68.15 rows=734 width=22) (actual time=9.112..10.517 rows=734
                                             Filter: ((name)::text <>
                           ->  Hash  (cost=500.35..500.35 rows=3048
width=35) (actual time=10.377..10.377 rows=3048 loops=1)
                                 ->  Index Scan using rskidx on rs  (cost=
0.00..500.35 rows=3048 width=35) (actual time=0.082..5.589 rows=3048
                     ->  Hash  (cost=9118.63..9118.63 rows=8076 width=6)
(actual time=31.124..31.124 rows=8076 loops=1)
                           ->  Index Scan using rcridx on rcr  (cost=
0.00..9118.63 rows=8076 width=6) (actual time=2.036..19.218 rows=8076
               ->  Hash  (cost=769.94..769.94 rows=3715 width=30) (actual
time=17.275..17.275 rows=3715 loops=1)
                     ->  Index Scan using ridx on rcl
(cost=0.00..769.94rows=3715 width=30) (actual time=
4.238..11.432 rows=3715 loops=1)
         ->  Hash  (cost=120.38..120.38 rows=2466 width=31) (actual time=
14.010..14.010 rows=2466 loops=1)
               ->  Index Scan using rckdx on rcpn
(cost=0.00..120.38rows=2466 width=31) (actual time=
4.564..9.926 rows=2466 loops=1)
 Total runtime: 441153.878 ms
(32 rows)

we are using 8.2 version

On 3/26/07, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
> On Mon, Mar 26, 2007 at 05:34:39PM +0530, Gauri Kanekar wrote:
> > how to speedup nested loop queries and by which parameters.
> Please post a query you're trying to tune and the EXPLAIN ANALYZE
> output, as well as any changes you've already made in postgresql.conf
> or configuration variables you've set in a particular session.
> Without more information we can't give much advice other than to
> make sure you're vacuuming and analyzing the tables often enough
> to keep them from becoming bloated with dead rows and to keep the
> statistics current, and to review a configuration checklist such
> as this one:
> --
> Michael Fuhr


In response to


pgsql-performance by date

Next:From: Dave DutcherDate: 2007-03-26 15:38:12
Subject: Re: Nested Loop
Previous:From: Michael FuhrDate: 2007-03-26 14:05:49
Subject: Re: Nested Loop

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group