From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Mark Stosberg <mark(at)summersault(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: reindex vs 'analyze' (was: Re: cube operations slower than geo_distance() on production server) |
Date: | 2007-02-14 18:07:23 |
Message-ID: | 26766.1171476443@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Smet | 2007-02-14 18:12:54 | Re: Proximity query with GIST and row estimation |
Previous Message | Kenji Morishige | 2007-02-14 17:25:01 | Re: quad or dual core Intel CPUs |