Re:PostgreSQL 11.3 and 10.8
by colanderman on Thursday May 09, 2019 @01:18PM
(#19870156)
Be warned that in PostgreSQL, WITH is an optimization barrier, and is planned to remain that way to serve that purpose. If you can, prefer using views to enhance readability (and testability as a bonus). PostgreSQL views (unlike those in MySQL) do not prevent optimization across them.
No, CTEs are not planned to remain a barrier, this is already fixed in the next version which is in feature freeze right now.
https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
My favorite feature of PostgreSQL 12 except perhaps REINDEX CONCURRENTLY, but I am very biased since I was involved in both patches (both were large projects involving many devs and reviewers). It is awesome to finally see both land.
Re:PostgreSQL 11.3 and 10.8
by paulddraper on Thursday May 09, 2019 @01:57PM
(#19870516)
This is the best news I've heard all week.
I have tried to express my joy at this news to my less SQL literate co-workers... that failed so I wanted to let it out here. This is the best news, I am overjoyed!
Re:PostgreSQL 11.3 and 10.8
by colanderman on Thursday May 09, 2019 @06:15PM
(#19872970)
Oh wow, that is news to me! A welcome change.
Re:PostgreSQL 11.3 and 10.8
by hans_castorp on Thursday May 09, 2019 @02:09PM
(#19870635)
Which is very often a good thing. I have tuned more than one query by moving a sub-query/derived table into a CTE.
What bothers me more, that a CTE prevents parallel execution, but I think that too is fixed with Postgres 12
For indexing on function expressions in particular, the workaround we use is to add a generated column and index that.