Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5MF7ma28234 for ; Fri, 22 Jun 2001 11:07:48 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 75902 for ; Fri, 22 Jun 2001 08:07:40 -0700 From: "Josh Berkus" Subject: View performance question To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Fri, 22 Jun 2001 08:07:40 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200106/370 X-Sequence-Number: 3660 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