Re: Temp table's effect on performance

On 1/18/13, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jeff Janes <jeff.janes@gmail.com> writes:
>> On Fri, Jan 18, 2013 at 9:29 AM, Robert James <srobertjames@gmail.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
>

What information would be helpful to post?