Thread

Re: Discussion on missing optimizations

Adam Brusselback <adambrusselback@gmail.com> — 2017-10-08T15:17:01Z

> 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 :)

This is one that I know would help with my database.  There is a ton
of logic stored in views,
which get joined to to the original table to filter the set rather
than imposing that set of
conditions in every separate query.

It would be really nice if the optimizer could simplify those to
eliminate the self join.  It's almost always
on the primary key of a table that the join would happen on, and if
not it'd be a non-nullable column for sure.


On another note:
> turning ORs into UNIONs
This is another one which would be incredibly useful for me.  I've had
to do this manually for performance
reasons far too often.


> Partially agreed. A comment to the article also mentions that some other
> database performs more optimizations depending on the cost of the
> plan. That's not easy to do in our current plan structure, but I think
> it's quite a worthwhile concept.

I would love to see this in Postgres.  It would allow the planner to
not waste cycles unnecessarily on
queries where it's just not needed, and to potentially spend a few
more cycles planning on very
costly queries to save a ton while executing.