From: Alvaro Herrera <alvherre@commandprompt.com>
To: Joe Lester <joe_lester@sweetwater.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index Being Ignored?
Date: Fri, 30 Jun 2006 10:14:55 -0400
Message-ID: <20060630141455.GC21718@surnet.cl> (raw)
In-Reply-To: <D2575270-BD66-4654-A9E7-11A4207A00D2@sweetwater.com>
References: <D2575270-BD66-4654-A9E7-11A4207A00D2@sweetwater.com>
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
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: alvherre@commandprompt.com, joe_lester@sweetwater.com
Subject: Re: Index Being Ignored?
In-Reply-To: <20060630141455.GC21718@surnet.cl>
* 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