On Sat, 19 Oct 2019 at 13:36, Stephen Frost <sfr...@snowman.net> wrote:
> Greetings, > > * Isaac Morland (isaac.morl...@gmail.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.
