Re: Nested Loop

Sorry,

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
message
lc_monetary = 'en_US.UTF-8' # locale for monetary
formatting
lc_numeric = 'en_US.UTF-8' # locale for number
formatting
lc_time = 'en_US.UTF-8' # locale for time formatting

all other are the default values.

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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: (rm.ck = 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 = rc.ky)
-> 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: (rm.sk = 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 =
rd.k)
-> 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
loops=1)
Filter: ((name)::text <>
'SYSTEM'::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
loops=1)
-> 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
loops=1)
-> 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:
>
> http://www.powerpostgresql.com/PerfList
>
> --
> Michael Fuhr
>

--
Regards
Gauri