Re: Backport "WITH ... AS MATERIALIZED" syntax to <12?

On Sat, 19 Oct 2019 at 13:36, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Isaac Morland (isaac(dot)morland(at)gmail(dot)com) wrote:
> > That embeds a temporary hack in the application code indefinitely.
>
> ... one could argue the same about having to say AS MATERIALIZED.
>

I think OFFSET 0 is a hack - the fact that it forces an optimization fence
feels like an oddity. By contrast, saying AS MATERIALIZED means materialize
the CTE. I suppose you could argue that the need to be able to request that
is a temporary hack until query optimization improves further, but I don't
think that's realistic. For the foreseeable future we will need to be able
to tell the query planner that it is wrong. I mean, in principle the DB
should figure out for itself which (non-constraint) indexes are needed. But
I don't see any proposals to attempt to implement that.

Side note: I am frequently disappointed by the query planner. I have had
many situations in which a nice simple strategy of looking up some tiny
number of records in an index and then following more indices to get joined
records would have worked, but instead it did a linear scan through the
wrong starting table. So I'm very glad the AS MATERIALIZED now exists for
when it's needed. On the other hand, I recognize that the reason I'm
disappointed is because my expectations are so high: often I've written a
query that joins several views together, meaning that under the covers it's
really joining maybe 20 tables, and it comes back with the answer
instantly. So in effect the query planner is just good enough to make me
expect it to be even better than it is.