On Fri, Jan 18, 2013 at 9:29 AM, Robert James <srobertjames@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
