Date: Fri, 30 Jun 2006 10:14:55 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Joe Lester <joe_lester@sweetwater.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index Being Ignored?
Message-ID: <20060630141455.GC21718@surnet.cl>
Mail-Followup-To: Joe Lester <joe_lester@sweetwater.com>,
	pgsql-performance@postgresql.org
References: <D2575270-BD66-4654-A9E7-11A4207A00D2@sweetwater.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <D2575270-BD66-4654-A9E7-11A4207A00D2@sweetwater.com>
User-Agent: Mutt/1.5.11+cvs20060403

Joe Lester wrote:
> I have a index question. My table has 800K rows and I a doing a basic  
> query on an indexed integer field which takes over 2 seconds to  
> complete because it's ignoring the index for some reason. Any ideas  
> as to why it's ignoring the index? I'm using postgres 8.0.2.
> 
> SELECT count(*) FROM purchase_order_items WHERE expected_quantity > '0'
> 
> EXPLAIN ANALYZE reveals that it's not using the index...
> 
> Aggregate  (cost=22695.28..22695.28 rows=1 width=0) (actual  
> time=2205.688..2205.724 rows=1 loops=1)
>   ->  Seq Scan on purchase_order_items  (cost=0.00..21978.08  
> rows=286882 width=0) (actual time=0.535..2184.405 rows=7458 loops=1)
>         Filter: (expected_quantity > 0)
> Total runtime: 2207.203 ms

The estimated rowcount is far off.  When did you last run ANALYZE on
this table?

BTW, you should upgrade (to 8.0.8) unless you want known bugs to destroy
your data.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support