Re: indexes are farked

On Tue, 2005-08-02 at 13:26, Dr NoName wrote:
> > When's the last time you analyzed this table? And
>
> a few hours before I posted this. vacuumdb --analyze
> also runs every night.

Were there a lot of updates / deletes between when you ran analyze and
when you ran this query? If so, you might want to schedule more
frequent analyze runs and / or include them in whatever script is doing
the udpating / deleting.

Also, you might want to look at tuning your database. I've found that
on machines that can cache most of their data sets, adjusting things
like effective_cache_size and random_page_cost makes a big difference.

> > have you considered
> > running the pg_autovacuum daemon, which will vacuum
> > and analyze for you
> > in the back ground?
>
> We are using postgresql 7.3.2 which doesn't have
> autovacuum.

Sad. I think you can use the pg_autovacuum from 7.4 on 7.3 though I've
not tried it.

Also, 7.3.2 it QUITE out of date. you should, at a minimum, be running
7.3.10. It's a straigh ahead, in place upgrade, and I'm certain there
were bug fixes from 7.3.2 to 7.3.10 that you wouldn't want to run
without.

You might want to schedule analyzes to run every thirty minutes or every
hour.