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
BTW, you should upgrade (to 8.0.8) unless you want known bugs to destroy
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
In response to
pgsql-performance by date
|Next:||From: Markus Schaber||Date: 2006-06-30 14:29:06|
|Subject: Re: Index Being Ignored?|
|Previous:||From: Ksenia Marasanova||Date: 2006-06-30 14:13:52|
|Subject: newly created database makes queries run 300% faster|