BUG #7495: chosen wrong index

The following bug has been logged on the website:

Bug reference: 7495
Logged by: Andreas
Email address: psql(at)elbrief(dot)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=0.00..0.27 rows=10 width=8) (actual time=0.150..0.173 rows=10
loops=1)
-> Index Scan using bla_b on bla (cost=0.00..265.29 rows=10000 width=8)
(actual time=0.147..0.159 rows=10 loops=1)
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=0.00..26.32 rows=10 width=8) (actual time=991.096..991.113
rows=10 loops=1)
-> Index Scan using bla_a on bla (cost=0.00..26322.29 rows=10000
width=8) (actual time=991.093..991.103 rows=10 loops=1)
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=835.76..835.78 rows=10 width=8) (actual time=51.551..51.571
rows=10 loops=1)
-> Sort (cost=835.76..860.76 rows=10001 width=8) (actual
time=51.547..51.548 rows=10 loops=1)
Sort Key: wasnoch.bla.a
Sort Method: top-N heapsort Memory: 17kB
-> HashAggregate (cost=419.62..519.63 rows=10001 width=8) (actual
time=32.061..42.544 rows=10000 loops=1)
-> Append (cost=0.00..369.62 rows=10001 width=8) (actual
time=0.037..19.857 rows=10000 loops=1)
-> Index Scan using bla_b on bla (cost=0.00..265.29
rows=10000 width=8) (actual time=0.035..11.538 rows=10000 loops=1)
Index Cond: (b > 990000)
-> Index Scan using bla_b on bla (cost=0.00..4.31
rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: (b < 0)
Total runtime: 51.678 ms

seq_page_cost = 1.0
random_page_cost = 20.0
restart server

explain analyze select * from bla where b > 997400 order by a limit 10 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Limit (cost=253.37..253.40 rows=10 width=8) (actual time=3.642..3.653
rows=10 loops=1)
-> Sort (cost=253.37..259.87 rows=2600 width=8) (actual
time=3.639..3.643 rows=10 loops=1)
Sort Key: a
Sort Method: top-N heapsort Memory: 17kB
-> Index Scan using bla_b on bla (cost=0.00..197.19 rows=2600
width=8) (actual time=0.041..2.155 rows=2600 loops=1)
Index Cond: (b > 997400)
Total runtime: 3.698 ms

seq_page_cost = 1.0
random_page_cost = 2.0
restart server

explain analyze select * from bla where b > 997400 order by a limit 10 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..101.24 rows=10 width=8) (actual time=726.649..726.667
rows=10 loops=1)
-> Index Scan using bla_a on bla (cost=0.00..26322.29 rows=2600
width=8) (actual time=726.642..726.652 rows=10 loops=1)
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=114.29..114.31 rows=10 width=8) (actual time=4.009..4.020
rows=10 loops=1)
-> Sort (cost=114.29..120.04 rows=2301 width=8) (actual
time=4.007..4.011 rows=10 loops=1)
Sort Key: a
Sort Method: top-N heapsort Memory: 17kB
-> Index Scan using bla_b on bla (cost=0.00..64.56 rows=2301
width=8) (actual time=0.068..2.448 rows=2301 loops=1)
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