Re: Temporary tables under hot standby

On Wed, Apr 25, 2012 at 12:08 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Wed, Apr 25, 2012 at 4:49 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> How important is support for VACUUM on these tables under hot standby?  The
>>> alternative is to fail when a session retains a temporary table across 2B
>>> local transactions.  I do not currently see any challenges sufficient to
>>> motivate not supporting VACUUM, but it might be a useful simplification to
>>> keep in mind.  What about ANALYZE support; how important is the ability to
>>> collect statistics on temporary tables?  Again, I tentatively expect to
>>> support it regardless of the answer.
>>
>> I think it's probably pretty important to support VACUUM, because even
>> ignoring wraparound considerations, not vacuuming tends to cause
>> performance to suck.  I think ANALYZE is less important for the
>> reasons stated above.
>
> ANALYZE is essential for temp tables in many cases... not sure what
> the "reasons stated above" were, I can't resolve that reference.

My theory is that users of a global temp table will have
similar-enough usage patterns that a set of statistics that is good
enough for one user will be good enough for all of them. That might
not be true in all cases, but I think it will simplify things quite a
bit to assume it true for purposes of an initial implementation. And
as I noted, in some cases it might be a clear improvement: right now,
after creating a temp table, you've got to analyze it or you'll just
get the default statistics, which figure to be terrible. Inheriting
the statistics left over from the last guy's analyze figures to be
significantly superior.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company