Skip site navigation (1) Skip section navigation (2)

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

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: (view raw, whole thread or download thread mbox)
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

In response to


pgsql-performance by date

Next:From: Guillaume SmetDate: 2007-02-14 18:12:54
Subject: Re: Proximity query with GIST and row estimation
Previous:From: Kenji MorishigeDate: 2007-02-14 17:25:01
Subject: Re: quad or dual core Intel CPUs

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group