Re: Index Being Ignored?

great!

Thanks Markus and Tom!

On Jun 30, 2006, at 10:29 AM, Markus Schaber wrote:

> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>