Re: Statistics with temporary tables, optimizer question

On Thu, Sep 23, 2010 at 9:33 AM, Mladen Gogala
<mladen.gogala@vmsinfo.com> wrote:
> My question is how does the optimizer calculate stats for the temporary
> tables? I am probably not expected to do a vacuum analyze on the
> temporary table, after finishing the inserts? How exactly does the optimizer
> deal
> with the temporary tables?

The advice I've seen says to manually run an ANALYZE on a temporary
table which you're going to be doing something performance-intensive
with. Old thread:
  http://archives.postgresql.org/pgsql-general/2004-01/msg01553.php

and the 9.0 docs briefly mention as well:
  http://www.postgresql.org/docs/current/static/sql-createtable.html
saying "... appropriate vacuum and analyze operations should be
performed via session SQL commands. For example, if a temporary table
is going to be used in complex queries, it is wise to run ANALYZE on
the temporary table after it is populated. "

I think the planner assumes some bogus default (1000?) number of rows
for temporary tables otherwise. Can't find a more-reliable reference,
but see here:
  http://stackoverflow.com/questions/486154/postgresql-temporary-tables

I also use temp. tables fairly heavily, and I just run an ANALYZE on
any decently-sized tables I'm going to use in further queries, which
seems to work well for me.

Josh