From: Markus Schaber <schabi@logix-tt.com>
To: pgsql-performance@postgresql.org
Subject: Re: Index Being Ignored?
Date: Fri, 30 Jun 2006 16:29:06 +0200
Message-ID: <44A53532.50402@logix-tt.com> (raw)
In-Reply-To: <D2575270-BD66-4654-A9E7-11A4207A00D2@sweetwater.com>
References: <D2575270-BD66-4654-A9E7-11A4207A00D2@sweetwater.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
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: schabi@logix-tt.com
Subject: Re: Index Being Ignored?
In-Reply-To: <44A53532.50402@logix-tt.com>
* 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