Temp table's effect on performance

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?

Hello

2013/1/18 Robert James <srobertjames(at)gmail(dot)com>:
> I'd like to understand better why manually using a temp table can
> improve performance so much.

one possible effect - there should be different statistic

did you look on EXPLAIN ANALYZE?

Regards

Pavel Stehule

>
> 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?
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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

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

On 1/18/13, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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
>

What information would be helpful to post?

Robert James <srobertjames(at)gmail(dot)com> writes:
> On 1/18/13, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Whether that's the explanation is of course impossible to know from
>> the given (lack of) information.

> What information would be helpful to post?

Both forms of the query, EXPLAIN ANALYZE output for both, along with the
underlying table definitions.

regards, tom lane