Hacker News Reader

Comment by brasetvik | Hacker News Reader

Another feature that should probably be on that list is Common Table Expressions: https://momjian.us/main/writings/pgsql/cte.pdf

(I still frequently run into people who work a lot with SQL that don't use CTEs, though less than before :)

View on HN

Just be aware that CTEs add optimizer barriers in PostgreSQL. So a query like:

    SELECT *
    FROM ( ... ) AS results

may have a completely different query plan than a query like

    WITH results AS ( ... )
    SELECT *
    FROM results

This may or may not be desirable, and is important to be aware of.

It is a balance always... but code maintainability isn't something to be dismissed. If utilizing CTEs greatly improves your readability it's probably worth utilizing CTEs until there is a performance need to switch off (and it's pretty trivial to de-CTE a query)

Yes but the fact that they are an optimization fence is a major drawback.

Not always. I use them to force hash joins. This way you can filter table A on an index, and filter table B on an index, then join them with a hash join. In a straight join postgres would try to use the same index for all of the joins. At least that was my experience. Maybe I'm not understanding the docs properly or using the correct terminology. I know that the end result was 10x faster than any index I could come up with.

I think the article intended to highlight features specific to PostgreSQL. CTEs are undeniably great, especially recursive CTEs, but they're also part of the SQL:1999 standard and available in lots of databases.

Reply on news.ycombinator.com