Re: Temp table's effect on performance

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Fri, Jan 18, 2013 at 9:29 AM, Robert James <srobertjames(at)gmail(dot)com> wrote:
>> 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.

Whether you've done ANALYZE or not, the planner can see the physical
size of the temp table, which allows it to make a rowcount estimate
based on a guess as to the average row width (which it can make, in
a pretty squishy way, given only the column datatypes). Now an
estimate gotten that way can be pretty far off, but it might still
be much better than what we can come up with for a sub-select (view).
Of course if you *have* done an ANALYZE on the temp table then the
planner is far better informed than when considering a view.

Whether that's the explanation is of course impossible to know from
the given (lack of) information.

regards, tom lane