Bitmap Heap scan 8.1/8.2

Hello

I am unsure, did you check config values?

Pavel

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?

--
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
---------------------------------------------------------

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

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.

Another thing you might try is setting the cost values to the same
values in both databases - it might help.

And what does it mean by 'same data' - have you vacuumed / analyzed both
of them? What does this return:

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.

Tomas

Pavel Stehule 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

8.1:

16000

8.2:

400MB

work_mem

8.1:

8192

8.2:

4MB

effective_cache_size

8.1:

1000

8.2:

128MB

--
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
---------------------------------------------------------

2007/10/22, Martin Marques <martin@bugs.unl.edu.ar>:

Pavel Stehule 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

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 ..

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
---------------------------------------------------------

Pavel Stehule wrote:

2007/10/22, Martin Marques <martin@bugs.unl.edu.ar>:

Pavel Stehule 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

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
---------------------------------------------------------

Martin Marques escribió:

Pavel Stehule wrote:

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

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

2007/10/23, Martin Marques <martin@bugs.unl.edu.ar>:

Martin Marques escribió:

Pavel Stehule wrote:

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

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

http://www.postgresql.org/docs/8.2/interactive/runtime-config-query.html

5.0 means so seq scan will be expensive for optimaliser, and
optimaliser will prefer index scan.

Martin Marques escribi�:

Martin Marques escribi�:

Pavel Stehule wrote:

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

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

It's an arbitrary number, based on which all the other numbers are
measured.

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).

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:

Martin Marques escribi�:

seq_page_cost = 5.0 # measured on an arbitrary scale

What people generally do around here is mess with random_page_cost, and
leave seq_page_cost alone.

It's also worth pointing out that having seq_page_cost >
random_page_cost is simply not sane, and is very likely to result in
performance problems in other queries.

regards, tom lane

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?

--
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
---------------------------------------------------------

Martin Marques <martin@bugs.unl.edu.ar> writes:

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?

I'd guess the other way round: cheaper disks are likely to have worse
seek speeds, which translates to random accesses being proportionally
slower.

regards, tom lane