VACUUM ANALYZE slows down query

Certain queries on my database get slower after
running a VACUUM ANALYZE. Why would this happen, and
how can I fix it?

I am running PostgreSQL 7.4.2 (I also seen this
problem on v. 7.3 and 8.0)

Here is a sample query that exhibits this behaviour
(here the query goes from 1 second before VACUUM
ANALYZE to 2 seconds after; there are other queries
that go from 20 seconds before to 800 seconds after):

==================================================

select ToolRepairRequest.RequestID, (Select
count(ToolHistory.HistoryID) from ToolHistory where
ToolRepairRequest.RepairID=ToolHistory.RepairID) as
CountOfTH
from ((ToolRepairRequest
LEFT JOIN (ToolRepair
LEFT JOIN ToolHistory on (ToolRepair.RepairID =
ToolHistory.RepairID)) on (ToolRepairRequest.RepairID
= ToolRepair.RepairID))
LEFT JOIN ServiceOrder ON
(ToolRepairRequest.ServiceOrderID =
ServiceOrder.ServiceOrderID))
LEFT JOIN Tool ON (ToolRepairRequest.ToolID = Tool.ID)
where (ToolRepairRequest.StationID = 1303)

==================================================

Here are the EXPLAIN ANALYZE results:

Before VACUUM ANALYZE:

==================================================

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=3974.74..48055.42
rows=79 width=8) (actual time=359.751..1136.165
rows=1518 loops=1)
-> Nested Loop Left Join (cost=3974.74..6175.84
rows=78 width=12) (actual time=359.537..1023.404
rows=1518 loops=1)
-> Merge Right Join (cost=3974.74..5705.83
rows=78 width=16) (actual time=359.516..991.826
rows=1518 loops=1)
Merge Cond: ("outer".repairid =
"inner".repairid)
-> Merge Left Join
(cost=3289.68..4949.83 rows=27907 width=4) (actual
time=302.058..840.706 rows=28000 loops=1)
Merge Cond: ("outer".repairid =
"inner".repairid)
-> Index Scan using
toolrepair_pkey on toolrepair (cost=0.00..1175.34
rows=26485 width=4) (actual time=0.063..130.516
rows=26485 loops=1)
-> Sort (cost=3289.68..3359.44
rows=27906 width=4) (actual time=301.965..402.228
rows=27906 loops=1)
Sort Key:
toolhistory.repairid
-> Seq Scan on toolhistory
(cost=0.00..1229.06 rows=27906 width=4) (actual
time=0.009..116.441 rows=27906 loops=1)
-> Sort (cost=685.06..685.24 rows=74
width=16) (actual time=26.490..36.454 rows=1518
loops=1)
Sort Key:
toolrepairrequest.repairid
-> Seq Scan on toolrepairrequest
(cost=0.00..682.76 rows=74 width=16) (actual
time=0.039..20.506 rows=1462 loops=1)
Filter: (stationid = 1303)
-> Index Scan using serviceorder_pkey on
serviceorder (cost=0.00..6.01 rows=1 width=4) (actual
time=0.008..0.009 rows=0 loops=1518)
Index Cond: ("outer".serviceorderid =
serviceorder.serviceorderid)
-> Index Scan using tool_pkey on tool
(cost=0.00..6.01 rows=1 width=4) (actual
time=0.013..0.018 rows=1 loops=1518)
Index Cond: ("outer".toolid = tool.id)
SubPlan
-> Aggregate (cost=524.17..524.17 rows=1
width=4) (actual time=0.032..0.035 rows=1 loops=1518)
-> Index Scan using th_repair_key on
toolhistory (cost=0.00..523.82 rows=140 width=4)
(actual time=0.013..0.018 rows=1 loops=1518)
Index Cond: ($0 = repairid)
Total runtime: 1147.350 ms
(23 rows)

==================================================

and after VACUUM ANALYZE:

==================================================

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=18310.59..29162.44 rows=1533
width=8) (actual time=1886.942..2183.774 rows=1518
loops=1)
Merge Cond: ("outer".toolid = "inner".id)
-> Sort (cost=15110.46..15114.29 rows=1532
width=12) (actual time=1534.319..1539.461 rows=1518
loops=1)
Sort Key: toolrepairrequest.toolid
-> Nested Loop Left Join
(cost=4050.79..15029.41 rows=1532 width=12) (actual
time=410.948..1527.360 rows=1518 loops=1)
-> Merge Right Join
(cost=4050.79..5800.48 rows=1532 width=16) (actual
time=410.926..1488.229 rows=1518 loops=1)
Merge Cond: ("outer".repairid =
"inner".repairid)
-> Merge Left Join
(cost=3289.68..4946.79 rows=27907 width=4) (actual
time=355.606..1321.320 rows=28000 loops=1)
Merge Cond:
("outer".repairid = "inner".repairid)
-> Index Scan using
toolrepair_pkey on toolrepair (cost=0.00..1172.67
rows=26485 width=4) (actual time=0.108..235.096
rows=26485 loops=1)
-> Sort
(cost=3289.68..3359.44 rows=27906 width=4) (actual
time=355.460..519.987 rows=27906 loops=1)
Sort Key:
toolhistory.repairid
-> Seq Scan on
toolhistory (cost=0.00..1229.06 rows=27906 width=4)
(actual time=0.016..129.811 rows=27906 loops=1)
-> Sort (cost=761.11..764.83
rows=1487 width=16) (actual time=30.447..35.695
rows=1518 loops=1)
Sort Key:
toolrepairrequest.repairid
-> Seq Scan on
toolrepairrequest (cost=0.00..682.76 rows=1487
width=16) (actual time=0.039..23.852 rows=1462
loops=1)
Filter: (stationid =
1303)
-> Index Scan using serviceorder_pkey
on serviceorder (cost=0.00..6.01 rows=1 width=4)
(actual time=0.009..0.010 rows=0 loops=1518)
Index Cond:
("outer".serviceorderid = serviceorder.serviceorderid)
-> Sort (cost=3200.13..3267.24 rows=26844
width=4) (actual time=352.324..453.352 rows=24746
loops=1)
Sort Key: tool.id
-> Seq Scan on tool (cost=0.00..1225.44
rows=26844 width=4) (actual time=0.024..126.826
rows=26844 loops=1)
SubPlan
-> Aggregate (cost=6.98..6.98 rows=1 width=4)
(actual time=0.038..0.042 rows=1 loops=1518)
-> Index Scan using th_repair_key on
toolhistory (cost=0.00..6.97 rows=2 width=4) (actual
time=0.016..0.021 rows=1 loops=1518)
Index Cond: ($0 = repairid)
Total runtime: 2191.401 ms
(27 rows)

==================================================

Thanks for any assistance.

Walt

__________________________________
Do you Yahoo!?
Meet the all-new My Yahoo! - Try it today!
http://my.yahoo.com