From: | "Ozer, Pam" <pozer(at)automotive(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Slow Query- Simple taking |
Date: | 2010-10-19 18:21:02 |
Message-ID: | 216FFB77CBFAEE4B8EE4DF0A939FF1D1018317@mail-001.corp.automotive.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
Pam Ozer
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2010-10-19 20:58:58 | Re: Slow Query- Simple taking |
Previous Message | Merlin Moncure | 2010-10-19 17:41:04 | Re: odd postgresql performance (excessive lseek) |