Thread: View performance question
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
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 > >
"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
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 > >
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
"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
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.08rows=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
Scanon 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
audiencessc_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.07rows=7
width=16)
EXPLAIN
______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
"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