Pavel Stehule wrote:
2007/10/22, Martin Marques <martin@xxxxxxxxxxxxxxx>:
Pavel Stehule wrote:
2007/10/22, Martin Marques <martin@xxxxxxxxxxxxxxx>:
Pavel Stehule wrote:
Hello
I am unsure, did you check config values?
Don't know which ones you are talking about, but all enable_* are set to on.
Anything else?
shared_buffers
8.1:
16000
~ 128M
8.2:
400MB
work_mem
8.1:
8192
8M !!!! 8>4
8.2:
4MB
effective_cache_size
8.1:
1000
8M
8.2:
128MB
try
set work_mem to '8MB';
and
explain analyze select ..
These things didn't help. What changed the plan completely was this:
seq_page_cost = 5.0 # measured on an arbitrary scale
cpu_tuple_cost = 0.05 # same scale as above
Specially the first one. Now I get this:
explain analyze SELECT usuarios,nticket,objeto,vencimiento FROM
prestamos WHERE biblioteca = 19 AND vencimiento < now() AND NOT
devuelto ORDER BY vencimiento DESC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=7058.86..7060.86 rows=800 width=20) (actual
time=22.850..22.888 rows=95 loops=1)
Sort Key: vencimiento
-> Index Scan using prestamos_objetos_devuelto_idx on prestamos
(cost=0.00..7020.28 rows=800 width=20) (actual time=0.346..22.590
rows=95 loops=1)
Index Cond: (devuelto = false)
Filter: ((biblioteca = 19) AND (vencimiento < now()) AND (NOT
devuelto))
Total runtime: 22.973 ms
--
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 2: Don't 'kill -9' the postmaster