Optimising output SQL (CTEs are slow in Postgresql)

[ozgb]

Hi,

In postgresql, expressing a query using CTEs rather than subqueries can have a significant impact on query runtime:
https://dba.stackexchange.com/questions/188093/why-is-postgres-cte-slower-than-subquery
Archived link to article mentioned: https://web.archive.org/web/20221206070823/https://www.2ndquadrant.com/en/blog/postgresql-ctes-are-optimization-fences/

For the query in the linked discussion:

Execution times: Yours: 22640.389 ms, Subquery: 18451.245 ms, CTE: 46297.825 ms

I've noticed in the playground that CTEs are generated in the first example: https://prql-lang.org/playground/

Is there a way of pushing the SQL generation to avoid using CTEs to produce faster queries? Thanks

[ozgb]

I'm hoping that this isn't too much of an issue, since it seems like the issue with speed occurs when WHERE clauses are made outside the CTE. It looks like prql pushes WHERE clauses inside CTEs.

I haven't tried benchmarking prqls output vs subquery versions yet.

[snth]

PRQL allows you to use the same filter expressions for base table columns as well as derived columns whereas in SQL you would have to use a HAVING clause for the latter. That's one example of why it generates CTEs because then you can use a WHERE clause instead of HAVING.

[max-sixty]

But PRQL generates HAVING too! It's not top priority to avoid surplus CTEs, but if they exist we should try and collapse them. Let's open issues for those...

[snth]

Oh great! I didn't really know, and tbh, I haven't really spent much time looking at the SQL produced. I've mostly been quite happy just to write my PRQL queries and use the data I get back. 😁

[max-sixty]

My understanding is that CTE vs subquery performance has become much less significant over the past few years, and I notice those links are > 5 years old.

Do we have any more up to date benchmarks? Ideally with a query that PRQL has produced or might produce.

PRQL attempts to keep as much as possible within a single subquery. When it needs to, it does create a new subquery. And at the moment, it puts that subquery in a new CTE rather than a parenthesized subquery. Are there are cases where PRQL is making a subquery and didn't need to?

[snth]

Yes, exactly. Before Postgres 12, CTEs where always materialized which I believe means that they created almost temporary tables in memory. Since Postgres 12, CTEs can now be inlined (like subqueries) so that they are now mostly just a logical construct to help us structure our code and should no longer be the optimization fences that they used to be.

Some more details/examples here: https://medium.com/alan/ctes-optimization-8c082efc47ef

[ozgb]

Thanks for the responses + the link - if I do any experiments I'll post results here! 🚀