VACUUM ANALYZE slows down query - Mailing list pgsql-performance

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