Re: View performance question - Mailing list pgsql-sql
| From | Josh Berkus |
|---|---|
| Subject | Re: View performance question |
| Date | |
| Msg-id | web-76095@davinci.ethosmedia.com Whole thread Raw |
| In response to | Re: View performance question (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: View performance question
|
| List | pgsql-sql |
Tom,
OK. FIgured out the output.
I tried re-arranging the JOINS as you suggested. There was no impact on
gross performance (still 42 seconds to return the first row). And yes,
it is the classic "star" database.
What follows is the query plan.
-Josh
NOTICE: QUERY PLAN:
Hash Join (cost=1281.69..8085.70 rows=3104 width=1096) -> Hash Join (cost=1280.60..7960.36 rows=3104 width=1080)
-> Hash Join (cost=1279.55..7835.10 rows=3104 width=1064) -> Hash Join (cost=1277.67..7708.19
rows=3104
width=1048) -> Hash Join (cost=1276.41..7582.51 rows=3104
width=1032) -> Hash Join
(cost=1273.85..7376.62 rows=3104 width=1012) -> Hash Join (cost=1272.57..7173.31
rows=3104 width=996) -> Hash Join
(cost=1271.47..7047.95 rows=3104 width=980) -> Hash Join
(cost=1270.22..6844.69 rows=3104 width=972) -> Hash Join
(cost=1269.12..6719.33 rows=3104 width=956) -> Hash Join
(cost=1267.80..6515.92 rows=3104 width=948) -> Hash
Join (cost=1266.25..6312.06 rows=3104 width=932) ->
Hash Join (cost=1264.40..6185.20 rows=3104 width=916)
-> Hash Join (cost=1263.31..6059.87 rows=3104 width=900)
-> Hash Join (cost=1262.26..5934.61 rows=3104 width=884)
-> Hash Join (cost=1260.39..5807.70 rows=3104 width=868)
-> Hash Join (cost=1259.12..5682.01 rows=3104 width=852)
-> Hash Join (cost=1256.56..5476.13 rows=3104
width=832)
-> Hash Join (cost=1255.29..5272.82 rows=3104
width=816)
-> Hash Join (cost=1254.19..5147.46
rows=3104 width=800)
-> Hash Join
(cost=1252.94..4944.20 rows=3104 width=792)
-> Hash Join
(cost=1251.84..4818.84 rows=3104 width=776)
-> Hash Join
(cost=1250.51..4615.43 rows=3104 width=768)
-> Hash Join
(cost=1248.96..4411.57 rows=3104 width=752)
-> Hash
Join (cost=1247.11..4284.71 rows=3104 width=736)
->
Hash Join (cost=1245.76..4158.85 rows=3104 width=720)
-> Hash Join (cost=1244.66..4033.49 rows=3104 width=704)
-> Hash Join (cost=1243.62..3908.25 rows=3104 width=690)
-> Hash Join (cost=1242.34..3782.52 rows=3104 width=674)
-> Hash Join (cost=1241.30..3657.28 rows=3104 width=660)
-> Hash Join (cost=1240.17..3454.27 rows=3104
width=644)
-> Hash Join (cost=1239.05..3251.26 rows=3104
width=628)
-> Hash Join (cost=1238.01..3126.02
rows=3104 width=614)
-> Hash Join (cost=1236.97..3000.79
rows=3104 width=600)
-> Hash Join
(cost=1235.94..2875.56 rows=3104 width=586)
-> Hash Join
(cost=1234.85..2750.22 rows=3104 width=570)
-> Hash Join
(cost=1233.75..2624.86 rows=3104 width=556)
-> Hash Join
(cost=616.88..1374.95 rows=3104 width=416)
-> Seq
Scan on mergers (cost=0.00..231.04 rows=3104 width=276)
-> Hash
(cost=495.50..495.50 rows=5350 width=140)
->
Seq Scan on companies pc (cost=0.00..495.50 rows=5350 width=140)
-> Hash
(cost=495.50..495.50 rows=5350 width=140)
-> Seq
Scan on companies sc (cost=0.00..495.50 rows=5350 width=140)
-> Hash
(cost=1.08..1.08 rows=8 width=14)
-> Seq Scan
on merger_types (cost=0.00..1.08 rows=8 width=14)
-> Hash (cost=1.07..1.07
rows=7 width=16)
-> Seq Scan on
merger_intents (cost=0.00..1.07 rows=7 width=16)
-> Hash (cost=1.03..1.03
rows=3 width=14)
-> Seq Scan on
revenue_types revenue_types1 (cost=0.00..1.03 rows=3 width=14)
-> Hash (cost=1.03..1.03 rows=3
width=14)
-> Seq Scan on revenue_types
revenue_types2 (cost=0.00..1.03 rows=3 width=14)
-> Hash (cost=1.03..1.03 rows=3 width=14)
-> Seq Scan on revenue_types
cash_types (cost=0.00..1.03 rows=3 width=14)
-> Hash (cost=1.10..1.10 rows=10 width=16)
-> Seq Scan on earnings_types
earnings_types1 (cost=0.00..1.10 rows=10 width=16)
-> Hash (cost=1.10..1.10 rows=10 width=16)
-> Seq Scan on earnings_types earnings_types2
(cost=0.00..1.10 rows=10 width=16)
-> Hash (cost=1.03..1.03 rows=3 width=14)
-> Seq Scan on amount_types (cost=0.00..1.03 rows=3
width=14)
-> Hash (cost=1.23..1.23 rows=23 width=16)
-> Seq Scan on accounting_methods (cost=0.00..1.23 rows=23
width=16)
-> Hash (cost=1.03..1.03 rows=3 width=14)
-> Seq Scan on entry_statuses (cost=0.00..1.03 rows=3 width=14)
-> Hash (cost=1.08..1.08 rows=8 width=16)
-> Seq Scan on muv_sources (cost=0.00..1.08 rows=8 width=16)
->
Hash (cost=1.28..1.28 rows=28 width=16)
-> Seq Scan on classifications (cost=0.00..1.28 rows=28 width=16)
-> Hash
(cost=1.68..1.68 rows=68 width=16)
->
Seq Scan on states pc_states (cost=0.00..1.68 rows=68 width=16)
-> Hash
(cost=1.44..1.44 rows=44 width=16)
-> Seq Scan
on countries pc_countries (cost=0.00..1.44 rows=44 width=16)
-> Hash
(cost=1.26..1.26 rows=26 width=8)
-> Seq Scan on
state_regions pc_srt (cost=0.00..1.26 rows=26 width=8)
-> Hash (cost=1.08..1.08
rows=8 width=16)
-> Seq Scan on regions
pc_sr (cost=0.00..1.08 rows=8 width=16)
-> Hash (cost=1.20..1.20 rows=20
width=8) -> Seq Scan on
country_regions pc_cot (cost=0.00..1.20 rows=20 width=8)
-> Hash (cost=1.08..1.08 rows=8
width=16)
-> Seq Scan on regions pc_cr
(cost=0.00..1.08 rows=8 width=16)
-> Hash (cost=1.22..1.22 rows=22 width=16)
-> Seq Scan on stock_exchanges
pc_stock_exchanges (cost=0.00..1.22 rows=22 width=16)
-> Hash (cost=2.25..2.25 rows=125 width=20)
-> Seq Scan on co_subcategories pc_co_sub
(cost=0.00..2.25 rows=125 width=20)
-> Hash (cost=1.21..1.21 rows=21 width=16)
-> Seq Scan on co_categories pc_co_cat
(cost=0.00..1.21 rows=21 width=16)
-> Hash (cost=1.70..1.70 rows=70 width=16)
-> Seq Scan on verticals pc_verticals (cost=0.00..1.70
rows=70 width=16)
-> Hash (cost=1.04..1.04 rows=4 width=16)
-> Seq Scan on audiences pc_audiences (cost=0.00..1.04 rows=4
width=16)
-> Hash (cost=1.07..1.07 rows=7 width=16)
-> Seq Scan on revenue_models pc_rev (cost=0.00..1.07 rows=7 width=16)
->
Hash (cost=1.68..1.68 rows=68 width=16)
-> Seq Scan on states sc_states (cost=0.00..1.68 rows=68 width=16)
-> Hash
(cost=1.44..1.44 rows=44 width=16) ->
Seq Scan on countries sc_countries (cost=0.00..1.44 rows=44 width=16)
-> Hash
(cost=1.26..1.26 rows=26 width=8) -> Seq
Scan on state_regions sc_srt (cost=0.00..1.26 rows=26 width=8) ->
Hash
(cost=1.08..1.08 rows=8 width=16) -> Seq Scan on
regions sc_sr (cost=0.00..1.08 rows=8 width=16) -> Hash (cost=1.20..1.20
rows=20 width=8) -> Seq Scan on
country_regions sc_cot (cost=0.00..1.20 rows=20 width=8) -> Hash
(cost=1.08..1.08rows=8
width=16) -> Seq Scan on regions
sc_cr (cost=0.00..1.08 rows=8 width=16) -> Hash (cost=1.22..1.22 rows=22
width=16) -> Seq Scan on stock_exchanges
sc_stock_exchanges (cost=0.00..1.22 rows=22 width=16) -> Hash (cost=2.25..2.25 rows=125
width=20) -> Seq Scan on co_subcategories
sc_co_sub (cost=0.00..2.25 rows=125 width=20) -> Hash (cost=1.21..1.21 rows=21 width=16)
-> Seq Scan on co_categories sc_co_cat
(cost=0.00..1.21 rows=21 width=16) -> Hash (cost=1.70..1.70 rows=70 width=16) -> Seq
Scanon verticals sc_verticals
(cost=0.00..1.70 rows=70 width=16) -> Hash (cost=1.04..1.04 rows=4 width=16) -> Seq Scan on
audiencessc_audiences (cost=0.00..1.04
rows=4 width=16) -> Hash (cost=1.07..1.07 rows=7 width=16) -> Seq Scan on revenue_models sc_rev
(cost=0.00..1.07rows=7
width=16)
EXPLAIN
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco