pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: View performance question
Date: Fri, 22 Jun 2001 08:07:40 -0700
Message-ID: <web-75902@davinci.ethosmedia.com> (raw)

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



view thread (8+ messages)  latest in thread

Message-ID: <web-75902@davinci.ethosmedia.com>
Permalink:  ../web-75902@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-75902@davinci.ethosmedia.com

 · 

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
  Subject: Re: View performance question
  In-Reply-To: <web-75902@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