Re: BUG: PG do not use index

In response to Eugen(dot)Konkov(at)aldec(dot)com:

> > The standard question: when was the last time you did a vacuum analyze
> > on this table?
>
> Never did.

That's your problem. Without updated statistics on that table, PostgreSQL
probably thinks that it's so small that an index scan wouldn't be any
faster.

> Fortunately, The Auto-Vacuum Daemon monitors table activity and performs
> VACUUMs when necessary. This eliminates the need for administrators to worry
> about disk space recovery in all but the most unusual cases.
> http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html

Have you verified that this is running correctly (will be information
in the log files each time it runs). Have you verified that the settings
are aggressive enough to be processing this particular table often enough.
The easiest way to test this is to run an EXPLAIN ANALYZE on the query,
then manually VACUUM ANALYZE the table, then run another EXPLAIN ANALYZE.
If the differences in times and statistics between the two EXPLAINs is
significant, then autovacuum probably isn't doing enough. Also, if it
turns out that autovacuum isn't cutting it, you'll probably need to run
VACUUM FULL and REINDEX on the whole database to get things back under
control.

> It seems I am using old version.
> Need I do something more than just reinstall binaries?
> My current version:
> C:\Program Files\PostgreSQL\8.0\bin>postgres --version
> postgres (PostgreSQL) 8.0.3

You can upgrade to 8.0.15 simply by reinstalling, restarting the postmaster
and running a REINDEX (the REINDEX may not be required, see the release
notes for 8.0.6):
http://www.postgresql.org/docs/8.0/static/release-8-0-6.html

However, the 8.0 series is lacking a lot of improvements. If you can
spare some downtime, I highly recommend you upgrade to 8.2.7. This is
a bit more work though, because you'll have to dump your database, then
reinstall PG, then restore the data into a freshly created cluster.

In any event, make sure you have a good backup before doing either
upgrade.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023