CTE optimization fence

From: Guy Burgess <guy(at)burgess(dot)co(dot)nz>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: CTE optimization fence
Date: 2018-06-27 03:42:47
Message-ID: ce9c1fb9-c309-f666-3221-5d5f9e66051e@burgess.co.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,
I am running into performance issues with large CTE "WITH" queries (just
for selecting, not updating).  I was surprised to find that the queries
run much faster if I convert the CTEs to subqueries. From googling, I
see that this is due to CTE acting as an optimization fence in PG.
Unfortunately due to the application I'm dealing with, converting all
CTE queries to subquery model is not feasible. Plus, the readability of
CTE is a big bonus.

I see there was some discussion last year about removing the CTE
optimization fence (e.g.
http://www.postgresql-archive.org/CTE-inlining-td5958992.html) but can't
find anything more recent. Does anyone know if this is still under
consideration? For what it's worth, I would love some way to make CTEs
inlined/optimized.

Thank you very much to the developers for a truly amazing database system.

Thanks
Guy

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2018-06-27 03:48:23 Re: CTE optimization fence
Previous Message Jerry Sievers 2018-06-26 20:48:13 Re: found xmin from before relfrozenxid on pg_catalog.pg_authid