Learning PostgreSQL 11 - Third Edition

Using CTE when not mandatory

Common table expressions (CTEs) allow developers to write very complex logic. Also, a CTE can be used in several places to optimize performance. However, using a CTE may be problematic in the case of a predicate pushdown, as PostgreSQL doesn't optimize beyond CTE boundaries; each CTE runs in isolation. To understand this limitation, let's have a look at the following two dummy equivalent examples and note the difference between their performance:

\o /dev/null\timing postgres=# SELECT * FROM guru WHERE id = 4;Time: 0,678 mspostgres=# WITH gurus as (SELECT * FROM guru) SELECT * FROM gurus WHERE id = 4;Time: 67,641 ms