4 ms·
Also suffering under mysql 5.7 here and agree. Also even stuff like CTEs/WITH make queries more readable and composite field types like ARRAY are still missing
by Macha 8y ago
Also suffering under mysql 5.7 here and agree. Also even stuff like CTEs/WITH make queries more readable and composite field types like ARRAY are still missing (you see GROUP_CONCAT shenanigans being used instead).
For indexing on function expressions in particular, the workaround we use is to add a generated column and index that.
- colanderman 8y agoBe 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.
- zkomp 8y agoNo, 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-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
- paulddraper 8y agoThis is the best news I've heard all week.
- munk-a 8y agoI 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!
- jeltz 8y agoMy 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.
- colanderman 8y agoOh wow, that is news to me! A welcome change.
- hans_castorp 8y agoWhich 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