Tomas Vondra 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
work_mem
effective_cache_size
Pavel
Well, the cost_* values might be interesting too. That is
seq_page_cost
random_page_cost
cpu_tuple_cost
cpu_index_tuple_cost
cpu_operator_cost
effective_cache_size
(the first one is available in 8.2 only). My guess is that there are
different values, and the 8.2 overestimates the index scan - which seems
to be incorrect.
Try to disable the seqscan in the 8.2 database (set enable_seqscan =
off), and run the explain analyze again. This time it should choose
different query plan - maybe the index scan as in 8.1.
siprebi-1.4=> SHOW enable_seqscan ;
enable_seqscan
----------------
off
(1 fila)
siprebi-1.4=> 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=4365.26..4367.26 rows=800 width=20) (actual
time=30.736..30.755 rows=49 loops=1)
Sort Key: vencimiento
-> Bitmap Heap Scan on prestamos (cost=2502.69..4326.68 rows=800
width=20) (actual time=28.983..30.644 rows=49 loops=1)
Filter: ((biblioteca = 19) AND (vencimiento < now()) AND (NOT
devuelto))
-> Bitmap Index Scan on prestamos_usuarios_devuelto_idx
(cost=0.00..2502.49 rows=1976 width=0) (actual time=28.874..28.874
rows=2300 loops=1)
Index Cond: (devuelto = false)
Total runtime: 45.725 ms
Here I see that the time has been halved. So this plan is much better.
Another thing you might try is setting the cost values to the same
values in both databases - it might help.
Which ones and how?
And what does it mean by 'same data' - have you vacuumed / analyzed both
of them? What does this return:
dumped the 8.1 DB and restored in the 8.2. Both were vacummed analyze to
have accurate stats.
select relname, relpages, reltuples from pg_class where relname =
'prestamos' or relname='prestamos_objetos_devuelto_idx';
That should return number of tuples / occupied pages in the table and
index.
select relname, relpages, reltuples from pg_class where relname =
'prestamos' or relname='prestamos_objetos_devuelto_idx';
relname | relpages | reltuples
--------------------------------+----------+-----------
prestamos_objetos_devuelto_idx | 373 | 134697
prestamos | 1705 | 134697
--
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 4: Have you searched our list archives?
http://archives.postgresql.org/
