Re: reindex vs 'analyze'

On Wed, Feb 14, 2007 at 01:07:23PM -0500, Tom Lane wrote:
> Mark Stosberg <mark(at)summersault(dot)com> writes:
> > Your suggestion about the pet_state index was right on. I tried
> > "Analyze" on it, but still got the same bad estimate. However, I then
> > used "reindex" on that index, and that fixed the estimate accuracy,
> > which made the query run faster!
>
> No, the estimate is about the same, and so is the plan. The data seems
> to have changed though --- on Monday you had
>
> -> Bitmap Index Scan on pets_pet_state_idx (cost=0.00..562.50 rows=39571 width=0) (actual time=213.620..213.620 rows=195599 loops=82)
> Index Cond: ((pet_state)::text = 'available'::text)
>
> and now it's
>
> -> Bitmap Index Scan on pets_pet_state_idx (cost=0.00..285.02 rows=41149 width=0) (actual time=22.043..22.043 rows=40397 loops=82)
> Index Cond: ((pet_state)::text = 'available'::text)
>
> Don't tell me you got 155000 pets adopted out yesterday ... what
> happened here?

That seemed be the difference that the "reindex" made. The number of
rows in the table and the number marked "available" is roughly
unchanged.

select count(*) from pets;
--------
304951
(1 row)

select count(*) from pets where pet_state = 'available';
-------
39857

It appears just about 400 were marked as "adopted" yesterday.

> [ thinks... ] One possibility is that those were dead but
> not-yet-vacuumed rows. What's your vacuuming policy on this table?

It gets vacuum analyzed ery two hours throughout most of the day. Once
Nightly we vacuum analyze everything, but most of the time we just do
this table.

> (A bitmap-index-scan plan node will count dead rows as returned,
> unlike all other plan node types, since we haven't actually visited
> the heap yet...)

Thanks again for your help, Tom.

Mark

--
. . . . . . . . . . . . . . . . . . . . . . . . . . .
Mark Stosberg Principal Developer
mark(at)summersault(dot)com Summersault, LLC
765-939-9301 ext 202 database driven websites
. . . . . http://www.summersault.com/ . . . . . . . .