From: Mark Stosberg <mark@summersault.com>
To: pgsql-performance@postgresql.org
Subject: Re: reindex vs 'analyze'
Date: Wed, 14 Feb 2007 14:05:02 -0500
Message-ID: <20070214190502.GB33364@summersault.com> (raw)
In-Reply-To: <26766.1171476443@sss.pgh.pa.us>
References: <eqq3k3$1mpu$1@news.hub.org>
<b42b73150702121103l297b690w84dde8ef9ef8d2f1@mail.gmail.com>
<eqqgc5$kle$1@news.hub.org>
<b42b73150702130615t22776654u9e0925e0fe368bc1@mail.gmail.com>
<b42b73150702130631x332e4f18s34a057c0c47c5e8c@mail.gmail.com>
<20070213204747.GD23491@summersault.com>
<18714.1171437344@sss.pgh.pa.us>
<b42b73150702140558l3220d2e0v3e5e38d64b6e35c5@mail.gmail.com>
<eqvdbm$1c8p$1@news.hub.org>
<26766.1171476443@sss.pgh.pa.us>
On Wed, Feb 14, 2007 at 01:07:23PM -0500, Tom Lane wrote:
> Mark Stosberg <mark@summersault.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?
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@summersault.com Summersault, LLC
765-939-9301 ext 202 database driven websites
. . . . . http://www.summersault.com/ . . . . . . . .
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark@summersault.com
Subject: Re: reindex vs 'analyze'
In-Reply-To: <20070214190502.GB33364@summersault.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox