Re: View performance question

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
> 
>