Slow Query- Simple taking

Поиск
Список
Период
Сортировка
Искать
От
Ozer, Pam
Тема
Slow Query- Simple taking
Дата
Msg-id
216FFB77CBFAEE4B8EE4DF0A939FF1D1018317@mail-001.corp.automotive.com
Список
Дерево обсуждения
Slow Query- Simple taking "Ozer, Pam" <pozer@automotive.com>
Re: Slow Query- Simple taking "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Re: Slow Query- Simple taking "Ozer, Pam" <pozer@automotive.com>
Re: Slow Query- Simple taking Robert Haas <robertmhaas@gmail.com>
Re: Slow Query- Simple taking Mladen Gogala <mladen.gogala@vmsinfo.com>
Re: Slow Query- Simple taking Richard Broersma <richard.broersma@gmail.com>
Re: Slow Query- Simple taking Mladen Gogala <mladen.gogala@vmsinfo.com>
Re: Slow Query- Simple taking Robert Haas <robertmhaas@gmail.com>
Re: Slow Query- Simple taking Mathieu De Zutter <mathieu@dezutter.org>
Re: Slow Query- Simple taking Robert Haas <robertmhaas@gmail.com>
Re: Slow Query- Simple taking Robert Haas <robertmhaas@gmail.com>

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



В списке pgsql-performance по дате отправления
От: Merlin Moncure
Дата:
От: Kevin Grittner
Дата:
Сообщение: Re: Slow Query- Simple taking
FAQ