Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B7F379FBCA7 for ; Wed, 14 Feb 2007 14:07:47 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 80297-01 for ; Wed, 14 Feb 2007 14:07:24 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id DA7589FB98B for ; Wed, 14 Feb 2007 14:07:24 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l1EI7NTo026767; Wed, 14 Feb 2007 13:07:24 -0500 (EST) To: Mark Stosberg cc: pgsql-performance@postgresql.org Subject: Re: reindex vs 'analyze' (was: Re: cube operations slower than geo_distance() on production server) In-reply-to: References: <20070213204747.GD23491@summersault.com> <18714.1171437344@sss.pgh.pa.us> Comments: In-reply-to Mark Stosberg message dated "Wed, 14 Feb 2007 11:28:38 -0500" Date: Wed, 14 Feb 2007 13:07:23 -0500 Message-ID: <26766.1171476443@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/155 X-Sequence-Number: 23209 Mark Stosberg 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