Re: ANALYZE and index/stats degradation

On Mon, July 2, 2007 18:15, Gregory Stark wrote:

>> So I suppose the planner has a good reason to ignore the index at that
>> point. I'm assuming that this is something to do with the correlation
>> between the index and the column's statistics degrading in some way.
>
> Best to post "explain analyze <query>" for when the performance is good
> and
> bad. Perhaps also an explain analyze for the query with enable_seqscan off
> when it's bad.

Can't easily do that anymore... AFAIR the plans were all identical
anyway, except in the "enable_seqscan bad" case which used a sequential
scan instead of using the index. The queries are very simple, along the
lines of "select * from foo where id >= x and id < y".

> Also, which version of Postgres is this?

It was an 8.2 version.

> It's possible you just need vacuum to run more frequently on this table
> and
> autovacuum isn't doing it often enough. In which case you might have a
> cron
> job run vacuum (or vacuum analyze) on this table more frequently.

Actually, come to think of it, I don't think I'd want any vacuums at all
on this particular table. Just the analyze on the primary key, no
vacuums, no statistics on anything else. Unfortunately it's not just one
table, but a set of tables that can be created dynamically. I could
change that, but in this particular case I don't think I should.

Jeroen