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
-----------------------------------------