Re: How exactly does Analyze work?

Richard Neill <rn214(at)cam(dot)ac(dot)uk> writes:
> In particular, what happens in the following case:
> 1. I start with have a table with 100 million rows, and column wid has
> linearly distributed values from 45-90. (wid is indexed)

> 2. I run vacuum analyze

> 3. I insert about 2 million rows, all of which have the new wid of 91.

> 4. I then do a select * WHERE wid = 91.

> How smart is analyze? Will it actually say "well, I've never seen 91 in
> this table, because all the values only go up to 90, so you'd better do
> a sequential scan"?

ANALYZE is not magic. The system won't know that the 91's are there
until you re-ANALYZE (either manually or automatically). In a case
like this I expect the planner would assume there are very few matching
rows and go for an indexscan. That might still be the right thing given
this specific scenario (need to fetch 2% of the table), but it certainly
wouldn't be if you had say half of the table matching the query.
Moral: re-ANALYZE after any bulk load.

> On another note, I notice that if I ever manually run vacuum or analyze,
> the performance of the database drops to the point where many of the
> operators get kicked out. Is there any way to run them "nice" ?

See vacuum_cost_delay.

> We need to maintain a response time of under 1 second all day for simple
> queries (which usually run in about 22ms). But Vacuum or Analyze seem to
> lock up the system for a few minutes, during which other queries block
> on them, although there is still plenty of CPU spare.

It sounds to me like you don't really have enough disk I/O bandwidth
to meet your performance requirements. All the CPU in the world won't
help you if you didn't spend any money on the disks :-(. You might be
able to alleviate this with vacuum_cost_delay, but it's a band-aid.

regards, tom lane