BUG #7495: chosen wrong index - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      7495
Logged by:          Andreas
Email address:      psql@elbrief.de
PostgreSQL version: 9.1.4
Operating system:   Debian Linux
Description:        =


Hello.

create table bla ( a int , b int ) ;

insert into bla ( a , b ) select a , a from generate_series( 1 , 1000000 )
as a ( a ) ;

create index bla_a on bla ( a ) ;

create index bla_b on bla ( b ) ;

explain analyze select * from bla where b > 990000 limit 10 ;
                                                       QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------
 Limit  (cost=3D0.00..0.27 rows=3D10 width=3D8) (actual time=3D0.150..0.173=
 rows=3D10
loops=3D1)
   ->  Index Scan using bla_b on bla  (cost=3D0.00..265.29 rows=3D10000 wid=
th=3D8)
(actual time=3D0.147..0.159 rows=3D10 loops=3D1)
         Index Cond: (b > 990000)
 Total runtime: 0.226 ms

 explain analyze select * from bla where b > 990000 order by a limit 10 ;
                                                          QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------
 Limit  (cost=3D0.00..26.32 rows=3D10 width=3D8) (actual time=3D991.096..99=
1.113
rows=3D10 loops=3D1)
   ->  Index Scan using bla_a on bla  (cost=3D0.00..26322.29 rows=3D10000
width=3D8) (actual time=3D991.093..991.103 rows=3D10 loops=3D1)
         Filter: (b > 990000)
 Total runtime: 991.164 ms

explain analyze select * from ( select * from bla where b > 990000 union
select * from bla where b < 0 ) a order by a limit 10 ;
                                                                  QUERY
PLAN
---------------------------------------------------------------------------=
-------------------------------------------------------------------
 Limit  (cost=3D835.76..835.78 rows=3D10 width=3D8) (actual time=3D51.551..=
51.571
rows=3D10 loops=3D1)
   ->  Sort  (cost=3D835.76..860.76 rows=3D10001 width=3D8) (actual
time=3D51.547..51.548 rows=3D10 loops=3D1)
         Sort Key: wasnoch.bla.a
         Sort Method: top-N heapsort  Memory: 17kB
         ->  HashAggregate  (cost=3D419.62..519.63 rows=3D10001 width=3D8) =
(actual
time=3D32.061..42.544 rows=3D10000 loops=3D1)
               ->  Append  (cost=3D0.00..369.62 rows=3D10001 width=3D8) (ac=
tual
time=3D0.037..19.857 rows=3D10000 loops=3D1)
                     ->  Index Scan using bla_b on bla  (cost=3D0.00..265.29
rows=3D10000 width=3D8) (actual time=3D0.035..11.538 rows=3D10000 loops=3D1)
                           Index Cond: (b > 990000)
                     ->  Index Scan using bla_b on bla  (cost=3D0.00..4.31
rows=3D1 width=3D8) (actual time=3D0.012..0.012 rows=3D0 loops=3D1)
                           Index Cond: (b < 0)
 Total runtime: 51.678 ms

seq_page_cost =3D 1.0
random_page_cost =3D 20.0
restart server

explain analyze select * from bla where b > 997400 order by a limit 10 ;
                                                          QUERY PLAN
---------------------------------------------------------------------------=
----------------------------------------------------
 Limit  (cost=3D253.37..253.40 rows=3D10 width=3D8) (actual time=3D3.642..3=
.653
rows=3D10 loops=3D1)
   ->  Sort  (cost=3D253.37..259.87 rows=3D2600 width=3D8) (actual
time=3D3.639..3.643 rows=3D10 loops=3D1)
         Sort Key: a
         Sort Method: top-N heapsort  Memory: 17kB
         ->  Index Scan using bla_b on bla  (cost=3D0.00..197.19 rows=3D2600
width=3D8) (actual time=3D0.041..2.155 rows=3D2600 loops=3D1)
               Index Cond: (b > 997400)
 Total runtime: 3.698 ms

seq_page_cost =3D 1.0
random_page_cost =3D 2.0
restart server

explain analyze select * from bla where b > 997400 order by a limit 10 ;
                                                         QUERY PLAN
---------------------------------------------------------------------------=
--------------------------------------------------
 Limit  (cost=3D0.00..101.24 rows=3D10 width=3D8) (actual time=3D726.649..7=
26.667
rows=3D10 loops=3D1)
   ->  Index Scan using bla_a on bla  (cost=3D0.00..26322.29 rows=3D2600
width=3D8) (actual time=3D726.642..726.652 rows=3D10 loops=3D1)
         Filter: (b > 997400)
 Total runtime: 726.731 ms

explain analyze select * from bla where b > 997699 order by a limit 10 ;
                                                          QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------
 Limit  (cost=3D114.29..114.31 rows=3D10 width=3D8) (actual time=3D4.009..4=
.020
rows=3D10 loops=3D1)
   ->  Sort  (cost=3D114.29..120.04 rows=3D2301 width=3D8) (actual
time=3D4.007..4.011 rows=3D10 loops=3D1)
         Sort Key: a
         Sort Method: top-N heapsort  Memory: 17kB
         ->  Index Scan using bla_b on bla  (cost=3D0.00..64.56 rows=3D2301
width=3D8) (actual time=3D0.068..2.448 rows=3D2301 loops=3D1)
               Index Cond: (b > 997699)
 Total runtime: 4.073 ms

i have also played with cpu_tuple_cost, cpu_index_tuple_cost
and cpu_operator_cost, but there i have not found a setting
which chose index bla_b under b > 996000. but till b > 900000
it is faster to chose bla_b instead of bla_a.

i think the planner estimate the wrong amount of costs.

best regards,
Andreas