I have a recursive view that uses a CTE in both the recursive and non-recursive operands to the union operator. This CTE is quite complex --- it calls various others CTEs and does some aggregations, etc. Looking at the explain plan for the view I can see that a "CTE Scan" with a Filter is being performed for the recursive call. As one would expect, due to the recursion, it is scanned many many times and is killing the performance of the view.
My question is this: are there any ways to make a CTE aware of the underlying indexes of its contributing tables? Given the complexity of the CTE, and that it is referenced on both sides of the union, I am reluctant to simply substitue the definition of the CTE into the FROM clause. It seems my only option is to create the CTE as a materialized view and create appropriate indexes to allow the recursive union to perform acceptably. Does anyone have other suggestions for how best to achieve a performant query? Thanks, -Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
