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: 26766.1171476443@sss.pgh.pa.us (view raw or flat)
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

In response to

Responses

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-2014 The PostgreSQL Global Development Group