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
