CTE where to place the where clause to filter rows sooner (in postgresql)?
CTEs pose as optimization fences in Postgres.

Each CTE is planned, executed and materialized separately. The predicates of a later CTE are not visible in earlier CTEs. Place conditions to eliminate rows as early as possible (a or b in your example).

Or rewrite with subqueries to allow more aggressive optimizations in Postgres. (But you should still eliminate irrelevant rows as early as possible, optimization only goes so far ...)

Details in Craig's blog post:


https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/