On Wed, Feb 14, 2007 at 01:07:23PM -0500, Tom Lane wrote: > 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? 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@xxxxxxxxxxxxxxx Summersault, LLC 765-939-9301 ext 202 database driven websites . . . . . http://www.summersault.com/ . . . . . . . .
