openpolitics.com

Be careful with CTE in PostgreSQL

>A lesser known fact about CTE in PostgreSQL is that the database will evaluate the query inside the CTE and store the results.

PostgreSQL materialized the CTE, meaning, it created a temporary structure with the results of the query defined in the CTE, and only then applied the filter to it. Because the predicate was not applied on the table (but the CTE) PostgreSQL was unable to utilize the index on the ID column.

Unlike PostgreSQL, Oracle is not materializing CTEs by default and the two queries generate the same execution plan.