pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedView performance question
8+ messages / 3 participants
[nested] [flat]
* View performance question
@ 2001-06-22 15:07 Josh Berkus <josh@agliodbs.com>
2001-06-22 15:34 ` Re: View performance question Alex Pilosov <alex@pilosoft.com>
2001-06-22 17:05 ` Re: View performance question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Josh Berkus @ 2001-06-22 15:07 UTC (permalink / raw)
To: pgsql-sql
Folks,
I created a view to support comprehensive reporting on one of the
databases I work with. Unfortunately, due to the structure of the
database, which includes 25 reference tables, this requires 3 regular
joins and about 40 LEFT OUTER JOINS, outputting about 100 columns.
However, the two main data tables only have about 6000 records each.
The view is quite slow, taking almost a full minute to return any
records, on an Athalon 800 with 512mb RAM. Since the client's computer
is significantly less powerful, I'm concerned about performance.
All joined fields are indexed. Is there anything else I can do to speed
this up? (view text below)
-Josh Berkus
DROP VIEW vw_valuation_report;
CREATE VIEW vw_valuation_report AS
SELECT mergers.id as id, merger_types.name AS merger_type,
announced_on,merged_on, sec_employees, merger_intents.name as
merger_intent,
percent_own, revenue1, revenue_types1.name AS revenue1_type,
revenue2, revenue_types2.name AS revenue2_type,
revenue1_note, revenue2_note, earnings, earnings_types1.name AS
earnings_type1,
earnings2, earnings_types2.name AS earnings_type2,cash_amount,
stock_amount, other_amount, liability_assumption,total_amount,
amount_types.name AS amount_type,deal_summary, max_earnout,
accounting_methods.name AS accounting_method,entry_statuses.name AS
entry_status,
paid_subscribers,unpaid_subscribers, page_views, page_view_ranking,
visits,visitors, muv_sources.name AS muv_source,
cash_flow, cash_types.name as cash_flow_type, corp_customers,
ind_customers,classifications.name as classification, entered_on,
pc.id as pc_id, pc.name as pc_name, pc.city as pc_city,
pc_states.name as pc_state, pc.zip as pc_zip, pc_countries.name as
pc_country,
pc_sr.name as pc_stregion, pc_cr.name as pc_coregion,pc.url as pc_url,
pc_stock_exchanges.name as pc_exchange,pc.stock_symbol as pc_symbol,
pc_co_cat.name as pc_category,pc_co_sub.name as pc_subcategory,
pc_verticals.name as pc_vertical,
pc_audiences.name as pc_audience, pc_rev.name as pc_revenue,pc.sic_code
AS pc_sic,
pc.ind_code as pc_ind,pc.summary as pc_summary,pc.abstract AS
pc_abstract,
sc.id as sc_id, sc.name as sc_name, sc.city as sc_city,
sc_states.name as sc_state, sc.zip as sc_zip, sc_countries.name as
sc_country,
sc_sr.name as sc_stregion, sc_cr.name as sc_coregion,
sc.url as sc_url, sc_stock_exchanges.name as sc_exchange,
sc.stock_symbol as sc_symbol, sc_co_cat.name as sc_category,
sc_co_sub.name as sc_subcategory, sc_verticals.name as sc_vertical,
sc_audiences.name as sc_audience, sc_rev.name as sc_revenue,
sc.sic_code AS sc_sic, sc.ind_code as sc_ind,
sc.summary as sc_summary, sc.abstract AS sc_abstract
FROM mergers
LEFT OUTER JOIN merger_types ON mergers.merger_type = merger_types.id
LEFT OUTER JOIN merger_intents ON mergers.merger_intent =
merger_intents.id
LEFT OUTER JOIN revenue_types revenue_types1 ON mergers.revenue1_type =
revenue_types1.id
LEFT OUTER JOIN revenue_types revenue_types2 ON mergers.revenue2_type =
revenue_types2.id
LEFT OUTER JOIN revenue_types cash_types ON mergers.cash_flow_type =
cash_types.id
LEFT OUTER JOIN earnings_types earnings_types1 ON mergers.earnings_type
= earnings_types1.id
LEFT OUTER JOIN earnings_types earnings_types2 ON mergers.earnings_type
= earnings_types2.id
LEFT OUTER JOIN amount_types ON mergers.amount_type = amount_types.id
LEFT OUTER JOIN accounting_methods ON mergers.accounting =
accounting_methods.id
LEFT OUTER JOIN entry_statuses ON mergers.entry_status =
entry_statuses.id
LEFT OUTER JOIN muv_sources ON mergers.muv_source = muv_sources.id
LEFT OUTER JOIN classifications ON mergers.classification =
classifications.id
JOIN companies pc ON mergers.primary_co = pc.id
LEFT OUTER JOIN states pc_states ON pc.state = pc_states.id
LEFT OUTER JOIN countries pc_countries ON pc.country = pc_countries.id
LEFT OUTER JOIN state_regions pc_srt ON pc.state = pc_srt.state
LEFT OUTER JOIN regions pc_sr ON pc_srt.region = pc_sr.id
LEFT OUTER JOIN country_regions pc_cot ON pc.country = pc_cot.country
LEFT OUTER JOIN regions pc_cr ON pc_cot.region = pc_cr.id
LEFT OUTER JOIN stock_exchanges pc_stock_exchanges ON pc.stock_ex =
pc_stock_exchanges.id
LEFT OUTER JOIN co_subcategories pc_co_sub ON pc.subcategory =
pc_co_sub.id
LEFT OUTER JOIN co_categories pc_co_cat ON pc_co_sub.category =
pc_co_cat.id
LEFT OUTER JOIN verticals pc_verticals ON pc.vertical = pc_verticals.id
LEFT OUTER JOIN audiences pc_audiences ON pc.audience = pc_audiences.id
LEFT OUTER JOIN revenue_models pc_rev ON pc.revenue_model = pc_rev.id
JOIN companies sc ON mergers.secondary_co = sc.id
LEFT OUTER JOIN states sc_states ON sc.state = sc_states.id
LEFT OUTER JOIN countries sc_countries ON sc.country = sc_countries.id
LEFT OUTER JOIN state_regions sc_srt ON sc.state = sc_srt.state
LEFT OUTER JOIN regions sc_sr ON sc_srt.region = sc_sr.id
LEFT OUTER JOIN country_regions sc_cot ON sc.country = sc_cot.country
LEFT OUTER JOIN regions sc_cr ON sc_cot.region = sc_cr.id
LEFT OUTER JOIN stock_exchanges sc_stock_exchanges ON sc.stock_ex =
sc_stock_exchanges.id
LEFT OUTER JOIN co_subcategories sc_co_sub ON sc.subcategory =
sc_co_sub.id
LEFT OUTER JOIN co_categories sc_co_cat ON sc_co_sub.category =
sc_co_cat.id
LEFT OUTER JOIN verticals sc_verticals ON sc.vertical = sc_verticals.id
LEFT OUTER JOIN audiences sc_audiences ON sc.audience = sc_audiences.id
LEFT OUTER JOIN revenue_models sc_rev ON sc.revenue_model = sc_rev.id
;
______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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: View performance question
2001-06-22 15:07 View performance question Josh Berkus <josh@agliodbs.com>
@ 2001-06-22 15:34 ` Alex Pilosov <alex@pilosoft.com>
2001-06-22 16:48 ` Re: View performance question Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 8+ messages in thread
From: Alex Pilosov @ 2001-06-22 15:34 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
please do 'explain select * from yourview'
also make sure all tables are vacuumed
On Fri, 22 Jun 2001, Josh Berkus wrote:
> Folks,
>
> I created a view to support comprehensive reporting on one of the
> databases I work with. Unfortunately, due to the structure of the
> database, which includes 25 reference tables, this requires 3 regular
> joins and about 40 LEFT OUTER JOINS, outputting about 100 columns.
>
> However, the two main data tables only have about 6000 records each.
> The view is quite slow, taking almost a full minute to return any
> records, on an Athalon 800 with 512mb RAM. Since the client's computer
> is significantly less powerful, I'm concerned about performance.
>
> All joined fields are indexed. Is there anything else I can do to speed
> this up? (view text below)
>
> -Josh Berkus
>
>
> DROP VIEW vw_valuation_report;
>
> CREATE VIEW vw_valuation_report AS
> SELECT mergers.id as id, merger_types.name AS merger_type,
> announced_on,merged_on, sec_employees, merger_intents.name as
> merger_intent,
> percent_own, revenue1, revenue_types1.name AS revenue1_type,
> revenue2, revenue_types2.name AS revenue2_type,
> revenue1_note, revenue2_note, earnings, earnings_types1.name AS
> earnings_type1,
> earnings2, earnings_types2.name AS earnings_type2,cash_amount,
> stock_amount, other_amount, liability_assumption,total_amount,
> amount_types.name AS amount_type,deal_summary, max_earnout,
> accounting_methods.name AS accounting_method,entry_statuses.name AS
> entry_status,
> paid_subscribers,unpaid_subscribers, page_views, page_view_ranking,
> visits,visitors, muv_sources.name AS muv_source,
> cash_flow, cash_types.name as cash_flow_type, corp_customers,
> ind_customers,classifications.name as classification, entered_on,
> pc.id as pc_id, pc.name as pc_name, pc.city as pc_city,
> pc_states.name as pc_state, pc.zip as pc_zip, pc_countries.name as
> pc_country,
> pc_sr.name as pc_stregion, pc_cr.name as pc_coregion,pc.url as pc_url,
> pc_stock_exchanges.name as pc_exchange,pc.stock_symbol as pc_symbol,
> pc_co_cat.name as pc_category,pc_co_sub.name as pc_subcategory,
> pc_verticals.name as pc_vertical,
> pc_audiences.name as pc_audience, pc_rev.name as pc_revenue,pc.sic_code
> AS pc_sic,
> pc.ind_code as pc_ind,pc.summary as pc_summary,pc.abstract AS
> pc_abstract,
> sc.id as sc_id, sc.name as sc_name, sc.city as sc_city,
> sc_states.name as sc_state, sc.zip as sc_zip, sc_countries.name as
> sc_country,
> sc_sr.name as sc_stregion, sc_cr.name as sc_coregion,
> sc.url as sc_url, sc_stock_exchanges.name as sc_exchange,
> sc.stock_symbol as sc_symbol, sc_co_cat.name as sc_category,
> sc_co_sub.name as sc_subcategory, sc_verticals.name as sc_vertical,
> sc_audiences.name as sc_audience, sc_rev.name as sc_revenue,
> sc.sic_code AS sc_sic, sc.ind_code as sc_ind,
> sc.summary as sc_summary, sc.abstract AS sc_abstract
> FROM mergers
> LEFT OUTER JOIN merger_types ON mergers.merger_type = merger_types.id
> LEFT OUTER JOIN merger_intents ON mergers.merger_intent =
> merger_intents.id
> LEFT OUTER JOIN revenue_types revenue_types1 ON mergers.revenue1_type =
> revenue_types1.id
> LEFT OUTER JOIN revenue_types revenue_types2 ON mergers.revenue2_type =
> revenue_types2.id
> LEFT OUTER JOIN revenue_types cash_types ON mergers.cash_flow_type =
> cash_types.id
> LEFT OUTER JOIN earnings_types earnings_types1 ON mergers.earnings_type
> = earnings_types1.id
> LEFT OUTER JOIN earnings_types earnings_types2 ON mergers.earnings_type
> = earnings_types2.id
> LEFT OUTER JOIN amount_types ON mergers.amount_type = amount_types.id
> LEFT OUTER JOIN accounting_methods ON mergers.accounting =
> accounting_methods.id
> LEFT OUTER JOIN entry_statuses ON mergers.entry_status =
> entry_statuses.id
> LEFT OUTER JOIN muv_sources ON mergers.muv_source = muv_sources.id
> LEFT OUTER JOIN classifications ON mergers.classification =
> classifications.id
>
> JOIN companies pc ON mergers.primary_co = pc.id
> LEFT OUTER JOIN states pc_states ON pc.state = pc_states.id
> LEFT OUTER JOIN countries pc_countries ON pc.country = pc_countries.id
> LEFT OUTER JOIN state_regions pc_srt ON pc.state = pc_srt.state
> LEFT OUTER JOIN regions pc_sr ON pc_srt.region = pc_sr.id
> LEFT OUTER JOIN country_regions pc_cot ON pc.country = pc_cot.country
> LEFT OUTER JOIN regions pc_cr ON pc_cot.region = pc_cr.id
> LEFT OUTER JOIN stock_exchanges pc_stock_exchanges ON pc.stock_ex =
> pc_stock_exchanges.id
> LEFT OUTER JOIN co_subcategories pc_co_sub ON pc.subcategory =
> pc_co_sub.id
> LEFT OUTER JOIN co_categories pc_co_cat ON pc_co_sub.category =
> pc_co_cat.id
> LEFT OUTER JOIN verticals pc_verticals ON pc.vertical = pc_verticals.id
> LEFT OUTER JOIN audiences pc_audiences ON pc.audience = pc_audiences.id
> LEFT OUTER JOIN revenue_models pc_rev ON pc.revenue_model = pc_rev.id
>
> JOIN companies sc ON mergers.secondary_co = sc.id
> LEFT OUTER JOIN states sc_states ON sc.state = sc_states.id
> LEFT OUTER JOIN countries sc_countries ON sc.country = sc_countries.id
> LEFT OUTER JOIN state_regions sc_srt ON sc.state = sc_srt.state
> LEFT OUTER JOIN regions sc_sr ON sc_srt.region = sc_sr.id
> LEFT OUTER JOIN country_regions sc_cot ON sc.country = sc_cot.country
> LEFT OUTER JOIN regions sc_cr ON sc_cot.region = sc_cr.id
> LEFT OUTER JOIN stock_exchanges sc_stock_exchanges ON sc.stock_ex =
> sc_stock_exchanges.id
> LEFT OUTER JOIN co_subcategories sc_co_sub ON sc.subcategory =
> sc_co_sub.id
> LEFT OUTER JOIN co_categories sc_co_cat ON sc_co_sub.category =
> sc_co_cat.id
> LEFT OUTER JOIN verticals sc_verticals ON sc.vertical = sc_verticals.id
> LEFT OUTER JOIN audiences sc_audiences ON sc.audience = sc_audiences.id
> LEFT OUTER JOIN revenue_models sc_rev ON sc.revenue_model = sc_rev.id
> ;
>
>
>
> ______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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: View performance question
2001-06-22 15:07 View performance question Josh Berkus <josh@agliodbs.com>
2001-06-22 15:34 ` Re: View performance question Alex Pilosov <alex@pilosoft.com>
@ 2001-06-22 16:48 ` Josh Berkus <josh@agliodbs.com>
2001-06-22 17:00 ` Re: View performance question Alex Pilosov <alex@pilosoft.com>
2001-06-22 17:58 ` Re: View performance question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Josh Berkus @ 2001-06-22 16:48 UTC (permalink / raw)
To: Alex Pilosov <alex@pilosoft.com>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
Alex,
> please do 'explain select * from yourview'
I can't figure out how to get Explain to output to a file instead of the
screen. Embarassing, I know, but the command seems to subbornly resist
all command-line re-direction.
>
> also make sure all tables are vacuumed
They are.
-Josh
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: View performance question
2001-06-22 15:07 View performance question Josh Berkus <josh@agliodbs.com>
2001-06-22 15:34 ` Re: View performance question Alex Pilosov <alex@pilosoft.com>
2001-06-22 16:48 ` Re: View performance question Josh Berkus <josh@agliodbs.com>
@ 2001-06-22 17:00 ` Alex Pilosov <alex@pilosoft.com>
1 sibling, 0 replies; 8+ messages in thread
From: Alex Pilosov @ 2001-06-22 17:00 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
Cut and paste is your friend :)
If you are using psql, do \o foo and then explain....
-alex
On Fri, 22 Jun 2001, Josh Berkus wrote:
> Alex,
>
> > please do 'explain select * from yourview'
>
> I can't figure out how to get Explain to output to a file instead of the
> screen. Embarassing, I know, but the command seems to subbornly resist
> all command-line re-direction.
>
> >
> > also make sure all tables are vacuumed
>
> They are.
>
> -Josh
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: View performance question
2001-06-22 15:07 View performance question Josh Berkus <josh@agliodbs.com>
2001-06-22 15:34 ` Re: View performance question Alex Pilosov <alex@pilosoft.com>
2001-06-22 16:48 ` Re: View performance question Josh Berkus <josh@agliodbs.com>
@ 2001-06-22 17:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-22 18:37 ` Re: View performance question Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2001-06-22 17:58 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Alex Pilosov <alex@pilosoft.com>; pgsql-sql
"Josh Berkus" <josh@agliodbs.com> writes:
> I can't figure out how to get Explain to output to a file instead of the
> screen. Embarassing, I know, but the command seems to subbornly resist
> all command-line re-direction.
I think that in psql, EXPLAIN results (and notices in general) come out
via stderr not stdout.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: View performance question
2001-06-22 15:07 View performance question Josh Berkus <josh@agliodbs.com>
2001-06-22 15:34 ` Re: View performance question Alex Pilosov <alex@pilosoft.com>
2001-06-22 16:48 ` Re: View performance question Josh Berkus <josh@agliodbs.com>
2001-06-22 17:58 ` Re: View performance question Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-22 18:37 ` Josh Berkus <josh@agliodbs.com>
2001-06-22 21:11 ` Re: View performance question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2001-06-22 18:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; 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.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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: View performance question
2001-06-22 15:07 View performance question Josh Berkus <josh@agliodbs.com>
2001-06-22 15:34 ` Re: View performance question Alex Pilosov <alex@pilosoft.com>
2001-06-22 16:48 ` Re: View performance question Josh Berkus <josh@agliodbs.com>
2001-06-22 17:58 ` Re: View performance question Tom Lane <tgl@sss.pgh.pa.us>
2001-06-22 18:37 ` Re: View performance question Josh Berkus <josh@agliodbs.com>
@ 2001-06-22 21:11 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2001-06-22 21:11 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
"Josh Berkus" <josh@agliodbs.com> writes:
> 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.
Hm. The query plan looks very reasonable: hashing the smaller tables is
exactly what I'd think it should do. If the rows estimates shown in the
plan are accurate, it's hard to see how it's spending 42 seconds on
this.
The primary tables seem to be big enough that they are probably being
divided into hash batches, if you use the default sort_mem setting of
512K. Try increasing sort_mem (SET SORT_MEM TO 5000 or so) and see if
that makes a difference.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: View performance question
2001-06-22 15:07 View performance question Josh Berkus <josh@agliodbs.com>
@ 2001-06-22 17:05 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2001-06-22 17:05 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
"Josh Berkus" <josh@agliodbs.com> writes:
> I created a view to support comprehensive reporting on one of the
> databases I work with. Unfortunately, due to the structure of the
> database, which includes 25 reference tables, this requires 3 regular
> joins and about 40 LEFT OUTER JOINS, outputting about 100 columns.
I suppose this is a star schema, wherein rows of the main tables join
to at most one row of the "reference" tables? If so, you probably want
to make sure you perform the join of the main tables before you start
outerjoining the reference tables onto them. The syntax you are using
is constraining the planner to use what's probably not a good plan.
See
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2001-06-22 21:11 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-22 15:07 View performance question Josh Berkus <josh@agliodbs.com>
2001-06-22 15:34 ` Alex Pilosov <alex@pilosoft.com>
2001-06-22 16:48 ` Josh Berkus <josh@agliodbs.com>
2001-06-22 17:00 ` Alex Pilosov <alex@pilosoft.com>
2001-06-22 17:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-22 18:37 ` Josh Berkus <josh@agliodbs.com>
2001-06-22 21:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-22 17:05 ` Tom Lane <tgl@sss.pgh.pa.us>
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