Pam Ozer
Обсуждение: Slow Query- Simple taking
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.
"Ozer, Pam" <pozer@automotive.com> wrote: > 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 The EXPLAIN ANALYZE doesn't have to return 116412 rows to the client. It doesn't seem too out of line to me that it takes two seconds to do that. > Can someone tell me why after it runs the index scan it hen runs a > bitmap heap scan? Without visiting the heap it can't tell whether the tuples it has found are visible to your query. Also, it needs to get the actual values out of the heap. > It should not take this long to run should it? If you want an answer to that, we need more information. See this page for ideas: http://wiki.postgresql.org/wiki/SlowQueryQuestions > If I limit the results it comes back in 300ms. I don't find that surprising. Wouldn't you think that reading and transmitting more rows would take more time? -Kevin
On Tue, Oct 19, 2010 at 8:21 PM, Ozer, Pam <pozer@automotive.com> wrote: > 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 > "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? Hi, As far as I understand, the bitmap index scan only marks which pages contain rows matching the conditions. The bitmap heap scan will read these marked pages sequentially and recheck the condition as some pages will contain more data than requested. Pgsql will use a 'nomal' index scan if it believes that there's no added value in reading it sequentially instead of according to the index. In this case the planner is expecting a lot of matches, so it makes sense that it will optimize for I/O throughput. I'm wondering why you need to run a query that returns that many rows though. Kind regards, Mathieu
On mysql the same query only takes milliseconds not seconds. That's a big difference. -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: Tuesday, October 19, 2010 1:59 PM To: Ozer, Pam; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow Query- Simple taking "Ozer, Pam" <pozer@automotive.com> wrote: > 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 The EXPLAIN ANALYZE doesn't have to return 116412 rows to the client. It doesn't seem too out of line to me that it takes two seconds to do that. > Can someone tell me why after it runs the index scan it hen runs a > bitmap heap scan? Without visiting the heap it can't tell whether the tuples it has found are visible to your query. Also, it needs to get the actual values out of the heap. > It should not take this long to run should it? If you want an answer to that, we need more information. See this page for ideas: http://wiki.postgresql.org/wiki/SlowQueryQuestions > If I limit the results it comes back in 300ms. I don't find that surprising. Wouldn't you think that reading and transmitting more rows would take more time? -Kevin
On Tue, Oct 19, 2010 at 2:21 PM, Ozer, Pam <pozer@automotive.com> wrote: > 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. It doesn't. The EXPLAIN output shows it running the bitmap index scan first and then bitmap heap scan. The bitmap index scan is taking 22 ms, and the bitmap index and bitmap heap scans combined are taking 402 ms. The sort is then taking another 800+ ms for a total of 1325 ms. Any additional time is spent returning rows to the client. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Thu, Oct 28, 2010 at 10:39 AM, Robert Haas <robertmhaas@gmail.com> wrote: >> 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. > > It doesn't. The EXPLAIN output shows it running the bitmap index scan > first and then bitmap heap scan. The bitmap index scan is taking 22 > ms, and the bitmap index and bitmap heap scans combined are taking 402 > ms. The sort is then taking another 800+ ms for a total of 1325 ms. > Any additional time is spent returning rows to the client. Doh! I misread your email. You had it right, and I'm all wet. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Oct 19, 2010 at 6:05 PM, Ozer, Pam <pozer@automotive.com> wrote: > On mysql the same query only takes milliseconds not seconds. That's a > big difference. I can believe that MySQL is faster, because they probably don't need to do the bitmap heap scan. There is a much-anticipated feature called index-only scans that we don't have yet in PG, which would help cases like this a great deal. But I don't see how MySQL could send back 116,000 rows to the client in milliseconds, or sort them that quickly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 10/28/2010 10:42 AM, Robert Haas wrote: > I can believe that MySQL is faster, because they probably don't need > to do the bitmap heap scan. There is a much-anticipated feature > called index-only scans that we don't have yet in PG, which would help > cases like this a great deal. Yyesss! Any time frame on that? Can you make it into 9.0.2? -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
On Thu, Oct 28, 2010 at 7:51 AM, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote: > Yyesss! Any time frame on that? Can you make it into 9.0.2? Maybe 9.1.0 or 9.2.0 :) 9.0's features are already frozen. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On 10/28/2010 10:53 AM, Richard Broersma wrote: > On Thu, Oct 28, 2010 at 7:51 AM, Mladen Gogala > <mladen.gogala@vmsinfo.com> wrote: > >> Yyesss! Any time frame on that? Can you make it into 9.0.2? > Maybe 9.1.0 or 9.2.0 :) 9.0's features are already frozen. > > Well, with all this global warming around us, index scans may still thaw in time to make it into 9.0.2 -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
On Thu, Oct 28, 2010 at 11:23 AM, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote: > On 10/28/2010 10:53 AM, Richard Broersma wrote: >> >> On Thu, Oct 28, 2010 at 7:51 AM, Mladen Gogala >> <mladen.gogala@vmsinfo.com> wrote: >> >>> Yyesss! Any time frame on that? Can you make it into 9.0.2? >> >> Maybe 9.1.0 or 9.2.0 :) 9.0's features are already frozen. >> >> > Well, with all this global warming around us, index scans may still thaw in > time to make it into 9.0.2 I fear this is not going to happen for 9.1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company