Re: [PERFORM] Slow Query- Simple taking
On Tue, Oct 19, 2010 at 2:21 PM, Ozer, Pam po...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow Query- Simple taking
On Thu, Oct 28, 2010 at 10:39 AM, Robert Haas robertmh...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow Query- Simple taking
On Tue, Oct 19, 2010 at 6:05 PM, Ozer, Pam po...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow Query- Simple taking
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow Query- Simple taking
On Thu, Oct 28, 2010 at 7:51 AM, Mladen Gogala mladen.gog...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow Query- Simple taking
On 10/28/2010 10:53 AM, Richard Broersma wrote: On Thu, Oct 28, 2010 at 7:51 AM, Mladen Gogala mladen.gog...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow Query- Simple taking
On Thu, Oct 28, 2010 at 11:23 AM, Mladen Gogala mladen.gog...@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.gog...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 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. Pam Ozer
Re: [PERFORM] Slow Query- Simple taking
Ozer, Pam po...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow Query- Simple taking
On Tue, Oct 19, 2010 at 8:21 PM, Ozer, Pam po...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow Query- Simple taking
On mysql the same query only takes milliseconds not seconds. That's a big difference. -Original Message- From: Kevin Grittner [mailto:kevin.gritt...@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 po...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
