From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Richard Neill <rn214(at)cam(dot)ac(dot)uk> |
Cc: | PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: How exactly does Analyze work? |
Date: | 2009-11-25 15:22:03 |
Message-ID: | 18036.1259162523@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | marcin mank | 2009-11-25 15:22:47 | Re: DELETE performance problem |
Previous Message | Kevin Kempter | 2009-11-25 15:22:01 | Re: How exactly does Analyze work? |