3 ms·

What pattern ought one look for in the explained plan to identify this issue? Is this something that can likely be improved, technically speaking?

• ximeng 8y ago
  Yes - most other DBMS don't do this. If you have a filter on a derived table that uses a CTE, Postgres will not be able to push it down to the CTE. If the CTE is expensive to calculate (a lot of rows, or joins, or complicated functions), but you don't need many rows from it in the final results, performance is likely to be very poor compared to rewriting as normal subqueries. You should be able to see this on the plan if you see an expensive CTE subquery with limited row results being used. This is unfortunate as it can reduce readability significantly. There's resistance to fixing this as it is considered a breaking change apparently, which it may be for CTEs used for data manipulation (INSERT / UPDATE / DELETE can operate using CTEs), but it shouldn't be for plain SELECTs. however no obvious plans for this to change. https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/ https://blog.2ndquadrant.com/postgresql-ctes-are-optimizatio... has more.