Thread: CTE materialized/not materialized
Hello,
In PG12, a CTE is automatically materialized as soon as it is used more than once in the outer query.
with big_query as (…) select * from big_query where …
Ok, good plan: PG inlined the big_query in the outer query (using indexed columns avoiding full scan, etc)
Then:
with big_query as (…) select * from big_query where … union all select * from big_query where …
Poor plan: PG materialized big_query because it appears twice in the outer sql
Ok, let’s enforce:
With big_query as not materialized as (…) select * from big_query where … union all select * from big_query where …
Back to a good plan!
The problem I have now, is that I have to decide the plan that PG must follow. It’s a kind of Oracle hint.
Is it possible, for PG, to decide, by its own, the best way to go (materialized or inlined) for each CTE encountered in the query,
rather than deciding to materialized as soon as the CTE is found more than once in the outer query?
Am I clear?
Regards.
-----------------------------------------
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 not the intended recipient or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that you have received this message in error and that any review, dissemination, distribution or copying of this message, or any attachment thereto, in whole or in part, is strictly prohibited. If you have received this message 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 computer virus 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
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
-----------------------------------------
On 2020-Nov-26, Voillequin, Jean-Marc wrote:
> The problem I have now, is that I have to decide the plan that PG must
> follow. It's a kind of Oracle hint.
>
> Is it possible, for PG, to decide, by its own, the best way to go
> (materialized or inlined) for each CTE encountered in the query,
> rather than deciding to materialized as soon as the CTE is found more
> than once in the outer query? Am I clear?
Actually, the planner does make a choice. Only if it's wrong then it is
necessary to mark the CTE for materialization. Previously, it always
materialized, causing many queries to have bad performance with no
recourse.
One could argue that we could do better at making the choice, but that
seems an unimplemented feature, not a bug.
Quoting src/backend/optimizer/plan/subselect.c:
* We cannot inline if any of these conditions hold:
*
* 1. The user said not to (the CTEMaterializeAlways option).
*
* 2. The CTE is recursive.
*
* 3. The CTE has side-effects; this includes either not being a plain
* SELECT, or containing volatile functions. Inlining might change
* the side-effects, which would be bad.
*
* 4. The CTE is multiply-referenced and contains a self-reference to
* a recursive CTE outside itself. Inlining would result in multiple
* recursive self-references, which we don't support.
*
* Otherwise, we have an option whether to inline or not. That should
* always be a win if there's just a single reference, but if the CTE
* is multiply-referenced then it's unclear: inlining adds duplicate
* computations, but the ability to absorb restrictions from the outer
* query level could outweigh that. We do not have nearly enough
* information at this point to tell whether that's true, so we let
* the user express a preference. Our default behavior is to inline
* only singly-referenced CTEs, but a CTE marked CTEMaterializeNever
* will be inlined even if multiply referenced.
--
Álvaro Herrera https://www.EnterpriseDB.com
Thanks.
I agree it's not a bug.
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) and
comparethem to choose the best?
-----Original Message-----
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Sent: Friday, November 27, 2020 3:54 PM
To: Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
Cc: 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.
On 2020-Nov-26, Voillequin, Jean-Marc wrote:
> The problem I have now, is that I have to decide the plan that PG must
> follow. It's a kind of Oracle hint.
>
> Is it possible, for PG, to decide, by its own, the best way to go
> (materialized or inlined) for each CTE encountered in the query,
> rather than deciding to materialized as soon as the CTE is found more
> than once in the outer query? Am I clear?
Actually, the planner does make a choice. Only if it's wrong then it is necessary to mark the CTE for materialization.
Previously, it always materialized, causing many queries to have bad performance with no recourse.
One could argue that we could do better at making the choice, but that seems an unimplemented feature, not a bug.
Quoting src/backend/optimizer/plan/subselect.c:
* We cannot inline if any of these conditions hold:
*
* 1. The user said not to (the CTEMaterializeAlways option).
*
* 2. The CTE is recursive.
*
* 3. The CTE has side-effects; this includes either not being a plain
* SELECT, or containing volatile functions. Inlining might change
* the side-effects, which would be bad.
*
* 4. The CTE is multiply-referenced and contains a self-reference to
* a recursive CTE outside itself. Inlining would result in multiple
* recursive self-references, which we don't support.
*
* Otherwise, we have an option whether to inline or not. That should
* always be a win if there's just a single reference, but if the CTE
* is multiply-referenced then it's unclear: inlining adds duplicate
* computations, but the ability to absorb restrictions from the outer
* query level could outweigh that. We do not have nearly enough
* information at this point to tell whether that's true, so we let
* the user express a preference. Our default behavior is to inline
* only singly-referenced CTEs, but a CTE marked CTEMaterializeNever
* will be inlined even if multiply referenced.
--
Álvaro Herrera https://www.EnterpriseDB.com
-----------------------------------------
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
-----------------------------------------
"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 concrete cost estimates.
regards, tom lane
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
-----------------------------------------
On 2020-Nov-27, Voillequin, Jean-Marc wrote: > Thank you Tom & Alvaro. > I'm still dreaming of such query planner! An idea mentioned several times is that of making more expensive optimizer passes for certain plans -- either at the user's request or merely based on the initial estimated cost being very large. A query that's seems more expensive to execute would also seem to warrant a greater optimization effort. (But if it's automated and we get it wrong, then we'd get complaints that we spend pointless time in the optimizer.) Your sample plan has 3 CTEs, so we would have to plan eight queries for the brute-force way -- one per combination of each CTE inlined or materialized. Maybe it's worth the optimizer cost, maybe not. Now maybe there's a smarter approach than brute-forcing it.