I have the following query running on 8.4, which takes 3516 ms. It is very straight forward. It brings back 116412 records. The explain only takes 1348ms
select VehicleUsed.VehicleUsedId as VehicleUsedId ,
VehicleUsed.VehicleUsedDisplayPriority as VehicleUsedDisplayPriority ,
VehicleUsed.VehicleYear as VehicleYear ,
VehicleUsed.VehicleUsedDisplayPriority as VehicleUsedDisplayPriority ,
VehicleUsed.HasVehicleUsedThumbnail as HasVehicleUsedThumbnail ,
VehicleUsed.HasVehicleUsedPrice as HasVehicleUsedPrice ,
VehicleUsed.VehicleUsedPrice as VehicleUsedPrice ,
VehicleUsed.HasVehicleUsedMileage as HasVehicleUsedMileage ,
VehicleUsed.VehicleUsedMileage as VehicleUsedMileage ,
VehicleUsed.IsCPO as IsCPO , VehicleUsed.IsMTCA as IsMTCA
from VehicleUsed
where ( VehicleUsed.VehicleMakeId = 28 )
order by VehicleUsed.VehicleUsedDisplayPriority , VehicleUsed.VehicleYear desc , VehicleUsed.HasVehicleUsedThumbnail desc , VehicleUsed.HasVehicleUsedPrice desc , VehicleUsed.VehicleUsedPrice , VehicleUsed.HasVehicleUsedMileage desc , VehicleUsed.VehicleUsedMileage ,
VehicleUsed.IsCPO desc , VehicleUsed.IsMTCA desc
The explain is also very straight forward
"Sort (cost=104491.48..105656.24 rows=116476 width=41) (actual time=1288.413..1325.457 rows=116412 loops=1)"
" Sort Key: vehicleuseddisplaypriority, vehicleyear, hasvehicleusedthumbnail, hasvehicleusedprice, vehicleusedprice, hasvehicleusedmileage, vehicleusedmileage, iscpo, ismtca"
" Sort Method: quicksort Memory: 19443kB"
" -> Bitmap Heap Scan on vehicleused (cost=7458.06..65286.42 rows=116476 width=41) (actual time=34.982..402.164 rows=116412 loops=1)"
" Recheck Cond: (vehiclemakeid = 28)"
" -> Bitmap Index Scan on vehicleused_i08 (cost=0.00..7341.59 rows=116476 width=0) (actual time=22.854..22.854 rows=116412 loops=1)"
" Index Cond: (vehiclemakeid = 28)"
"Total runtime: 1348.487 ms"
Can someone tell me why after it runs the index scan it hen runs a bitmap heap scan? It should not take this long to run should it? If I limit the results it comes back in 300ms.
I have recently run a vacuum analyze on the VehicleUsed table.
Any help would be appreciated.