I'd like to understand better why manually using a temp table can
improve performance so much.
I had one complicated query that performed well. I replaced a table
in it with a reference to a view, which was really just the table with
an inner join, and performance worsened by 2000x. Literally.
I then modified it to first manually SELECT the view into a temp
table, and performance returned to close to the original query. The
temp table had the same indexes as the original one.
How is that? What does the temp table do that the planner can't do
itself? Don't planner uses temp structures too?
In other words: Since my query is 100% identical algebraicly to not
using a temp table, why is it so much faster? Why can't the planner
work in the exact same order?