From: Michael Fuhr <mike@fuhr.org>
To: James Russell <internationalhobo@gmail.com>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sequential scan being used despite indexes
Date: Tue, 31 Jan 2006 22:08:28 -0700
Message-ID: <20060201050828.GA6681@winnie.fuhr.org> (raw)
In-Reply-To: <3a3a16930601312033w2e4e3186k8f502e5b8c2cc6f2@mail.gmail.com>
References: <3a3a16930601311914w5bd7d1d0hde7b4e495e0deb27@mail.gmail.com>
<43E02B2F.9070202@commandprompt.com>
<20060201035803.GA6324@winnie.fuhr.org>
<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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mike@fuhr.org, internationalhobo@gmail.com, jd@commandprompt.com
Subject: Re: Sequential scan being used despite indexes
In-Reply-To: <20060201050828.GA6681@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox