On 1/18/13, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Jeff Janes <jeff.janes@xxxxxxxxx> writes: >> On Fri, Jan 18, 2013 at 9:29 AM, Robert James <srobertjames@xxxxxxxxx> >> 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? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
