pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stef <svb@ucs.co.za>
To: pgsql-performance@postgresql.org
Subject: Query kills machine.
Date: Tue, 24 Aug 2004 10:32:40 +0200
Message-ID: <20040824103240.4d909777@svb.ucs.co.za> (raw)

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 

Attachments:

  query.sql (application/octet-stream, 2.5K) download

explain_analyze_before.txt (text/plain, 4.8K) download 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)
explain_after.txt (text/plain, 3.5K) download 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)
explain_analyze_after_hashjoin_off.txt (text/plain, 5.0K) download 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)
settings.txt (text/plain, 174B) download 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 (application/octet-stream, 4.0K) download old_sku.sql (application/octet-stream, 263B) download master_sku_descr.sql (application/octet-stream, 1.4K) download master_branch_descr.sql (application/octet-stream, 2.1K) download gir_outstanding.sql (application/octet-stream, 1.5K) download

view thread (7+ messages)  latest in thread

Message-ID: <20040824103240.4d909777@svb.ucs.co.za>
Permalink:  ../20040824103240.4d909777@svb.ucs.co.za/
Also on:    postgresql.org/message-id/20040824103240.4d909777@svb.ucs.co.za

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: svb@ucs.co.za
  Subject: Re: Query kills machine.
  In-Reply-To: <20040824103240.4d909777@svb.ucs.co.za>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox