Martin Marques (m...@bugs.unl.edu.ar)

Alvaro Herrera wrote:
>
> It's an arbitrary number, based on which all the other numbers are
> measured.

Now that I read more intensively he docs I see that all the cost
parameters are related one with the other.

> What people generally do around here is mess with random_page_cost, and
> leave seq_page_cost alone.  Often, it's the ratio
> seq_page_cost/random_page_cost what's most important to the cost
> equations results. (seq_page_cost wasn't tunable at all until
> recently, say 8.1 or 8.2 AFAIR).

Ok, now the 8.1 server has a RAID1 hardware board with SCSI disks, and
the 8.2 is just a PentiumD with SATA disks (it's my desktop PC where I
do tests). Should I have a lower random_page_cost on a machine that is
likely to have a lower disk IO speed?

Can someone explain how this parameters are measured? What is 5.0 in
this case for seq_page_cost?

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

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

8.1:

  16000


8.2:

  400MB


> work_mem

8.1:

  8192

8.2:

  4MB


> effective_cache_size

8.1:

  1000

8.2:

  128MB