Waiting for Postgres 17: Better Query Plans for Materialized CTE Scans

*Find the article on our blog here:*

https://pganalyze.com/blog/5mins-postgres-17-materialized-cte-scans

In E118 of "5mins of Postgres" we discuss two changes in the upcoming Postgres 17 release that improve query plans for queries that involve CTEs. This can improve query plans where you would see an explicit CTE scan, due to use of the MATERIALIZED keyword, or because Postgres wasn't able to pull up a query to the upper plan level.

*Learn more about pganalyze:*

https://pganalyze.com

https://pganalyze.com/newsletter

https://www.linkedin.com/company/pganalyze

*Check out the pganalyze library for eBooks, webinars, and more:*

https://pganalyze.com/resources

📑 *What we have discussed in this episode of 5mins of Postgres:*

*Extract column statistics from CTE references, if possible - Postgres commit by Tom Lane, co-authored by Jian Guo*

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f7816aec2;hp=06c70849fb26ac431a722b1d10cffe1c65e728a4

*Wrong rows estimations with joins of CTEs slows queries by more than factor 500 - Mailinglist thread by Hans Buschmann on pgsql-hackers*

https://www.postgresql.org/message-id/flat/4504e67078d648cdac3651b2960da6e7%40nidsa.net

*BUG #18466: Wrong row estimate for nested loop - Mailinglist thread by Yan Wu on pgsql-bugs*

https://www.postgresql.org/message-id/flat/18466-1d296028273322e2%40postgresql.org

*Propagate pathkeys from CTEs up to the outer query - Postgres commit by Tom Lane, authored by Richard Guo*

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a65724dfa73db8b451d0c874a9161935a34a914e

*Propagate pathkeys from CTEs up to the outer query - Mailinglist thread by Richard Guo on pgsql-hackers*

https://www.postgresql.org/message-id/flat/CAMbWs49xYd3f8CrE8-WW3--dV1zH_sDSDn-vs2DzHj81Wcnsew%40mail.gmail.com

*What are the "tenk" and "onek" tables in the Postgres docs? - 5mins of Postgres E108 transcript*

https://pganalyze.com/blog/5mins-postgres-17-explain-subplan#what-are-the-tenk-and-onek-tables-in-the-postgres-docs

#postgres #postgresql #queryoptimization