Hi all, I've attached all the query in query.sql
I'm using postgres 7.3.4 on Linux version 2.4.26-custom
( /proc/sys/vm/overcommit_memory = 0 this time )
free :
total used free shared buffers cached
Mem: 1810212 1767384 42828 0 5604 1663908
-/+ buffers/cache: 97872 1712340
Swap: 505912 131304 374608
After I rebuilt the database, the query was fast (28255.12 msec).
After one night's insertion into the tables that the query select from,
the query all of a sudden uses up all resources , and the kernel
starts swapping, and I haven't seen the query actually finish when
this happens. I did vacuum analyze AND reindex, but that doesn't
help.
I attached the explain analyze of the query before this happens, and
the explain plan from when it actually happens that the query doesn't finish.
The one noticeable difference, was that before, it used merge joins, and
after, it used hash joins.
When the query was slow, I tried to : set enable_hashjoin to off
for this query, and the query finished relatively fast again (316245.16 msec)
I attached the output of that explain analyze as well, as well as the postgres
settings.
Can anyone shed some light on what's happening here. I can't figure it out.
Kind Regards
Stefan
query.sql
Description: Binary data
Aggregate (cost=87597.84..89421.82 rows=2702 width=484) (actual
time=22727.88..28164.74 rows=12040 loops=1)
Filter: ((((sum(qty_on_hand) + sum(qty_in_goods)) - sum(qty_dp)) -
sum(qty_out_goods)) < 0::numeric)
-> Group (cost=87597.84..88003.17 rows=27022 width=484) (actual
time=22727.45..23242.01 rows=42705 loops=1)
-> Sort (cost=87597.84..87665.40 rows=27022 width=484) (actual
time=22727.43..22756.74 rows=42705 loops=1)
Sort Key: s.sku, s.stktype_code, i.sku_descr, br.cluster_code,
br.cluster_descr
-> Merge Join (cost=84388.96..85608.78 rows=27022 width=484) (actual
time=20303.41..21814.25 rows=42705 loops=1)
Merge Cond: (("outer".group_code = "inner".group_code) AND
("outer".sku = "inner".sku))
-> Sort (cost=64472.34..64489.67 rows=6930 width=388) (actual
time=16503.56..16530.23 rows=42705 loops=1)
Sort Key: s.group_code, os.sku
-> Merge Join (cost=63006.13..64030.25 rows=6930
width=388) (actual time=14394.48..15794.71 rows=42705 loops=1)
Merge Cond: (("outer".cluster_brn =
"inner".cluster_code) AND ("outer".sku = "inner".sku))
-> Index Scan using old_sku_uidx1 on old_sku os
(cost=0.00..797.79 rows=17799 width=64) (actual time=0.02..47.66 rows=17799 loops=1)
-> Sort (cost=63006.13..63045.07 rows=15574
width=324) (actual time=14393.77..14556.50 rows=132703 loops=1)
Sort Key: br.cluster_code, s.sku
-> Merge Join (cost=61645.75..61921.64
rows=15574 width=324) (actual time=4862.56..6078.94 rows=132703 loops=1)
Merge Cond: ("outer".brn_code =
"inner".brn_code)
-> Sort (cost=61587.79..61626.73
rows=15574 width=228) (actual time=4859.23..5043.43 rows=132703 loops=1)
Sort Key: s.brn_code
-> Index Scan using stmst_sku_idx4
on stmst_sku s (cost=0.00..60503.30 rows=15574 width=228) (actual time=0.07..1078.30
rows=132703 loops=1)
Index Cond: (fpp_code =
'200408'::text)
-> Sort (cost=57.96..59.62 rows=667
width=96) (actual time=3.26..91.93 rows=133005 loops=1)
Sort Key: br.brn_code
-> Seq Scan on master_branch_descr
br (cost=0.00..26.67 rows=667 width=96) (actual time=0.02..1.13 rows=667 loops=1)
-> Sort (cost=19916.61..20306.53 rows=155968 width=96) (actual
time=3797.71..3914.26 rows=184223 loops=1)
Sort Key: i.group_code, i.sku
-> Seq Scan on master_sku_descr i (cost=0.00..6463.68
rows=155968 width=96) (actual time=0.01..293.74 rows=155968 loops=1)
SubPlan
-> Aggregate (cost=6.02..6.02 rows=1 width=8) (actual time=0.04..0.04 rows=1
loops=14456)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding
(cost=0.00..6.01 rows=1 width=8) (actual time=0.03..0.03 rows=0 loops=14456)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code =
$2))
-> Aggregate (cost=6.02..6.02 rows=1 width=8) (actual time=0.04..0.04 rows=1
loops=14456)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding
(cost=0.00..6.01 rows=1 width=8) (actual time=0.03..0.03 rows=0 loops=14456)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code =
$2))
-> Aggregate (cost=6.02..6.02 rows=1 width=8) (actual time=0.03..0.03 rows=1
loops=12031)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding
(cost=0.00..6.01 rows=1 width=8) (actual time=0.03..0.03 rows=0 loops=12031)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code =
$2))
-> Aggregate (cost=11.96..11.96 rows=1 width=82) (actual time=0.16..0.16 rows=1
loops=14456)
-> Nested Loop (cost=0.00..11.95 rows=1 width=82) (actual time=0.08..0.15
rows=3 loops=14456)
-> Index Scan using stmst_sku_idx3 on stmst_sku k (cost=0.00..6.02
rows=1 width=50) (actual time=0.06..0.08 rows=3 loops=14456)
Index Cond: ((fpp_code = '200408'::text) AND (sku = $1) AND
(stktype_code = $2))
-> Index Scan using master_branch_descr_pkey on master_branch_descr
b (cost=0.00..5.92 rows=1 width=32) (actual time=0.02..0.02 rows=1 loops=42705)
Index Cond: ("outer".brn_code = b.brn_code)
Filter: ((brn_code <> cluster_code) AND (cluster_code = $0))
Total runtime: 28255.12 msec
(44 rows)
Aggregate (cost=316107.28..316114.91 rows=11 width=322)
Filter: ((((sum(qty_on_hand) + sum(qty_in_goods)) - sum(qty_dp)) -
sum(qty_out_goods)) < 0::numeric)
-> Group (cost=316107.28..316108.98 rows=113 width=322)
-> Sort (cost=316107.28..316107.57 rows=113 width=322)
Sort Key: s.sku, s.stktype_code, i.sku_descr, br.cluster_code,
br.cluster_descr
-> Hash Join (cost=308622.49..316103.43 rows=113 width=322)
Hash Cond: ("outer".sku = "inner".sku)
Join Filter: ("inner".group_code = "outer".group_code)
-> Seq Scan on master_sku_descr i (cost=0.00..6467.08
rows=156008 width=96)
-> Hash (cost=308618.04..308618.04 rows=1781 width=226)
-> Hash Join (cost=1698.78..308618.04 rows=1781 width=226)
Hash Cond: ("outer".sku = "inner".sku)
Join Filter: ("outer".brn_code = "inner".brn_code)
-> Seq Scan on stmst_sku s (cost=0.00..284677.69
rows=356150 width=106)
Filter: (fpp_code = '200408'::text)
-> Hash (cost=1550.41..1550.41 rows=59347 width=120)
-> Merge Join (cost=57.89..1550.41 rows=59347
width=120)
Merge Cond: ("outer".cluster_brn =
"inner".cluster_code)
-> Index Scan using old_sku_uidx1 on
old_sku os (cost=0.00..702.79 rows=17822 width=24)
-> Sort (cost=57.89..59.56 rows=666
width=96)
Sort Key: br.cluster_code
-> Seq Scan on master_branch_descr
br (cost=0.00..26.66 rows=666 width=96)
SubPlan
-> Aggregate (cost=23.73..23.73 rows=1 width=8)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding
(cost=0.00..23.70 rows=6 width=8)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code =
$2))
-> Aggregate (cost=23.73..23.73 rows=1 width=8)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding
(cost=0.00..23.70 rows=6 width=8)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code =
$2))
-> Aggregate (cost=23.73..23.73 rows=1 width=8)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding
(cost=0.00..23.70 rows=6 width=8)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code =
$2))
-> Aggregate (cost=32.16..32.16 rows=1 width=50)
-> Merge Join (cost=32.11..32.15 rows=1 width=50)
Merge Cond: ("outer".brn_code = "inner".brn_code)
-> Sort (cost=14.38..14.39 rows=3 width=32)
Sort Key: b.brn_code
-> Index Scan using master_branch_descr_idx6 on
master_branch_descr b (cost=0.00..14.35 rows=3 width=32)
Index Cond: (cluster_code = $0)
Filter: (brn_code <> cluster_code)
-> Sort (cost=17.73..17.74 rows=4 width=18)
Sort Key: k.brn_code
-> Index Scan using stmst_sku_idx3 on stmst_sku k
(cost=0.00..17.69 rows=4 width=18)
Index Cond: ((fpp_code = '200408'::text) AND (sku = $1)
AND (stktype_code = $2))
(44 rows)
Aggregate (cost=354420.02..354427.65 rows=11 width=322) (actual
time=246825.06..316125.53 rows=12050 loops=1)
Filter: ((((sum(qty_on_hand) + sum(qty_in_goods)) - sum(qty_dp)) -
sum(qty_out_goods)) < 0::numeric)
-> Group (cost=354420.02..354421.72 rows=113 width=322) (actual
time=246701.96..247283.96 rows=42930 loops=1)
-> Sort (cost=354420.02..354420.30 rows=113 width=322) (actual
time=246701.94..246736.97 rows=42930 loops=1)
Sort Key: s.sku, s.stktype_code, i.sku_descr, br.cluster_code,
br.cluster_descr
-> Merge Join (cost=354298.16..354416.17 rows=113 width=322) (actual
time=245205.55..245634.53 rows=42930 loops=1)
Merge Cond: (("outer".brn_code = "inner".brn_code) AND
("outer".cluster_brn = "inner".cluster_code))
-> Sort (cost=354240.27..354296.76 rows=22595 width=226)
(actual time=245179.07..245205.75 rows=42930 loops=1)
Sort Key: s.brn_code, os.cluster_brn
-> Merge Join (cost=348924.92..352606.20 rows=22595
width=226) (actual time=241551.96..244357.21 rows=42930 loops=1)
Merge Cond: ("outer".sku = "inner".sku)
-> Merge Join (cost=348924.92..351824.65 rows=22595
width=202) (actual time=241500.21..243397.42 rows=132883 loops=1)
Merge Cond: (("outer".sku = "inner".sku) AND
("outer".group_code = "inner".group_code))
-> Sort (cost=329001.17..329891.54
rows=356150 width=106) (actual time=238575.25..238742.16 rows=132883 loops=1)
Sort Key: s.sku, s.group_code
-> Seq Scan on stmst_sku s
(cost=0.00..284677.69 rows=356150 width=106) (actual time=219190.28..235315.66
rows=132883 loops=1)
Filter: (fpp_code = '200408'::text)
-> Sort (cost=19923.75..20313.77 rows=156008
width=96) (actual time=2924.57..3048.34 rows=185259 loops=1)
Sort Key: i.sku, i.group_code
-> Seq Scan on master_sku_descr i
(cost=0.00..6467.08 rows=156008 width=96) (actual time=1.62..976.63 rows=156008
loops=1)
-> Index Scan using old_sku_idx1 on old_sku os
(cost=0.00..398.04 rows=17822 width=24) (actual time=49.45..209.45 rows=46294 loops=1)
-> Sort (cost=57.89..59.56 rows=666 width=96) (actual
time=26.41..54.70 rows=43232 loops=1)
Sort Key: br.brn_code, br.cluster_code
-> Seq Scan on master_branch_descr br (cost=0.00..26.66
rows=666 width=96) (actual time=8.04..24.21 rows=666 loops=1)
SubPlan
-> Aggregate (cost=23.73..23.73 rows=1 width=8) (actual time=0.06..0.06 rows=1
loops=14481)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding
(cost=0.00..23.70 rows=6 width=8) (actual time=0.04..0.05 rows=0 loops=14481)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code =
$2))
-> Aggregate (cost=23.73..23.73 rows=1 width=8) (actual time=0.04..0.04 rows=1
loops=14481)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding
(cost=0.00..23.70 rows=6 width=8) (actual time=0.03..0.03 rows=0 loops=14481)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code =
$2))
-> Aggregate (cost=23.73..23.73 rows=1 width=8) (actual time=0.03..0.03 rows=1
loops=12042)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding
(cost=0.00..23.70 rows=6 width=8) (actual time=0.03..0.03 rows=0 loops=12042)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code =
$2))
-> Aggregate (cost=32.16..32.16 rows=1 width=50) (actual time=4.53..4.53 rows=1
loops=14481)
-> Merge Join (cost=32.11..32.15 rows=1 width=50) (actual time=4.45..4.52
rows=3 loops=14481)
Merge Cond: ("outer".brn_code = "inner".brn_code)
-> Sort (cost=14.38..14.39 rows=3 width=32) (actual time=3.84..4.01
rows=271 loops=14481)
Sort Key: b.brn_code
-> Index Scan using master_branch_descr_idx6 on
master_branch_descr b (cost=0.00..14.35 rows=3 width=32) (actual time=0.02..2.14
rows=564 loops=14481)
Index Cond: (cluster_code = $0)
Filter: (brn_code <> cluster_code)
-> Sort (cost=17.73..17.74 rows=4 width=18) (actual time=0.14..0.14
rows=3 loops=14481)
Sort Key: k.brn_code
-> Index Scan using stmst_sku_idx3 on stmst_sku k
(cost=0.00..17.69 rows=4 width=18) (actual time=0.08..0.11 rows=3 loops=14481)
Index Cond: ((fpp_code = '200408'::text) AND (sku = $1)
AND (stktype_code = $2))
Total runtime: 316245.16 msec
(47 rows)
shared_buffers = 110592 wal_buffers = 400 sort_mem = 30720 vacuum_mem = 10240 checkpoint_segments = 30 commit_delay = 5000 commit_siblings = 100 effective_cache_size = 201413
stmst_sku.sql
Description: Binary data
old_sku.sql
Description: Binary data
master_sku_descr.sql
Description: Binary data
master_branch_descr.sql
Description: Binary data
gir_outstanding.sql
Description: Binary data
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
