Hello I am unsure, did you check config values? Pavel 2007/10/22, Martin Marques <martin@xxxxxxxxxxxxxxx>:> 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> ---------------------------(end of broadcast)---------------------------TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
