I get that people with gigantic PostgreSQL installations with stringent performance requirements sometimes need to do odd things to squeeze out the last few percentage points of performance. As the people (well, at least the people close to the ground) at these organizations are fully aware, performance optimizations are extremely volatile with respect to new versions of software, whether it's PostgreSQL, Oracle, the Linux kernel, or what have you. They expect this, and they have processes in place to handle it. If they don't, it's pilot error.
Well put. People on the ground in those situations go to great lengths to freeze the query plan as-is. For them, an upgrade is something that is done after months of planning. They might be surprised by the dropping of this optimization fence, but the surprise won't be in production, and they've got just as good of chance of being pleasantly surprised.
> 2. Add a decorator for WITH (e.g. "WITH MATERIALIZED x (...") to add an > explicit optimization fence. This will for the first time add official > support for a query hint in the syntax which is a quite big precedent.
Yep. It's one we should think very carefully before we introduce.
There's a tiny, oblique precedence for this with Oracle's WITH [FUNCTION | PROCEDURE] syntax. In both cases, the user is able to create an ephemeral object that can be referenced later in the query. Good idea or bad, it's a sign that others have been fine with that conceptual path.
Personally, I'm fine with WITH MATERIALIZED, but I'm also ok with just not promising the fence. I think there is value in letting users break up a complex query into understandable WITH-chunks, and that value shouldn't prevent good performance. The fence will probably still be there anyway in the case of INSERT/UPDATE RETURNING and cases where a CTE is referenced more than once in the query that follows.
