Re: Bitmap Heap scan 8.1/8.2

Hello

I am unsure, did you check config values?

Pavel

2007/10/22, Martin Marques <martin(at)bugs(dot)unl(dot)edu(dot)ar>:
> I have to PG servers, one ver. 8.1.9 and the other 8.2.4.
>
> I was checking a query out and found that with the exact same DB (same
> data in it) and the same query I get different plans, and significantly
> higher time in 8.2:
>
> On 8.1 I get:
>
> test=> explain analyze SELECT * FROM prestamos WHERE biblioteca = 19 AND
> vencimiento < now() AND NOT devuelto ORDER BY vencimiento DESC;
>
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=2793.74..2795.82 rows=832 width=20) (actual
> time=25.795..25.832 rows=49 loops=1)
> Sort Key: vencimiento
> -> Bitmap Heap Scan on prestamos (cost=850.43..2753.39 rows=832
> width=20) (actual time=20.747..25.529 rows=49 loops=1)
> Filter: ((biblioteca = 19) AND (vencimiento < now()) AND (NOT
> devuelto))
> -> Bitmap Index Scan on prestamos_objetos_devuelto_idx
> (cost=0.00..850.43 rows=2200 width=0) (actual time=20.265..20.265
> rows=2301 loops=1)
> Index Cond: (devuelto = false)
> Total runtime: 25.971 ms
> (7 filas)
>
> On 8.2:
>
> test=> explain analyze SELECT * FROM prestamos WHERE biblioteca = 19 AND
> vencimiento < now() AND NOT devuelto ORDER BY vencimiento DESC;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------
> Sort (cost=4100.77..4102.77 rows=800 width=20) (actual
> time=95.082..95.103 rows=49 loops=1)
> Sort Key: vencimiento
> -> Seq Scan on prestamos (cost=0.00..4062.20 rows=800 width=20)
> (actual time=7.293..82.778 rows=49 loops=1)
> Filter: ((biblioteca = 19) AND (vencimiento < now()) AND (NOT
> devuelto))
> Total runtime: 95.165 ms
> (5 filas)
>
> It's like it's ignoring the HEAP scan. Why? It's obviously (unless I'm
> totally wrong) faster in this case.
>
> --
> 21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
> ---------------------------------------------------------
> Lic. Martín Marqués | SELECT 'mmarques' ||
> Centro de Telemática | '@' || 'unl.edu.ar';
> Universidad Nacional | DBA, Programador,
> del Litoral | Administrador
> ---------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>