Re: Discussion on missing optimizations

On Fri, Oct 06, 2017 at 10:19:54PM -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2017-10-06 21:33:16 -0400, Adam Brusselback wrote:
> >> The article in question is here:
> >> https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/
>
> > That's interesting.
>
> The impression I have in a quick scan is that probably hardly any of these
> are cases that any of the DB designers think are important in themselves.

That's true for some of those. But some of them might become important
when you start pushing WHERE constraints from outside into inner table
sources and subqueries, as dumb-looking constraints can simply appear
from pushing non-dumb-looking constraints.

More than the op optimizations would make a big difference for me:

- turning subqueries into joins

- turning ORs into UNIONs

It is easy enough to work around the lack of this optimization in
many cases, but it does make queries more verbose.

- pushing WHERE constraints from outer queries into the table source
queries (_including_ VIEWs)

- determining that some table in a query that had WHERE constraints
pushed into it... now has a very well-filled out lookup key,
therefore it's the one that should be the table source to start
the plan with, i.e., that it should be first in the outermost loop
of a nested loop join

For me these two would be huge wins. I have to resort to
functions with roughly the same body as views just so that I can
have the optimizer pick the correct plan. This causes a lot of
code duplication in my schemas.

- pushing WHERE constraints from outer queries into HAVING thence WHERE
constraints on GROUP BY queries where the outer constraints are on
columns used to GROUP BY

I find myself making two versions of views that do aggregation: one
that does not, and one that does. This allows me to use the
non-aggregating view in contexts where I need this optimization, but
then I have to re-code the aggregation at that layer. Again, lots of
duplication.

These sorts of optimizations are huge.

> Rather, they fall out of more general optimization attempts, or not,
> depending on the optimization mechanisms in use in a particular DB.
> For example, reducing "WHERE 1=1" to "WHERE TRUE" and then to nothing
> comes out of a constant-subexpression-precalculation mechanism for us,
> whereas "WHERE column=column" doesn't fall to that approach. ISTM it
> would be really dumb to expend planner cycles looking specifically for
> that case, so I guess that DB2 et al are finding it as a side-effect of
> some more general optimization ... I wonder what that is?

If you can reduce the number of compilations / optimization passes for
statements, then spending more time in the optimizer is not a big deal.
So, when invoked via PREPARE I would say spending more cycles looking
for this sort of thing is OK, but in many other cases it's not.

Also, sometimes these cases crop up do to pushing constraints into VIEWs
and sub-queries. In those cases then constant sub-expression
elimination can be a win.

> (edit: a few minutes later, I seem to remember that equivclass.c has
> to do something special with the X=X case, so maybe it could do
> something else special instead, with little new overhead.)

I'd expect that column = column is not trivial to turn into TRUE, not
unless those columns are NOT NULLable.

> > 9. Unneeded Self JOIN
>
> > Can't remember discussions of this.
>
> I can't get very excited about that one either.
>
> In the end, what the article fails to consider is that all of these are
> tradeoffs, not unalloyed goods. If you spend planner cycles on every
> query to look for cases that only the most unabashedly brain-dead ORMs
> ever generate, you're not really doing your users a favor on balance.

I can't get very excited about this one either, though I do believe it
can arise as the author says, "when you build complex views and JOIN
them to each other". Maybe I'm not excited about it because I've not
needed it :)

Nico
--