Re: Index Being Ignored?

[Markus Schaber <schabi(at)logix-tt(dot)com>]

Hi, Joe,

Joe Lester wrote:
> 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)

The query planner estimates that your filter will hit 286882 rows, while
in reality it hits only 7458 rows. That's why the query planer chooses a
sequential scan.

It seems that the statistics for the column expected_quantity are off.

My suggestions:

- make shure that the statistics are current by analyzing the table
appropriately (e. G. by using the autovacuum daemon from contrib).

- increase the statistics target for this column.

- if you run this query very often, an conditional index might make sense:

CREATE INDEX purchase_order_having_quantity_idx ON purchase_order_items
(expected_quantity) WHERE expected_quantity > 0;


HTH,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org