explain doubt

Started by Luca Ferrarialmost 14 years ago3 messagesgeneral
Jump to latest
#1Luca Ferrari
fluca1978@infinito.it

Hi all,
imagine the following simple situation:

# CREATE TABLE test( pk serial not null, description text, primary key(pk));
# INSERT INTO test(pk) VALUES(generate_series(1,1000000 ) );
# VACUUM FULL ANALYZE test;
# EXPLAIN SELECT * FROM test WHERE pk = 1 OR pk = 100;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=8.69..16.59 rows=2 width=36)
Recheck Cond: ((pk = 1) OR (pk = 100))
-> BitmapOr (cost=8.69..8.69 rows=2 width=0)
-> Bitmap Index Scan on test_pkey (cost=0.00..4.35 rows=1
width=0)
Index Cond: (pk = 1)
-> Bitmap Index Scan on test_pkey (cost=0.00..4.35 rows=1
width=0)
Index Cond: (pk = 100)

Now, what is the .35 in the cost of the bitmap index scan nodes? I
mean it seems that the system has to walk 23 index tuples on each
index page but I'm not sure about this, does it mean that this is the
tree high? Since the value is the same for both the first and a
"middle" key I suspect it is an average count, but on what?

Thanks

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Luca Ferrari (#1)
Re: explain doubt

Luca Ferrari <fluca1978@infinito.it> writes:

Now, what is the .35 in the cost of the bitmap index scan nodes?

If you're going to get picky about second-order cost components,
it's time to start reading the source code:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/optimizer/path/costsize.c;h=480c1b7425ce66a02e2e10711359143ea7ffe59c;hb=HEAD#l218
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/selfuncs.c;h=95e46276f0a8911758f4ec02b993193bf55eee15;hb=HEAD#l6087

The short answer though is that this is probably coming from CPU cost
components not disk-access components.

regards, tom lane

#3Luca Ferrari
fluca1978@infinito.it
In reply to: Tom Lane (#2)
Re: explain doubt

On Mon, Jun 25, 2012 at 4:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The short answer though is that this is probably coming from CPU cost
components not disk-access components.

Yes of course they are cpu costs, but I'm not able to understand which
ones. Is there a way to make PostgreSQL to log the values of the
single parts of the cost computation (e.g., minIOCost, maxIOCost,
...)?

Luca