Thread: BUG #10144: PostgreSQL cost_index in costsize.c bug? (cann't estimate indexCorrelation correct)

The following bug has been logged on the website:

Bug reference:      10144
Logged by:          digoal.zhou
Email address:      digoal@126.com
PostgreSQL version: 9.3.4
Operating system:   CentOS 6.5 x64
Description:

i see postgresql optimizer don't add column's indexCorrelation in index scan
and then shold not choose optimal scannode.
when data insert random more, the effect more.
this is my test :

digoal=> create table test_indexscan(id int, info text);
CREATE TABLE
digoal=> insert into test_indexscan select
(random()*5000000)::int,md5(random()::text) from generate_series(1,100000);
INSERT 0 100000
digoal=> create index idx_test_indexscan_id on test_indexscan (id);
CREATE INDEX
digoal=> select correlation from pg_stats where tablename='test_indexscan'
and attname='id';
 correlation
-------------
  0.00986802
(1 row)
digoal=> select ctid,id from test_indexscan limit 10;
  ctid  |   id
--------+---------
 (0,1)  | 4217216
 (0,2)  | 2127868
 (0,3)  | 2072952
 (0,4)  |   62641
 (0,5)  | 4927312
 (0,6)  | 3000894
 (0,7)  | 2799439
 (0,8)  | 4165217
 (0,9)  | 2446438
 (0,10) | 2835211
(10 rows)
digoal=> select id,ctid from test_indexscan order by id limit 10;
 id  |   ctid
-----+-----------
  56 | (192,318)
  73 | (119,163)
 218 | (189,2)
 235 | (7,209)
 260 | (41,427)
 340 | (37,371)
 548 | (118,363)
 607 | (143,174)
 690 | (161,38)
 714 | (1,21)
(10 rows)
digoal=> select relpages from pg_class where relname='test_indexscan';
 relpages
----------
      208
(1 row)

digoal=> select relpages from pg_class where
relname='idx_test_indexscan_id';
 relpages
----------
       86
(1 row)

when use index scan, the heap page scaned is so large because the id data
random inserted.
but index scan's total_cost it's so small? and small than bitmap scan .


digoal=> explain (analyze,verbose,costs,buffers,timing) select * from
test_indexscan where id>90000;

QUERY PLAN


------------------------------------------------------------------------------------------------------------------------------------
----------------------
 Index Scan using idx_test_indexscan_id on digoal.test_indexscan
(cost=0.29..2035.38 rows=99719 width=37) (actual time=0.027..87.45
6 rows=98229 loops=1)
   Output: id, info
   Index Cond: (test_indexscan.id > 90000)
   Buffers: shared hit=97837
 Total runtime: 97.370 ms
(5 rows)

digoal=> select count(*) from test_indexscan where id>90000;
 count
-------
 98229
(1 row)

use index scan in this case will scan 97837 pages approach to count(*) in
this case.

when i use bitmap scan, the scaned pages small, because bitmap scan sort the
ctid first and then fetch tuples.
i think bitmapscan's total_cost is correct. but index scan's cost is wrong
because it's not compute indexCorrelation effective in it.

digoal=> set enable_indexscan=off;
SET
digoal=> explain (analyze,verbose,costs,buffers,timing) select * from
test_indexscan where id>90000;
                                                                QUERY PLAN



------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on digoal.test_indexscan  (cost=846.77..2282.96 rows=98255
width=37) (actual time=15.291..35.911 rows=98229 loops=
1)
   Output: id, info
   Recheck Cond: (test_indexscan.id > 90000)
   Buffers: shared hit=292
   ->  Bitmap Index Scan on idx_test_indexscan_id  (cost=0.00..822.21
rows=98255 width=0) (actual time=15.202..15.202 rows=98229 loo
ps=1)
         Index Cond: (test_indexscan.id > 90000)
         Buffers: shared hit=84
 Total runtime: 45.838 ms
(8 rows)

digoal@126.com writes:
> i see postgresql optimizer don't add column's indexCorrelation in index scan
> and then shold not choose optimal scannode.

When I try this test case, I find that the planner prefers a plain
seqscan, then a bitmap scan, and last an indexscan; and the cost estimates
are not too out of line with reality.  I suspect you've changed the
planner's cost parameters to some non-default settings that don't really
square very well with your environment.

            regards, tom lane