Mark Stosberg <mark@xxxxxxxxxxxxxxx> 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
