Re: [HACKERS] Index Puzzle for you

Kristofer Munn <kmunn@munn.com> writes:
> [ why does the second example not use an index? ]

> mail=> explain select 1 from tblissuearticle where ixissue = 7 
>     and ixarticle = 9;

> Index Scan using tblissuearticle_idx1 on tblissuearticle  
>     (cost=228.04 rows=1 width=0)

> mail=> explain select 1 from tblissuearticle where ixissue = 7;

> Seq Scan on tblissuearticle  (cost=4076.63 rows=76338 width=0)

The thing that jumps out at me from this example is the much larger
estimate of returned rows in the second case.  The planner is clearly
estimating that "ixissue = 7" alone is not very selective.  That might
or might not be reasonable (how many rows are in the table, and what's
the actual distribution of ixissue values?), but if it is reasonable
then a sequential scan might indeed be the right choice.  Index scans
are not always better than sequential scans --- the planner's job would
be far simpler if they were ;-)
        regards, tom lane