Re: Slow Query- Simple taking

On Tue, Oct 19, 2010 at 8:21 PM, Ozer, Pam <pozer(at)automotive(dot)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