Re: How exactly does Analyze work?

On Wednesday 25 November 2009 05:34:26 Richard Neill wrote:
> Dear All,
>
> Thanks very much for your help so far. My understanding of PG is getting
> a lot better!
>
> I wonder if I've understood analyze properly: I'm not sure I quite
> understand how specific the statistics gathered actually are.
>
>
> 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"?
>
>
> -----
>
> 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" ?

increasing maintenance_work_mem to several GB (if you have the memory) will
help

>
> 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.
>
> -----
>
>
> Also, I find that, even with the autovacuum daemon running, there was
> one query last night that I had to terminate after an hour. In
> desperation, I restarted postgres, let it take 15 mins to vacuum the
> entire DB, and then re-ran the query (in 8 minutes)
>
> Any ideas how I can troubleshoot this better? The database is only 30GB
> in total - it should (if my intuition is right) be impossible that any
> simple select (even over a modestly complex view) should take longer
> than a multiple of the time required to read all the data from disk?
>
>
>
> Thanks very much,
>
> Richard
>