Re: Temp table's effect on performance

On Fri, Jan 18, 2013 at 9:29 AM, Robert James <srobertjames(at)gmail(dot)com> wrote:
> 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 don't see how this can be answered at a general level. Something
very specific happened. Without knowing what that specific thing is,
what can we say about it?

> 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?

Most likely the temp table just got lucky. If you re-wrote hundreds
of other queries to use a temp table, would all of them systematically
get faster? If so, that would certainly be a thing to wonder about.
But a single example, with no details....well, what can be said about
that? When you choose how to make the temp table, you are probably
imposing some intuitive knowledge you have (but the planner does not)
about the nature of your data.

> 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?

Unless you are doing ANALYZE on your temp table, then the planner has
to make some guesses about the size and selectivity and correlations
involved. Those guesses probably just got lucky at being better in
this particular case than the real statistics. Without having any
details, it is hard to say why. It is easy to spot the one time the
planner gets it wrong, while the 9,999 times it got it right go
unnoticed.

Cheers,

Jeff