Re: reindex vs 'analyze' (was: Re: cube operations slower than geo_distance() on production server)

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?

[ thinks... ] One possibility is that those were dead but
not-yet-vacuumed rows. What's your vacuuming policy on 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...)

regards, tom lane