From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joe Lester <joe_lester@sweetwater.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index Being Ignored?
Date: Fri, 30 Jun 2006 10:41:45 -0400
Message-ID: <23730.1151678505@sss.pgh.pa.us> (raw)
In-Reply-To: <D2575270-BD66-4654-A9E7-11A4207A00D2@sweetwater.com>
References: <D2575270-BD66-4654-A9E7-11A4207A00D2@sweetwater.com>
Joe Lester <joe_lester@sweetwater.com> writes:
> SELECT count(*) FROM purchase_order_items WHERE expected_quantity > '0'
> 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
Why is the expected row count so far off --- have you analyzed the table
lately? For such a simple WHERE condition the estimate should be pretty
accurate, if the stats are sufficient. If this table is very large you
might need to increase the statistics targets, but more likely you just
haven't got up-to-date stats at all.
The planner *never* "ignores" an index. It may deliberately decide not
to use it, if it thinks the seqscan plan will be faster, as it does in
this case --- note the much higher cost estimate for the indexscan:
> SET ENABLE_SEQSCAN TO OFF;
> EXPLAIN ANALYZE SELECT count(*) FROM purchase_order_items WHERE
> expected_quantity > '0'
> Aggregate (cost=1050659.46..1050659.46 rows=1 width=0) (actual
> time=137.393..137.441 rows=1 loops=1)
> -> Index Scan using purchase_order_items_expected_quantity_idx on
> purchase_order_items (cost=0.00..1049942.25 rows=286882 width=0)
> (actual time=0.756..119.990 rows=7458 loops=1)
> Index Cond: (expected_quantity > 0)
> Total runtime: 139.185 ms
The reason the cost estimate is out of line with reality is mainly that
the rows estimate is out of line with reality. There may be some index
order correlation it's not aware of too.
BTW you might want to think about updating to PG 8.1. Its "bitmap"
index scans are much better suited for queries that are using a
relatively unselective index condition.
regards, tom lane
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: tgl@sss.pgh.pa.us, joe_lester@sweetwater.com
Subject: Re: Index Being Ignored?
In-Reply-To: <23730.1151678505@sss.pgh.pa.us>
* 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