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

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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
Cc: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Bruce Momjian <bruce(at)momjian(dot)us>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "david(at)lang(dot)hm" <david(at)lang(dot)hm>, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, Vitalii Tymchyshyn <tivv00(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: [HACKERS] Slow count(*) again...
Date: 2011-02-02 18:32:28
Message-ID: AANLkTin7A_Q=Yc9Wf_5=POp2mVqmaiwqUDsFMLG68H45@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2011-02-02 18:47:21
Subject: Re: [HACKERS] Slow count(*) again...
Previous:From: Kenneth MarshallDate: 2011-02-02 18:32:17
Subject: Re: Server Configuration

pgsql-hackers by date

Next:From: Dimitri FontaineDate: 2011-02-02 18:39:49
Subject: Re: ALTER EXTENSION UPGRADE, v3
Previous:From: David E. WheelerDate: 2011-02-02 18:29:28
Subject: Re: ALTER EXTENSION UPGRADE, v3

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