View performance question - Mailing list 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 mergersLEFT OUTER JOIN merger_types ON mergers.merger_type = merger_types.idLEFT OUTER JOIN merger_intents ON
mergers.merger_intent=
 
merger_intents.idLEFT OUTER JOIN revenue_types revenue_types1 ON mergers.revenue1_type =
revenue_types1.idLEFT OUTER JOIN revenue_types revenue_types2 ON mergers.revenue2_type =
revenue_types2.idLEFT OUTER JOIN revenue_types cash_types ON mergers.cash_flow_type =
cash_types.idLEFT OUTER JOIN earnings_types earnings_types1 ON mergers.earnings_type
= earnings_types1.idLEFT OUTER JOIN earnings_types earnings_types2 ON mergers.earnings_type
= earnings_types2.idLEFT OUTER JOIN amount_types ON mergers.amount_type = amount_types.idLEFT OUTER JOIN
accounting_methodsON mergers.accounting =
 
accounting_methods.idLEFT OUTER JOIN entry_statuses ON mergers.entry_status =
entry_statuses.idLEFT OUTER JOIN muv_sources ON mergers.muv_source = muv_sources.idLEFT OUTER JOIN classifications ON
mergers.classification=
 
classifications.id

JOIN companies pc ON mergers.primary_co = pc.idLEFT OUTER JOIN states pc_states ON pc.state = pc_states.idLEFT OUTER
JOINcountries pc_countries ON pc.country = pc_countries.idLEFT OUTER JOIN state_regions pc_srt ON pc.state =
pc_srt.state   LEFT OUTER JOIN regions pc_sr ON pc_srt.region = pc_sr.idLEFT OUTER JOIN country_regions pc_cot ON
pc.country= pc_cot.country    LEFT OUTER JOIN regions pc_cr ON pc_cot.region = pc_cr.idLEFT OUTER JOIN stock_exchanges
pc_stock_exchangesON pc.stock_ex =
 
pc_stock_exchanges.idLEFT 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.idLEFT OUTER JOIN verticals pc_verticals ON pc.vertical = pc_verticals.idLEFT OUTER JOIN audiences
pc_audiencesON pc.audience = pc_audiences.idLEFT OUTER JOIN revenue_models pc_rev ON pc.revenue_model = pc_rev.id
 

JOIN companies sc ON mergers.secondary_co = sc.idLEFT OUTER JOIN states sc_states ON sc.state = sc_states.idLEFT OUTER
JOINcountries sc_countries ON sc.country = sc_countries.idLEFT OUTER JOIN state_regions sc_srt ON sc.state =
sc_srt.state   LEFT OUTER JOIN regions sc_sr ON sc_srt.region = sc_sr.idLEFT OUTER JOIN country_regions sc_cot ON
sc.country= sc_cot.country    LEFT OUTER JOIN regions sc_cr ON sc_cot.region = sc_cr.idLEFT OUTER JOIN stock_exchanges
sc_stock_exchangesON sc.stock_ex =
 
sc_stock_exchanges.idLEFT 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.idLEFT OUTER JOIN verticals sc_verticals ON sc.vertical = sc_verticals.idLEFT OUTER JOIN audiences
sc_audiencesON sc.audience = sc_audiences.idLEFT OUTER JOIN revenue_models sc_rev ON sc.revenue_model = sc_rev.id
 
;



______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