Date: Tue, 31 Jan 2006 22:08:28 -0700
From: Michael Fuhr <mike@fuhr.org>
To: James Russell <internationalhobo@gmail.com>
Cc: "Joshua D. Drake" <jd@commandprompt.com>, pgsql-performance@postgresql.org
Subject: Re: Sequential scan being used despite indexes
Message-ID: <20060201050828.GA6681@winnie.fuhr.org>
References: <3a3a16930601311914w5bd7d1d0hde7b4e495e0deb27@mail.gmail.com>
	<43E02B2F.9070202@commandprompt.com>
	<20060201035803.GA6324@winnie.fuhr.org>
	<3a3a16930601312033w2e4e3186k8f502e5b8c2cc6f2@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3a3a16930601312033w2e4e3186k8f502e5b8c2cc6f2@mail.gmail.com>

On Wed, Feb 01, 2006 at 01:33:08PM +0900, James Russell wrote:
> Reading about this issue further in the FAQ, it seems that I should ensure
> that Postgres has adequate and accurate information about the tables in
> question by regularly running VACUUM ANALYZE, something I don't do
> currently.

Many people use a cron job (or the equivalent) to run VACUUM ANALYZE
at regular intervals; some also use the pg_autovacuum daemon, which
is a contrib module in 8.0 and earlier and part of the backend as of
8.1.

How often to vacuum/analyze depends on usage.  Once per day is
commonly cited, but busy tables might need it more often than that.
Just recently somebody had a table that could have used vacuuming
every five minutes or less (all records were updated every 30
seconds); pg_autovacuum can be useful in such cases.

> I disabled SeqScan as per the FAQ, and it indeed was a lot slower so
> Postgres was making the right choice in this case.

The planner might be making the right choice given the statistics
it has, but it's possible that better statistics would lead to a
different plan, perhaps one where an index scan would be faster.

What happens if you run VACUUM ANALYZE on all the tables, then run
the query again with EXPLAIN ANALYZE?

-- 
Michael Fuhr