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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
