Bitmap Heap scan 8.1/8.2
От | Martin Marques |
---|---|
Тема | Bitmap Heap scan 8.1/8.2 |
Дата | |
Msg-id | 471CDC38.7060504@bugs.unl.edu.ar обсуждение исходный текст |
Ответы |
Re: Bitmap Heap scan 8.1/8.2
|
Список | 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 ---------------------------------------------------------
В списке pgsql-general по дате отправления: