Re: [HACKERS] Use of Indicies ...

On Mon, 24 Jan 2000, Tom Lane wrote:

> The Hermit Hacker <scrappy@hub.org> writes:
> > There is an index on all three conditions in the WHERE clause:
> > Yet EXPLAIN shows:
> > Aggregate  (cost=2.05 rows=1 width=4)
> -> Index Scan using referrer_link_counter_id on referrer_link  (cost=2.05 rows=1 width=4)
> 
> > Why does EXPLAIN only show the use of one of the indices, why counter_id
> > and why not all three?
> 
> Indexscans only know how to use one index at a time.
> 
> The optimizer picked the counter_id index out of the three available
> choices because it thought that would be the cheapest (most selective)
> alternative --- or, if the computed selectivities were all the same,
> just because it happened to try that one first.
> 
> Do you have reason to think that one of the other indexes would have
> been cheaper?

Nope, just looked weird that not all three were used, that's all
... someone else responded to me on this with a similar response
... basically, that it didn't make sense to use all three indices since it
there would be no 'index' on the result of the first condition ...

Ie. if 'counter_id = ?' returned 4 tuples out of 40000, why look at those
40000 again for 'referrer_url = ?', when you already know that only 4
match the first condition ...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org