CTE in a Recursive Union

From: Joe Wildish <joe-postgresql(dot)org(at)elusive(dot)cx>
To: pgsql-general(at)postgresql(dot)org
Subject: CTE in a Recursive Union
Date: 2017-08-29 22:47:04
Message-ID: 722D044E-87F8-453F-8B68-DC7DFC2311C9@elusive.cx
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Joe Wildish 2017-08-29 22:47:10 CTE in a Recursive Union
Previous Message Thomas Munro 2017-08-29 22:13:57 Re: Serializable Isolation and read/write conflict with index and different keys