Re: Bitmap Heap scan 8.1/8.2 - Mailing list pgsql-general

Tomas Vondra wrote:
>
>> 2007/10/22, Martin Marques <martin@bugs.unl.edu.ar>:
>>> 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
---------------------------------------------------------