Re: Index Being Ignored?

Joe Lester <joe_lester(at)sweetwater(dot)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