Skip site navigation (1) Skip section navigation (2)

Re: Index Being Ignored?

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Joe Lester <joe_lester(at)sweetwater(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Index Being Ignored?
Date: 2006-06-30 14:14:55
Message-ID: 20060630141455.GC21718@surnet.cl (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Markus SchaberDate: 2006-06-30 14:29:06
Subject: Re: Index Being Ignored?
Previous:From: Ksenia MarasanovaDate: 2006-06-30 14:13:52
Subject: newly created database makes queries run 300% faster

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group