Slow Query- Simple taking

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

Responses

Browse pgsql-performance by date

  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)