BUG #9552: IndexOnlyScan startup_cost>0, why not equal 0?it's a bug?

Hello,

I belive index only scan can return tuple direct, it's not need to scan
heappage, why it's startup_cost equal to index scan?
I'ts a bug?

No. It is a result of reasonable calculus.

Index Scan using t11_pkey on t11 (cost=0.29..4.31 rows=1 width=9)

Index scan runs through several disk (but usually on cache)
blocks during descending down to the leaf node in the index tree
and does comparisons for certain amount of index entries(tuples)
in each index node with regard to btree. The figure made from the
expected number of the comparisons multiplied by
cpu_operator_cost. Each access methods (index types) has their
own function to do that.

=# select amname, amcostestimate from pg_am where amname = 'btree';
amname | amcostestimate
--------+----------------
btree | btcostestimate

The function 'btcostestimate' in selfuncs.c calculates it for
btree index. You can see the details there if you wish.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

digoal@126.com writes:

I belive index only scan can return tuple direct, it's not need to scan
heappage, why it's startup_cost equal to index scan?
I'ts a bug?

No. Your test case fails to vacuum t11, so although the planner selects
an index-only scan, it's still predicting that all the tuples will require
heap visits to confirm visibility.

The startup cost would not change in any case, since that's about index
descent costs not heap visit costs.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

HI,
Thanks, but IndexOnlyScan's startup_cost not contain VM scan cost?

digoal=# create table t11(id int primary key, info text);
CREATE TABLE
digoal=# insert into t11 select generate_series(1,100000),'test';
INSERT 0 100000
digoal=# select reltuples from pg_class where relname='t11_pkey';
reltuples
-----------
100000
(1 row)
digoal=# show cpu_operator_cost;
cpu_operator_cost
-------------------
0.0025
(1 row)
digoal=# select (log(100000)/log(2.0))*0.0025;
?column?
-------------------
0.041524101186092
(1 row)
digoal=# create extension pageinspect;
CREATE EXTENSION
digoal=# SELECT * FROM bt_metap('t11_pkey');
magic | version | root | level | fastroot | fastlevel
--------+---------+------+-------+----------+-----------
340322 | 2 | 3 | 1 | 3 | 1
(1 row)
digoal=# select (1+1)*50*0.0025;
?column?
----------
0.2500
(1 row)
digoal=# select 0.041524101186092+0.2500;
?column?
-------------------
0.291524101186092
(1 row)

##########################
We can see index scan and index only scan startup_cost is same below, indexonlyscan not contain vm scan cost?
And index only scan real slower than index scan.

digoal=# explain select * from t11 where id=1;
QUERY PLAN
--------------------------------------------------------------------
Index Scan using t11_pkey on t11 (cost=0.29..4.31 rows=1 width=9) --
Index Cond: (id = 1)
(2 rows)
digoal=# explain select id from t11 where id=1;
QUERY PLAN
-------------------------------------------------------------------------
Index Only Scan using t11_pkey on t11 (cost=0.29..4.31 rows=1 width=4) --
Index Cond: (id = 1)
(2 rows)

digoal=# explain (analyze,verbose,costs,buffers,timing) select id from t11 where id<10000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using t11_pkey on public.t11 (cost=0.29..204.20 rows=9995 width=4) (actual time=0.088..3.623 rows=9999 loops=1)
Output: id
Index Cond: (t11.id < 10000)
Heap Fetches: 9999
Buffers: shared hit=22
Total runtime: 4.748 ms
(6 rows)

digoal=# vacuum ANALYZE t11;
VACUUM
digoal=# explain (analyze,verbose,costs,buffers,timing) select id from t11 where id<10000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using t11_pkey on public.t11 (cost=0.29..191.64 rows=10020 width=4) (actual time=0.088..2.597 rows=9999 loops=1)
Output: id
Index Cond: (t11.id < 10000)
Heap Fetches: 0
Buffers: shared hit=9
Total runtime: 3.634 ms
(6 rows)

------------------------------------------------------------
公益是一辈子的事, I'm Digoal , Just Do it！
德哥(Digoal.Zhou)
数据库技术经理
***********************************************
杭州斯凯网络科技有限公司
杭州市紫荆花路2号联合大厦B座11层
邮编: 310013
手机: +86 13484021953
座机: +86 571 89710948
QQ: 276732431
email: digoal.zhou@mopo.com
MSN: zzzqware@hotmail.com
Blog: http://blog.163.com/digoal@126/
Github: https://github.com/digoal

From: Tom Lane
Date: 2014-03-13 21:56
To: digoal
CC: pgsql-bugs
Subject: Re: [BUGS] BUG #9552: IndexOnlyScan startup_cost>0, why not equal 0?it's a bug?
digoal@126.com writes:

I belive index only scan can return tuple direct, it's not need to scan
heappage, why it's startup_cost equal to index scan?
I'ts a bug?

No. Your test case fails to vacuum t11, so although the planner selects
an index-only scan, it's still predicting that all the tuples will require
heap visits to confirm visibility.

The startup cost would not change in any case, since that's about index
descent costs not heap visit costs.

regards, tom lane