pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Jona <jonanews@oismail.com>
To: pgsql-performance@postgresql.org
Subject: Bad choice of query plan from PG 7.3.6 to PG 7.3.9 part 1b
Date: Tue, 03 May 2005 19:56:44 +0200
Message-ID: <4277BB5C.3000902@oismail.com> (raw )
Please refer to part 1a for questions and part 2 for more queries and
query plans.
Why won't this list accept my questions and sample data in one mail???
/Jona
----------------------------------------------------------------------------------------------------
Query 1:
EXPLAIN ANALYZE
SELECT DISTINCT StatConTrans_Tbl.id, Code_Tbl.sysnm AS code,
PriceCat_Tbl.amount AS price, Country_Tbl.currency,
CreditsCat_Tbl.amount AS credits, Info_Tbl.title, Info_Tbl.description
FROM (SCT2SubCatType_Tbl
INNER JOIN SCT2Lang_Tbl ON SCT2SubCatType_Tbl.sctid = SCT2Lang_Tbl.sctid
INNER JOIN Language_Tbl ON SCT2Lang_Tbl.langid = Language_Tbl.id AND
Language_Tbl.sysnm = UPPER('us') AND Language_Tbl.enabled = true
INNER JOIN Info_Tbl ON SCT2SubCatType_Tbl.sctid = Info_Tbl.sctid AND
Language_Tbl.id = Info_Tbl.langid
INNER JOIN SubCatType_Tbl ON SCT2SubCatType_Tbl.subcattpid =
SubCatType_Tbl.id AND SubCatType_Tbl.enabled = true
INNER JOIN CatType_Tbl ON SubCatType_Tbl.cattpid = CatType_Tbl.id AND
CatType_Tbl.enabled = true
INNER JOIN SuperCatType_Tbl ON CatType_Tbl.spcattpid =
SuperCatType_Tbl.id AND SuperCatType_Tbl.enabled = true
INNER JOIN StatConTrans_Tbl ON SCT2SubCatType_Tbl.sctid =
StatConTrans_Tbl.id AND StatConTrans_Tbl.enabled = true
INNER JOIN Price_Tbl ON StatConTrans_Tbl.id = Price_Tbl.sctid AND
Price_Tbl.affid = 8
INNER JOIN PriceCat_Tbl ON Price_Tbl.prccatid = PriceCat_Tbl.id AND
PriceCat_Tbl.enabled = true
INNER JOIN Country_Tbl ON PriceCat_Tbl.cntid = Country_Tbl.id AND
Country_Tbl.enabled = true
INNER JOIN CreditsCat_Tbl ON Price_Tbl.crdcatid = CreditsCat_Tbl.id AND
CreditsCat_Tbl.enabled = true
INNER JOIN StatCon_Tbl ON StatConTrans_Tbl.id = StatCon_Tbl.sctid AND
StatCon_Tbl.ctpid = 1
INNER JOIN Code_Tbl ON SuperCatType_Tbl.id = Code_Tbl.spcattpid AND
Code_Tbl.affid = 8 AND Code_Tbl.cdtpid = 1)
WHERE SCT2SubCatType_Tbl.subcattpid = 79
ORDER BY StatConTrans_Tbl.id DESC
LIMIT 8 OFFSET 0
Plan on PostGre 7.3.6 on Red Hat Linux 3.2.3-39
"Limit (cost=178.59..178.61 rows=1 width=330) (actual time=22.77..28.51
rows=4 loops=1)"
" -> Unique (cost=178.59..178.61 rows=1 width=330) (actual
time=22.77..28.50 rows=4 loops=1)"
" -> Sort (cost=178.59..178.60 rows=1 width=330) (actual
time=22.76..22.85 rows=156 loops=1)"
" Sort Key: statcontrans_tbl.id, code_tbl.sysnm,
pricecat_tbl.amount, country_tbl.currency, creditscat_tbl.amount,
info_tbl.title, info_tbl.description"
" -> Hash Join (cost=171.19..178.58 rows=1 width=330)
(actual time=3.39..6.55 rows=156 loops=1)"
" Hash Cond: ("outer".cntid = "inner".id)"
" -> Nested Loop (cost=170.13..177.51 rows=1
width=312) (actual time=3.27..5.75 rows=156 loops=1)"
" Join Filter: ("inner".sctid = "outer".sctid)"
" -> Hash Join (cost=170.13..171.48 rows=1
width=308) (actual time=3.12..3.26 rows=4 loops=1)"
" Hash Cond: ("outer".crdcatid =
"inner".id)"
" -> Hash Join (cost=169.03..170.38
rows=1 width=300) (actual time=3.00..3.11 rows=4 loops=1)"
" Hash Cond: ("outer".spcattpid =
"inner".spcattpid)"
" -> Hash Join
(cost=167.22..168.56 rows=1 width=253) (actual time=2.88..2.97 rows=4
loops=1)"
" Hash Cond: ("outer".id =
"inner".prccatid)"
" -> Seq Scan on
pricecat_tbl (cost=0.00..1.29 rows=12 width=12) (actual time=0.04..0.08
rows=23 loops=1)"
" Filter: (enabled =
true)"
" -> Hash
(cost=167.21..167.21 rows=1 width=241) (actual time=2.80..2.80 rows=0
loops=1)"
" -> Nested Loop
(cost=3.77..167.21 rows=1 width=241) (actual time=1.31..2.79 rows=4
loops=1)"
" Join Filter:
("inner".sctid = "outer".sctid)"
" -> Nested
Loop (cost=3.77..161.19 rows=1 width=229) (actual time=1.19..2.60
rows=4 loops=1)"
" Join
Filter: ("outer".sctid = "inner".sctid)"
" -> Hash
Join (cost=3.77..155.17 rows=1 width=44) (actual time=1.07..2.37 rows=4
loops=1)"
"
Hash Cond: ("outer".langid = "inner".id)"
" ->
Nested Loop (cost=2.69..154.06 rows=7 width=40) (actual time=0.90..2.18
rows=8 loops=1)"
"
Join Filter: ("outer".sctid = "inner".sctid)"
"
-> Nested Loop (cost=2.69..21.30 rows=1 width=32) (actual
time=0.78..1.94 rows=4 loops=1)"
"
-> Nested Loop (cost=2.69..15.30 rows=1 width=28) (actual
time=0.66..1.76 rows=4 loops=1)"
"
-> Hash Join (cost=2.69..7.07 rows=1 width=20) (actual time=0.39..1.15
rows=154 loops=1)"
"
Hash Cond: ("outer".cattpid = "inner".id)"
"
-> Seq Scan on subcattype_tbl (cost=0.00..3.98 rows=79 width=8)
(actual time=0.03..0.35 rows=156 loops=1)"
"
Filter: (enabled = true)"
"
-> Hash (cost=2.68..2.68 rows=3 width=12) (actual time=0.31..0.31
rows=0 loops=1)"
"
-> Hash Join (cost=1.15..2.68 rows=3 width=12) (actual time=0.16..0.27
rows=31 loops=1)"
"
Hash Cond: ("outer".spcattpid = "inner".id)"
"
-> Seq Scan on cattype_tbl (cost=0.00..1.41 rows=16 width=8) (actual
time=0.04..0.09 rows=31 loops=1)"
"
Filter: (enabled = true)"
"
-> Hash (cost=1.14..1.14 rows=6 width=4) (actual time=0.06..0.06
rows=0 loops=1)"
"
-> Seq Scan on supercattype_tbl (cost=0.00..1.14 rows=6 width=4)
(actual time=0.03..0.05 rows=10 loops=1)"
"
Filter: (enabled = true)"
"
-> Index Scan using subcat_uq on sct2subcattype_tbl (cost=0.00..5.97
rows=1 width=8) (actual time=0.00..0.00 rows=0 loops=154)"
"
Index Cond: ((sct2subcattype_tbl.subcattpid = "outer".id) AND
(sct2subcattype_tbl.subcattpid = 79))"
"
-> Index Scan using statcontrans_pk on statcontrans_tbl
(cost=0.00..5.99 rows=1 width=4) (actual time=0.04..0.04 rows=1 loops=4)"
"
Index Cond: ("outer".sctid = statcontrans_tbl.id)"
"
Filter: (enabled = true)"
"
-> Index Scan using sct2lang_uq on sct2lang_tbl (cost=0.00..132.22
rows=43 width=8) (actual time=0.04..0.05 rows=2 loops=4)"
"
Index Cond: ("outer".id = sct2lang_tbl.sctid)"
" ->
Hash (cost=1.07..1.07 rows=1 width=4) (actual time=0.11..0.11 rows=0
loops=1)"
"
-> Seq Scan on language_tbl (cost=0.00..1.07 rows=1 width=4) (actual
time=0.10..0.11 rows=1 loops=1)"
"
Filter: (((sysnm)::text = 'US'::text) AND (enabled = true))"
" -> Index
Scan using info_uq on info_tbl (cost=0.00..6.00 rows=1 width=185)
(actual time=0.05..0.05 rows=1 loops=4)"
"
Index Cond: ((info_tbl.sctid = "outer".sctid) AND (info_tbl.langid =
"outer".langid))"
" -> Index Scan
using aff_price_uq on price_tbl (cost=0.00..6.01 rows=1 width=12)
(actual time=0.03..0.03 rows=1 loops=4)"
" Index
Cond: ((price_tbl.affid = 8) AND (price_tbl.sctid = "outer".sctid))"
" -> Hash (cost=1.81..1.81 rows=1
width=47) (actual time=0.08..0.08 rows=0 loops=1)"
" -> Seq Scan on code_tbl
(cost=0.00..1.81 rows=1 width=47) (actual time=0.04..0.07 rows=5 loops=1)"
" Filter: ((affid = 8)
AND (cdtpid = 1))"
" -> Hash (cost=1.09..1.09 rows=4
width=8) (actual time=0.06..0.06 rows=0 loops=1)"
" -> Seq Scan on creditscat_tbl
(cost=0.00..1.09 rows=4 width=8) (actual time=0.03..0.04 rows=7 loops=1)"
" Filter: (enabled = true)"
" -> Index Scan using ctp_statcon on
statcon_tbl (cost=0.00..6.01 rows=1 width=4) (actual time=0.05..0.31
rows=39 loops=4)"
" Index Cond: ((statcon_tbl.sctid =
"outer".sctid) AND (statcon_tbl.ctpid = 1))"
" -> Hash (cost=1.06..1.06 rows=2 width=18) (actual
time=0.06..0.06 rows=0 loops=1)"
" -> Seq Scan on country_tbl (cost=0.00..1.06
rows=2 width=18) (actual time=0.04..0.05 rows=4 loops=1)"
" Filter: (enabled = true)"
"Total runtime: 29.56 msec"
Plan on PostGre 7.3.9 on Red Hat Linux 3.2.3-49
"Limit (cost=545.53..545.60 rows=1 width=135) (actual
time=1251.71..1261.25 rows=4 loops=1)"
" -> Unique (cost=545.53..545.60 rows=1 width=135) (actual
time=1251.71..1261.24 rows=4 loops=1)"
" -> Sort (cost=545.53..545.54 rows=4 width=135) (actual
time=1251.70..1251.90 rows=156 loops=1)"
" Sort Key: statcontrans_tbl.id, code_tbl.sysnm,
pricecat_tbl.amount, country_tbl.currency, creditscat_tbl.amount,
info_tbl.title, info_tbl.description"
" -> Nested Loop (cost=485.61..545.49 rows=4 width=135)
(actual time=603.77..1230.96 rows=156 loops=1)"
" Join Filter: ("inner".sctid = "outer".sctid)"
" -> Hash Join (cost=485.61..486.06 rows=3
width=131) (actual time=541.87..542.22 rows=4 loops=1)"
" Hash Cond: ("outer".crdcatid = "inner".id)"
" -> Hash Join (cost=484.51..484.90 rows=3
width=123) (actual time=529.09..529.36 rows=4 loops=1)"
" Hash Cond: ("outer".spcattpid =
"inner".spcattpid)"
" -> Hash Join (cost=482.68..482.93
rows=3 width=114) (actual time=517.60..517.77 rows=4 loops=1)"
" Hash Cond: ("outer".cntid =
"inner".id)"
" -> Merge Join
(cost=481.60..481.80 rows=4 width=105) (actual time=517.36..517.43
rows=4 loops=1)"
" Merge Cond: ("outer".id =
"inner".prccatid)"
" -> Sort (cost=1.81..1.87
rows=23 width=12) (actual time=8.44..8.45 rows=6 loops=1)"
" Sort Key:
pricecat_tbl.id"
" -> Seq Scan on
pricecat_tbl (cost=0.00..1.29 rows=23 width=12) (actual time=8.31..8.37
rows=23 loops=1)"
" Filter:
(enabled = true)"
" -> Sort
(cost=479.80..479.81 rows=4 width=93) (actual time=508.87..508.87 rows=4
loops=1)"
" Sort Key:
price_tbl.prccatid"
" -> Nested Loop
(cost=13.69..479.75 rows=4 width=93) (actual time=444.70..508.81 rows=4
loops=1)"
" Join Filter:
("inner".sctid = "outer".sctid)"
" -> Nested
Loop (cost=13.69..427.04 rows=9 width=81) (actual time=444.60..508.62
rows=4 loops=1)"
" Join
Filter: ("outer".sctid = "inner".sctid)"
" ->
Nested Loop (cost=13.69..377.03 rows=8 width=44) (actual
time=345.13..398.38 rows=4 loops=1)"
"
Join Filter: ("outer".sctid = "inner".id)"
" ->
Hash Join (cost=13.69..327.32 rows=8 width=40) (actual
time=219.17..272.27 rows=4 loops=1)"
"
Hash Cond: ("outer".langid = "inner".id)"
"
-> Nested Loop (cost=12.61..325.92 rows=42 width=36) (actual
time=209.77..262.79 rows=8 loops=1)"
"
-> Hash Join (cost=12.61..106.32 rows=27 width=28) (actual
time=101.88..102.00 rows=4 loops=1)"
"
Hash Cond: ("outer".cattpid = "inner".id)"
"
-> Hash Join (cost=9.47..102.68 rows=33 width=16) (actual
time=84.14..84.21 rows=4 loops=1)"
"
Hash Cond: ("outer".subcattpid = "inner".id)"
"
-> Index Scan using subcat_uq on sct2subcattype_tbl (cost=0.00..92.56
rows=33 width=8) (actual time=83.33..83.37 rows=4 loops=1)"
"
Index Cond: (subcattpid = 79)"
"
-> Hash (cost=3.98..3.98 rows=156 width=8) (actual time=0.76..0.76
rows=0 loops=1)"
"
-> Seq Scan on subcattype_tbl (cost=0.00..3.98 rows=156 width=8)
(actual time=0.03..0.49 rows=156 loops=1)"
"
Filter: (enabled = true)"
"
-> Hash (cost=3.07..3.07 rows=27 width=12) (actual time=17.58..17.58
rows=0 loops=1)"
"
-> Hash Join (cost=1.16..3.07 rows=27 width=12) (actual
time=17.30..17.52 rows=31 loops=1)"
"
Hash Cond: ("outer".spcattpid = "inner".id)"
"
-> Seq Scan on cattype_tbl (cost=0.00..1.41 rows=31 width=8) (actual
time=0.02..0.12 rows=31 loops=1)"
"
Filter: (enabled = true)"
"
-> Hash (cost=1.14..1.14 rows=10 width=4) (actual time=17.09..17.09
rows=0 loops=1)"
"
-> Seq Scan on supercattype_tbl (cost=0.00..1.14 rows=10 width=4)
(actual time=17.05..17.07 rows=10 loops=1)"
"
Filter: (enabled = true)"
"
-> Index Scan using sct2lang_uq on sct2lang_tbl (cost=0.00..8.13
rows=2 width=8) (actual time=26.97..40.18 rows=2 loops=4)"
"
Index Cond: ("outer".sctid = sct2lang_tbl.sctid)"
"
-> Hash (cost=1.07..1.07 rows=1 width=4) (actual time=9.04..9.04
rows=0 loops=1)"
"
-> Seq Scan on language_tbl (cost=0.00..1.07 rows=1 width=4) (actual
time=9.02..9.03 rows=1 loops=1)"
"
Filter: (((sysnm)::text = 'US'::text) AND (enabled = true))"
" ->
Index Scan using statcontrans_pk on statcontrans_tbl (cost=0.00..5.88
rows=1 width=4) (actual time=31.51..31.52 rows=1 loops=4)"
"
Index Cond: (statcontrans_tbl.id = "outer".sctid)"
"
Filter: (enabled = true)"
" -> Index
Scan using info_uq on info_tbl (cost=0.00..5.93 rows=1 width=37)
(actual time=27.54..27.54 rows=1 loops=4)"
"
Index Cond: ((info_tbl.sctid = "outer".sctid) AND (info_tbl.langid =
"outer".langid))"
" -> Index Scan
using aff_price_uq on price_tbl (cost=0.00..5.88 rows=1 width=12)
(actual time=0.03..0.03 rows=1 loops=4)"
" Index
Cond: ((price_tbl.affid = 8) AND (price_tbl.sctid = "outer".sctid))"
" -> Hash (cost=1.06..1.06 rows=4
width=9) (actual time=0.05..0.05 rows=0 loops=1)"
" -> Seq Scan on
country_tbl (cost=0.00..1.06 rows=4 width=9) (actual time=0.02..0.03
rows=4 loops=1)"
" Filter: (enabled =
true)"
" -> Hash (cost=1.81..1.81 rows=8
width=9) (actual time=11.31..11.31 rows=0 loops=1)"
" -> Seq Scan on code_tbl
(cost=0.00..1.81 rows=8 width=9) (actual time=11.24..11.29 rows=5 loops=1)"
" Filter: ((affid = 8) AND
(cdtpid = 1))"
" -> Hash (cost=1.09..1.09 rows=7 width=8)
(actual time=12.59..12.59 rows=0 loops=1)"
" -> Seq Scan on creditscat_tbl
(cost=0.00..1.09 rows=7 width=8) (actual time=12.55..12.57 rows=7 loops=1)"
" Filter: (enabled = true)"
" -> Index Scan using ctp_statcon on statcon_tbl
(cost=0.00..20.40 rows=5 width=4) (actual time=27.97..171.84 rows=39
loops=4)"
" Index Cond: ((statcon_tbl.sctid =
"outer".sctid) AND (statcon_tbl.ctpid = 1))"
"Total runtime: 1299.02 msec"
view thread (12+ messages) latest in thread
Message-ID: <4277BB5C.3000902@oismail.com>
Permalink: ../4277BB5C.3000902@oismail.com/
Also on: postgresql.org/message-id/4277BB5C.3000902@oismail.com
copy link · copy postgr.es
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: jonanews@oismail.com
Subject: Re: Bad choice of query plan from PG 7.3.6 to PG 7.3.9 part 1b
In-Reply-To: <4277BB5C.3000902@oismail.com>
* 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