Re: reindex vs 'analyze'

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?
>
> [ 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...)

Today I noticed a combination of related mistakes here.

1. The Vacuum commands were being logged to a file that didn't exist.
I'm mot sure if this prevented them being run. I had copied the cron
entry for another machine, but neglected to create /var/log/pgsql:

vacuumdb -z --table pets -d saveapet >> /var/log/pgsql/vacuum.log 2>&1

###

However, I again noticed that the row counts were horribly off on the
'pet_state' index, and again used REINDEX to fix it. (Examples below).
However, if the "VACUUM ANALYZE" wasn't actually run, that does seem
like it could have been related.

I'll have to see how things are tomorrow after a full round of database
vacuuming.

Mark

-> Bitmap Index Scan on pets_pet_state_idx (cost=0.00..337.29
rows=39226 width=0) (actual time=77.158. .77.158 rows=144956
loops=81)
Index Cond: ((pet_state)::text =
'available'::text)
Total runtime: 8327.261 ms

-> Bitmap Index Scan on pets_pet_state_idx (cost=0.00..271.71
rows=39347 width=0) (actual time=15.466..15.466 rows=40109 loops=81)
Index Cond: ((pet_state)::text =
'available'::text)
Total runtime: 1404.124 ms