Skip site navigation (1) Skip section navigation (2)

Re: Statistics with temporary tables, optimizer question

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Statistics with temporary tables, optimizer question
Date: 2010-09-23 14:26:28
Message-ID: AANLkTin5Z3ie1XBCNs=sjDL=nsbXXERVF1xVnxcE_108@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Thu, Sep 23, 2010 at 9:33 AM, Mladen Gogala
<mladen(dot)gogala(at)vmsinfo(dot)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

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-09-23 14:36:59
Subject: Re: Statistics with temporary tables, optimizer question
Previous:From: Mladen GogalaDate: 2010-09-23 13:33:28
Subject: Statistics with temporary tables, optimizer question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group