Bitmap Heap scan 8.1/8.2

From: Martin Marques <martin(at)bugs(dot)unl(dot)edu(dot)ar>
To: pgsql-general(at)postgresql(dot)org
Subject: Bitmap Heap scan 8.1/8.2
Date: 2007-10-22 17:22:00
Message-ID: 471CDC38.7060504@bugs.unl.edu.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
---------------------------------------------------------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Benjamin Weaver 2007-10-22 18:00:38 unicode searches failing that use % and LIKE operators
Previous Message Guy Rouillier 2007-10-22 17:21:30 Re: How install postgreSQL in another drive than C:?