From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Stosberg <mark@summersault.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: reindex vs 'analyze' (was: Re: cube operations slower than geo_distance() on production server)
Date: Wed, 14 Feb 2007 13:07:23 -0500
Message-ID: <26766.1171476443@sss.pgh.pa.us> (raw)
In-Reply-To: <eqvdbm$1c8p$1@news.hub.org>
References: <eqiht3$2fvu$1@news.hub.org>
<b42b73150702091731x57996613hb50af0296007436c@mail.gmail.com>
<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>
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?
[ 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
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: tgl@sss.pgh.pa.us, mark@summersault.com
Subject: Re: reindex vs 'analyze' (was: Re: cube operations slower than geo_distance() on production server)
In-Reply-To: <26766.1171476443@sss.pgh.pa.us>
* 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