Re: Still confused about VACUUM vs. VACUUM FULL

On Thu, 2003-06-12 at 13:16, Tom Lane wrote:

> > We're also ANALYZE-ing the largest 12-18 tables on a cycle: every twenty
> > minutes, a daemon wakes up and ANALYZEs until they're all done or two
> > minutes has elapsed, whichever comes first.
>
> That sounds a tad excessive; are the statistics really changing that
> fast?


Well, I have some convincing evidence on this. One table at the center
of some of our biggest, hairiest queries uses an index on a timestamp.
Generally, the queries run looking back about 24 hours. We are inserting
40,000 rows a day (and deleting the same number, but the deletes happen
all at once, and the inserts happen during nearly every part of the
clock).

I've done

  explain select * from foo where the_time < <some-timestamp>;

and found that I could slice it down to a one-minute interval or so:
before 11:42 AM, and the optimizer uses a sequential scan; after, and it
uses the index.

And of course it stays at that point, even if another 10,000 rows get
inserted with current timestamps, until it's ANALYZEd again. So two or
three ANALYZEs per hour is not excessive, if it will keep the index
usable under the "right" circumstances.

--
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise