Re: [HACKERS] Slow count(*) again...

On Wed, Feb 2, 2011 at 1:19 PM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
> However, sometimes using an index results in a HORRIBLE HORRIBLE plan.
> I recently encountered the issue myself, and plopping an ANALYZE
> $tablename in there, since I was using a temporary table anyway, make
> all the difference. The planner switched from an index-based query to
> a sequential scan, and a sequential scan was (is) vastly more
> efficient in this particular case.

Yep...

> Personally, I'd get rid of autovacuum/autoanalyze support on temporary
> tables

We don't have any such support, which I think is the root of Mladen's complaint.

> (they typically have short lives and are often accessed
> immediately after creation preventing the auto* stuff from being
> useful anyway), *AND* every time I ask I'm always told "make sure
> ANALYZE the table before you use it".

Yeah. Any kind of bulk load into an empty table can be a problem,
even if it's not temporary. When you load a bunch of data and then
immediately plan a query against it, autoanalyze hasn't had a chance
to do its thing yet, so sometimes you get a lousy plan. In the case
of temporary tables, this can happen even if there's a delay before
you use the data. Some sort of fix for this - where the first query
that needs the stats does an analyze first - seems like it could be
quite useful (although it would suck if the transaction that took it
upon itself to do the analyze then rolled back, losing the stats and
forcing the next guy to do it all over again).

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