RE: CTE materialized/not materialized - Mailing list pgsql-sql
| From | Voillequin, Jean-Marc |
|---|---|
| Subject | RE: CTE materialized/not materialized |
| Date | |
| Msg-id | MW3PR20MB33388940243849D8EA2881C3BEF80@MW3PR20MB3338.namprd20.prod.outlook.com Whole thread Raw |
| In response to | Re: CTE materialized/not materialized (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: CTE materialized/not materialized
|
| List | pgsql-sql |
Thank you Tom & Alvaro.
I'm still dreaming of such query planner!
Just for information, this is an example of plan I have to optimize, and it's a nightmare.
Sort (cost=1513506.06..1513506.08 rows=8 width=20862)
Sort Key: o2p_tmp_9059.first_rank_id, o2p_tmp_9059.second_rank_id
-> Subquery Scan on o2p_tmp_9059 (cost=1513505.68..1513505.94 rows=8 width=20862)
-> WindowAgg (cost=1513505.68..1513505.86 rows=8 width=20866)
CTE deposit_coverage
-> Nested Loop Left Join (cost=187.14..453746.58 rows=1277 width=434)
Join Filter: ("*SELECT* 1".lsr_id = dr.lsr_id)
-> Hash Left Join (cost=187.14..451353.48 rows=1277 width=264)
Hash Cond: ((("*SELECT* 1".dis_code)::text = (dis.dis_code)::text) AND ((COALESCE("*SELECT*
1".insured_category,'0'::character varying))::text = (COALESCE(dis.insured_category, '0'::character varying))::text))
-> Hash Left Join (cost=186.09..451342.84 rows=1277 width=252)
Hash Cond: ((("*SELECT* 1".booking_company)::text = (bcm.booking_company)::text) AND
(("*SELECT*1".dis_code)::text = (bcm.dis_code)::text))
-> Append (cost=169.09..451229.98 rows=1277 width=210)
-> Subquery Scan on "*SELECT* 1" (cost=169.09..451073.64 rows=1 width=211)
-> Hash Left Join (cost=169.09..451073.63 rows=1 width=19929)
Hash Cond: (((lsr.contract_reference)::text =
(lsr_slice_1_1.contract_reference)::text)AND ((lsr.table_name)::text = (lsr_slice_1_1.table_name)::text) AND
(lsr.scenario_id= lsr_slice_1_1.scenario_id))
Filter: (lsr_slice_1_1.lsr_id IS NULL)
-> Seq Scan on lsr (cost=0.00..425638.55 rows=1684112 width=214)
Filter: ((dis_code IS NOT NULL) AND ((table_name)::text = ANY
('{LOANDEPO,ACCOUNT}'::text[])))
-> Hash (cost=146.76..146.76 rows=1276 width=87)
-> Seq Scan on lsr_slice_1 lsr_slice_1_1 (cost=0.00..146.76
rows=1276width=87)
-> Seq Scan on lsr_slice_1 (cost=0.00..149.95 rows=1276 width=210)
Filter: ((dis_code IS NOT NULL) AND ((table_name)::text = ANY
('{LOANDEPO,ACCOUNT}'::text[])))
-> Hash (cost=12.80..12.80 rows=280 width=162)
-> Seq Scan on booking_company_member bcm (cost=0.00..12.80 rows=280
width=162)
-> Hash (cost=1.02..1.02 rows=2 width=25)
-> Seq Scan on deposit_insur_scheme dis (cost=0.00..1.02 rows=2 width=25)
-> Materialize (cost=0.00..11.80 rows=120 width=40)
-> Seq Scan on dis_rules dr (cost=0.00..11.20 rows=120 width=40)
CTE lsr_m
-> Append (cost=169.09..469613.25 rows=1272 width=20382)
-> Nested Loop Anti Join (cost=169.09..469584.17 rows=1271 width=15789)
Join Filter: ("*SELECT* 1_1".lsr_id = fac.lsr_id)
-> Append (cost=169.09..469555.02 rows=1277 width=15786)
-> Subquery Scan on "*SELECT* 1_1" (cost=169.09..469381.14 rows=1 width=12431)
-> Hash Left Join (cost=169.09..469381.13 rows=1 width=12713)
Hash Cond: (((lsr_1.contract_reference)::text =
(lsr_slice_1_3.contract_reference)::text)AND ((lsr_1.table_name)::text = (lsr_slice_1_3.table_name)::text) AND
(lsr_1.scenario_id= lsr_slice_1_3.scenario_id))
Filter: (lsr_slice_1_3.lsr_id IS NULL)
-> Seq Scan on lsr lsr_1 (cost=0.00..416925.64 rows=3485164
width=11641)
-> Hash (cost=146.76..146.76 rows=1276 width=87)
-> Seq Scan on lsr_slice_1 lsr_slice_1_3 (cost=0.00..146.76
rows=1276width=87)
-> Seq Scan on lsr_slice_1 lsr_slice_1_2 (cost=0.00..167.50 rows=1276 width=15789)
-> Materialize (cost=0.00..10.01 rows=1 width=32)
-> Seq Scan on lr_secured_fac_sr fac (cost=0.00..10.00 rows=1 width=32)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..10.01 rows=1 width=20382)
-> Seq Scan on lr_secured_fac_sr source_1 (cost=0.00..10.00 rows=1 width=20358)
CTE lsr_source
-> Append (cost=539927.07..590114.78 rows=7 width=20822)
-> Subquery Scan on "*SELECT* 1_2" (cost=539927.07..539927.66 rows=1 width=20844)
-> WindowAgg (cost=539927.07..539927.65 rows=1 width=21214)
InitPlan 3 (returns $2)
-> Seq Scan on liq_supervisor_config_sr (cost=0.00..1.01 rows=1 width=2)
-> Sort (cost=539926.06..539926.07 rows=1 width=22688)
Sort Key: lsr_2.dis_code, lsr_2.insured_category, "*SELECT*
1_3".counterparty_group_dis,lsr_2.dis_coverage_mode DESC NULLS LAST, (CASE WHEN ((lsr_2.ccy_code)::text =
(dis_1.ccy_code)::text)THEN '0'::numeric ELSE dcg.priority_order END), lsr_2.asset_liability DESC,
lsr_2.dis_allocation_order,lsr_2.outstanding, lsr_2.contract_reference, lsr_2.table_name, lsr_2.standard_rw_new
-> Nested Loop Left Join (cost=489782.12..539926.05 rows=1 width=22688)
Join Filter: (las.id = lsr_2.lsr_id)
-> Hash Right Join (cost=489782.12..539915.38 rows=1 width=20165)
Hash Cond: ((lrp.entity_code)::text = (lsr_2.counterparty)::text)
-> Seq Scan on lr_entity_runoff_pref lrp (cost=0.00..43427.63
rows=1788163width=53)
-> Hash (cost=489782.11..489782.11 rows=1 width=20163)
-> Merge Left Join (cost=489782.08..489782.11 rows=1
width=20163)
Merge Cond: (((lsr_2.counterparty)::text =
(v_deposit_def.counterparty)::text)AND (((COALESCE(lsr_2.counterparty_group, '_'::character varying))::text) =
((COALESCE(v_deposit_def.counterparty_group,'_'::character varying))::text)))
-> Sort (cost=503.22..503.23 rows=1 width=20099)
Sort Key: lsr_2.counterparty,
((COALESCE(lsr_2.counterparty_group,'_'::character varying))::text)
-> Hash Right Join (cost=33.29..503.21 rows=1
width=20099)
Hash Cond: ((("*SELECT*
1_3".counterparty)::text= (lsr_2.counterparty)::text) AND (("*SELECT* 1_3".dis_code)::text = (lsr_2.dis_code)::text)
AND(("*SELECT* 1_3".booking_company)::text = (lsr_2.booking_company)::text) AND (("*SELECT*
1_3".insured_category)::text= (lsr_2.insured_category)::text))
Join Filter:
(((COALESCE(lsr_2.contract_reference,'_'::character varying))::text = (COALESCE("*SELECT* 1_3".contract_reference,
lsr_2.contract_reference,'_'::character varying))::text) AND ((COALESCE(lsr_2.table_name, '_'::character
varying))::text= (COALESCE("*SELECT* 1_3".table_name, lsr_2.table_name, '_'::character varying))::text) AND
(COALESCE(lsr_2.standard_rw_new,'-1'::numeric) = COALESCE("*SELECT* 1_3".standard_rw_new, lsr_2.standard_rw_new,
'-1'::numeric))AND ((COALESCE(lsr_2.is_operational, '_'::character varying))::text = (COALESCE("*SELECT*
1_3".is_operational,lsr_2.is_operational, '_'::character varying))::text))
-> Append (cost=0.00..464.75 rows=206
width=1024)
-> Subquery Scan on "*SELECT* 1_3"
(cost=0.00..28.82rows=6 width=1234)
-> CTE Scan on
deposit_coverage (cost=0.00..28.73 rows=6 width=1226)
Filter: ((coverage)::text
='A'::text)
-> Subquery Scan on "*SELECT* 2_2"
(cost=430.90..434.90rows=200 width=1024)
-> HashAggregate
(cost=430.90..432.90rows=200 width=1024)
Group Key:
o2p_tmp_9061.counterparty,o2p_tmp_9061.counterparty_group_dis, NULL::text, NULL::text, NULL::numeric,
o2p_tmp_9061.dis_code,o2p_tmp_9061.insured_category, o2p_tmp_9061.booking_company_member, o2p_tmp_9061.booking_company,
NULL::text,sum(o2p_tmp_9061.nb_asset) OVER (?), sum(o2p_tmp_9061.outstanding_asset) OVER (?),
sum(o2p_tmp_9061.nb_liability)OVER (?), sum(o2p_tmp_9061.outstanding_liability) OVER (?),
sum(o2p_tmp_9061.outstanding_operational)OVER (?), sum(o2p_tmp_9061.outstanding_full) OVER (?),
sum(o2p_tmp_9061.outstanding_liability_full)OVER (?), sum(o2p_tmp_9061.outstanding_operational_full) OVER (?),
o2p_tmp_9061.nb_unique_benef
-> WindowAgg
(cost=316.51..370.53rows=1271 width=1024)
-> Sort
(cost=316.51..319.69rows=1271 width=888)
Sort Key:
o2p_tmp_9061.counterparty_group_dis,o2p_tmp_9061.dis_code, o2p_tmp_9061.insured_category,
o2p_tmp_9061.booking_company_member
-> Subquery
Scanon o2p_tmp_9061 (cost=222.38..250.98 rows=1271 width=888)
->
HashAggregate (cost=222.38..238.27 rows=1271 width=1226)
GroupKey: d.counterparty, (COALESCE(g.entity_group_code, d.counterparty)), d.contract_reference, d.table_name,
d.standard_rw_new,d.dis_code, d.insured_category, d.booking_company_member, d.booking_company, d.is_operational,
d.nb_asset,d.outstanding_asset, d.nb_liability, d.outstanding_liability, d.outstanding_operational, d.outstanding_full,
d.outstanding_liability_full,d.outstanding_operational_full, CASE WHEN COALESCE(((d.coverage_limit_rule)::text =
'U'::text),false) THEN o2p_count_distinct(t_2.beneficiary_name) OVER (?) ELSE NULL::numeric END
-> WindowAgg (cost=127.06..162.01 rows=1271 width=1226)
-> Sort (cost=127.06..130.24 rows=1271 width=1320)
Sort Key: (COALESCE(g.entity_group_code, d.counterparty)), d.dis_code, d.insured_category,
d.booking_company_member
-> Hash Left Join (cost=18.45..61.53 rows=1271 width=1320)
Hash Cond: ((d.counterparty)::text = (g.entity_code)::text)
-> Hash Left Join (cost=12.25..50.55 rows=1271 width=1202)
Hash Cond: (((d.contract_reference)::text = (t_2.contract_reference)::text) AND
((d.table_name)::text= (t_2.table_name)::text))
Join Filter: ((d.coverage_limit_rule)::text = 'U'::text)
-> CTE Scan on deposit_coverage d (cost=0.00..28.73 rows=1271 width=1084)
Filter: ((coverage)::text <> 'A'::text)
-> Hash (cost=10.90..10.90 rows=90 width=416)
-> Seq Scan on trust_beneficiary t_2 (cost=0.00..10.90 rows=90 width=416)
-> Hash (cost=6.19..6.19 rows=1 width=102)
-> Index Only Scan using pk_group_sr on group_sr g (cost=0.42..6.19 rows=1 width=102)
Index Cond: (process_type = 'SOLE_PROPRIETORSHIP_LINK'::text)
-> Hash (cost=33.27..33.27 rows=1
width=19757)
-> Nested Loop Left Join
(cost=0.14..33.27rows=1 width=19757)
Join Filter:
(((lsr_2.dis_code)::text= (dis_1.dis_code)::text) AND ((lsr_2.insured_category)::text =
(dis_1.insured_category)::text))
-> Nested Loop Left Join
(cost=0.14..32.22rows=1 width=19744)
-> Nested Loop
(cost=0.00..30.81rows=1 width=19739)
Join Filter:
(scnr.ray_scenario_id= lsr_2.scenario_id)
-> Nested Loop
(cost=0.00..2.03rows=1 width=14)
-> Seq Scan
onconfig_param_sr cp (cost=0.00..1.01 rows=1 width=4)
-> Seq Scan
onray_sae_scenario_sr scnr (cost=0.00..1.01 rows=1 width=10)
-> CTE Scan on
lsr_mlsr_2 (cost=0.00..28.62 rows=13 width=19728)
Filter:
((table_name)::text= ANY ('{ACCOUNT,LOANDEPO}'::text[]))
-> Index Scan using
i1_dis_ccy_group_o2p_idxon dis_ccy_group dcg (cost=0.14..1.40 rows=1 width=22)
Index Cond:
(((dis_code)::text= (lsr_2.dis_code)::text) AND ((insured_category)::text = (lsr_2.insured_category)::text) AND
((ccy_code)::text= (lsr_2.ccy_code)::text))
-> Seq Scan on
deposit_insur_schemedis_1 (cost=0.00..1.02 rows=2 width=26)
-> Sort (cost=489278.86..489278.86 rows=2 width=166)
Sort Key: v_deposit_def.counterparty,
((COALESCE(v_deposit_def.counterparty_group,'_'::character varying))::text)
-> Subquery Scan on v_deposit_def
(cost=489278.79..489278.85rows=2 width=166)
-> Unique (cost=489278.79..489278.83
rows=2width=214)
InitPlan 4 (returns $3)
-> Seq Scan on
liq_supervisor_config_srliq_supervisor_config_sr_1 (cost=0.00..1.01 rows=1 width=2)
InitPlan 5 (returns $4)
-> Seq Scan on
liq_supervisor_config_srliq_supervisor_config_sr_2 (cost=0.00..1.01 rows=1 width=2)
InitPlan 6 (returns $5)
-> Seq Scan on
liq_supervisor_config_srliq_supervisor_config_sr_3 (cost=0.00..1.01 rows=1 width=2)
-> Sort (cost=489275.76..489275.77
rows=2width=214)
Sort Key: "*SELECT*
1_4".counterparty,"*SELECT* 1_4".counterparty_group, (count(1) OVER (?)), (abs(sum(CASE WHEN (($3)::text = '1'::text)
THEN("*SELECT* 1_4".outstanding + "*SELECT* 1_4".accrued_interests) ELSE "*SELECT* 1_4".outstanding END) OVER (?))),
(CASEWHEN ("*SELECT* 1_4".counterparty_group IS NOT NULL) THEN (count(1) OVER (?)) ELSE NULL::bigint END), (CASE WHEN
("*SELECT*1_4".counterparty_group IS NOT NULL) THEN abs((sum(CASE WHEN (($4)::text = '1'::text) THEN ("*SELECT*
1_4".outstanding+ "*SELECT* 1_4".accrued_interests) ELSE "*SELECT* 1_4".outstanding END) OVER (?))) ELSE NULL::numeric
END)
-> WindowAgg
(cost=489275.69..489275.75rows=2 width=214)
-> Sort
(cost=489275.69..489275.69rows=2 width=153)
Sort Key: "*SELECT*
1_4".counterparty
-> WindowAgg
(cost=489275.63..489275.68rows=2 width=153)
-> Sort
(cost=489275.63..489275.63rows=2 width=113)
Sort
Key:"*SELECT* 1_4".counterparty_group
->
HashJoin (cost=434673.74..489275.62 rows=2 width=113)
HashCond: ((lrp_1.entity_code)::text = ("*SELECT* 1_4".counterparty)::text)
-> Seq Scan on lr_entity_runoff_pref lrp_1 (cost=0.00..47898.04 rows=1787686 width=51)
Filter: ((run_off_eligible)::text = 'T'::text)
-> Hash (cost=434673.71..434673.71 rows=2 width=113)
-> Append (cost=0.00..434673.71 rows=2 width=113)
-> Subquery Scan on "*SELECT* 1_4" (cost=0.00..434520.56 rows=1 width=111)
-> Nested Loop Left Join (cost=0.00..434520.55 rows=1 width=20475)
Join Filter: (((lsr_3.contract_reference)::text = (lsr_slice_1_5.contract_reference)::text) AND
((lsr_3.table_name)::text= (lsr_slice_1_5.table_name)::text) AND (lsr_3.scenario_id = lsr_slice_1_5.scenario_id))
Filter: (lsr_slice_1_5.lsr_id IS NULL)
-> Seq Scan on lsr lsr_3 (cost=0.00..434351.46 rows=1 width=187)
Filter: (((bis_entity_category)::text = ANY ('{RETAIL,SME}'::text[])) AND
((is_deposit)::text= 'T'::text))
-> Seq Scan on lsr_slice_1 lsr_slice_1_5 (cost=0.00..146.76 rows=1276 width=87)
-> Seq Scan on lsr_slice_1 lsr_slice_1_4 (cost=0.00..153.14 rows=1 width=115)
Filter: (((bis_entity_category)::text = ANY ('{RETAIL,SME}'::text[])) AND ((is_deposit)::text =
'T'::text))
-> Seq Scan on liqratio_ae_sr las (cost=0.00..10.30 rows=30 width=2528)
-> Subquery Scan on "*SELECT* 2_1" (cost=50186.21..50187.08 rows=6 width=20818)
-> WindowAgg (cost=50186.21..50187.02 rows=6 width=21038)
InitPlan 7 (returns $6)
-> Seq Scan on liq_supervisor_config_sr liq_supervisor_config_sr_4
(cost=0.00..1.01rows=1 width=2)
-> Sort (cost=50185.20..50185.22 rows=6 width=21928)
Sort Key: (COALESCE(las_1.table_name, lsr_4.table_name)), lsr_4.ccy_code,
lsr_4.contract_type,lsr_4.book_code, (COALESCE(las_1.contract_reference, lsr_4.contract_reference)), lsr_4.attribute_1,
lsr_4.attribute_2,lsr_4.attribute_3, lsr_4.attribute_4, lsr_4.attribute_5, lsr_4.attribute_6, lsr_4.attribute_7,
lsr_4.attribute_8,lsr_4.attribute_9, lsr_4.attribute_10, lsr_4.attribute_11, lsr_4.attribute_12, lsr_4.attribute_13,
lsr_4.attribute_14,lsr_4.attribute_15, lsr_4.attribute_16, lsr_4.attribute_17, lsr_4.attribute_18, lsr_4.attribute_19,
lsr_4.attribute_20,lsr_4.family, lsr_4.deal_status, lsr_4.sub_reference
-> Nested Loop (cost=37.68..50185.13 rows=6 width=21928)
-> Seq Scan on config_param_sr cp_1 (cost=0.00..1.01 rows=1 width=0)
-> Nested Loop Left Join (cost=37.68..50184.06 rows=6 width=22226)
Join Filter: (las_1.id = lsr_4.lsr_id)
-> Hash Right Join (cost=37.68..50170.98 rows=6 width=19730)
Hash Cond: ((lrp_2.entity_code)::text =
(lsr_4.counterparty)::text)
-> Seq Scan on lr_entity_runoff_pref lrp_2
(cost=0.00..43427.63rows=1788163 width=53)
-> Hash (cost=37.60..37.60 rows=6 width=19728)
-> Hash Join (cost=1.02..37.60 rows=6 width=19728)
Hash Cond: (lsr_4.scenario_id =
scnr_1.ray_scenario_id)
-> CTE Scan on lsr_m lsr_4 (cost=0.00..31.80
rows=1259width=19728)
Filter: (((table_name)::text <>
'ACCOUNT'::text)AND ((table_name)::text <> 'LOANDEPO'::text))
-> Hash (cost=1.01..1.01 rows=1 width=3)
-> Seq Scan on ray_sae_scenario_sr scnr_1
(cost=0.00..1.01rows=1 width=3)
-> Materialize (cost=0.00..10.45 rows=30 width=2528)
-> Seq Scan on liqratio_ae_sr las_1 (cost=0.00..10.30
rows=30width=2528)
-> Sort (cost=31.07..31.09 rows=8 width=20858)
Sort Key: (CASE t.table_name WHEN 'ACCOUNT'::text THEN 0 WHEN 'LOANDEPO'::text THEN 0 ELSE 1 END)
-> Result (cost=10.49..30.95 rows=8 width=20858)
-> Append (cost=10.49..30.83 rows=8 width=20854)
-> Nested Loop Left Join (cost=10.49..18.94 rows=1 width=20836)
Join Filter: ((t.margin_agreement)::text =
(o2p_tmp_9062.margin_agreement_reference)::text)
Filter: ((o2p_tmp_9062.rank IS NULL) OR (o2p_tmp_9062.rank = 1))
-> Nested Loop Left Join (cost=0.14..8.53 rows=1 width=20714)
-> CTE Scan on lsr_source t (cost=0.00..0.18 rows=1 width=20706)
Filter: (((table_name)::text = 'COLLATERAL'::text) AND
((collateral_mode)::text= 'R'::text))
-> Index Scan using pk_margin_agreement on margin_agreement ma
(cost=0.14..8.16rows=1 width=106)
Index Cond: ((margin_agreement_reference)::text =
(t.margin_agreement)::text)
-> Subquery Scan on o2p_tmp_9062 (cost=10.35..10.39 rows=1 width=204)
Filter: (o2p_tmp_9062.rank = 1)
-> WindowAgg (cost=10.35..10.37 rows=1 width=236)
-> Sort (cost=10.35..10.35 rows=1 width=228)
Sort Key: link.margin_agreement_reference, link.link_weight
DESC,link.netting_agreement_reference
-> Nested Loop (cost=0.14..10.34 rows=1 width=228)
Join Filter: ((fna.netting_type)::text =
(fnt.code)::text)
-> Nested Loop (cost=0.14..9.31 rows=1 width=232)
-> Seq Scan on fdw_netting_agreement fna
(cost=0.00..1.01rows=1 width=11)
-> Index Scan using i1_margin_agreement_links on
margin_agreement_linkslink (cost=0.14..8.16 rows=1 width=228)
Index Cond:
((netting_agreement_reference)::text= (fna.agreement_reference)::text)
-> Seq Scan on fdw_netting_type fnt (cost=0.00..1.01
rows=1width=4)
Filter: ((netting_class)::text = 'O'::text)
-> Hash Right Join (cost=0.31..11.51 rows=5 width=20856)
Hash Cond: ((ma_1.margin_agreement_reference)::text =
(t_1.margin_agreement)::text)
-> Seq Scan on margin_agreement ma_1 (cost=0.00..10.80 rows=80 width=106)
-> Hash (cost=0.25..0.25 rows=5 width=20824)
-> CTE Scan on lsr_source t_1 (cost=0.00..0.25 rows=5 width=20824)
Filter: (((table_name)::text = ANY
('{CAPFLOOR,EXCHANGE_OPTION,FOREX,FRA,FUTURE,OPTIONS,SWAP,DEAL_IMPORT}'::text[]))OR (((table_name)::text =
'COLLATERAL'::text)AND ((collateral_mode)::text = 'B'::text)))
-> CTE Scan on lsr_source (cost=0.00..0.26 rows=2 width=20856)
Filter: (((table_name)::text <> ALL
('{CAPFLOOR,EXCHANGE_OPTION,FOREX,FRA,FUTURE,OPTIONS,SWAP,DEAL_IMPORT,COLLATERAL}'::text[]))OR (((table_name)::text =
'COLLATERAL'::text)AND ((collateral_mode IS NULL) OR ((collateral_mode)::text <> ALL ('{B,R}'::text[])))))
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, November 27, 2020 4:35 PM
To: Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-sql@lists.postgresql.org
Subject: Re: CTE materialized/not materialized
CAUTION: This email originated from outside of Moody's. Do not click links or open attachments unless you recognize the
senderand know the content is safe.
"Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com> writes:
> Let me rephrase.
> In the case the CTE can be inlined, why PG does not compute 2 plans (P1 with CTE inlined, P2 with CTE materialized)
andcompare them to choose the best?
It would be excessively expensive (even for just one CTE, never mind
several) and it wouldn't fit into the planner's structure very well.
As noted in the comment Alvaro quoted, the point where we make these decisions is pretty far upstream of having any
concretecost estimates.
regards, tom lane
-----------------------------------------
Moody's monitors email communications through its networks for regulatory compliance purposes and to protect its
customers,employees and business and where allowed to do so by applicable law. The information contained in this e-mail
message,and any attachment thereto, is confidential and may not be disclosed without our express permission. If you are
notthe intended recipient or an employee or agent responsible for delivering this message to the intended recipient,
youare hereby notified that you have received this message in error and that any review, dissemination, distribution or
copyingof this message, or any attachment thereto, in whole or in part, is strictly prohibited. If you have received
thismessage in error, please immediately notify us by telephone, fax or e-mail and delete the message and all of its
attachments.Every effort is made to keep our network free from viruses. You should, however, review this e-mail
message,as well as any attachment thereto, for viruses. We take no responsibility and have no liability for any
computervirus which may be transferred via this e-mail message.
This email was sent to you by Moody’s Investors Service EMEA Limited
Registered office address:
One Canada Square
Canary Wharf
London, E14 5FA
Registered in England and Wales No: 8922701
-----------------------------------------