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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
