Skip site navigation (1) Skip section navigation (2)

BUG #7495: chosen wrong index

From: psql(at)elbrief(dot)de
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #7495: chosen wrong index
Date: 2012-08-15 13:52:29
Message-ID: E1T1e1N-0004mk-2z@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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




Responses

pgsql-bugs by date

Next:From: Andres FreundDate: 2012-08-15 14:09:54
Subject: Re: BUG #7494: WAL replay speed depends heavily on the shared_buffers size
Previous:From: Dave PageDate: 2012-08-15 13:43:36
Subject: Re: BUG #7485: 9.2 beta3 libxml2 can't be loaded on Windows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group