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

On Sat, Oct 19, 2019 at 02:35:42PM -0400, Isaac Morland wrote:
> 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.

Well, since geqo_threshold = 12 is the default, for a 20-table join, you
are using genetic query optimization (GEQO) in PG 12 without
MATERIALIZED:

https://www.postgresql.org/docs/12/geqo.html

and GEQO assumes it would take too long to fully test all optimization
possibilities, so it randomly checks just some of them. Therefore, it
is no surprise you are disappointed in its output.

In a way, when you are using materialized CTEs, you are doing the
optimization yourself, in your SQL code, and then the table join count
drops low enough that GEQO is not used and Postgres fully tests all
optimization possibilities. This is behavior I had never considered ---
the idea that the user is partly replacing the optimizer, and that using
materialized CTEs prevents the problems that require the use of GEQO.

I guess my big take-away is that not only can MATERIALIZE change the
quality of query plans but it can also improve the quality of query
plans if it prevents GEQO from being used.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +