pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed From: Josh Berkus <josh@agliodbs.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-sql@postgresql.org
Subject: Re: View performance question
Date: Fri, 22 Jun 2001 11:37:44 -0700
Message-ID: <web-76095@davinci.ethosmedia.com> (raw )
In-Reply-To: <4840.993232680@sss.pgh.pa.us >
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.08 rows=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 Scan on 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 audiences sc_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.07 rows=7
width=16)
EXPLAIN
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
view thread (8+ messages) latest in thread
Message-ID: <web-76095@davinci.ethosmedia.com>
Permalink: ../web-76095@davinci.ethosmedia.com/
Also on: postgresql.org/message-id/web-76095@davinci.ethosmedia.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-sql@postgresql.org
Cc: josh@agliodbs.com, tgl@sss.pgh.pa.us
Subject: Re: View performance question
In-Reply-To: <web-76095@davinci.ethosmedia.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