
Always something that confuses me ... I have a query that looks like:

SELECT count(counter_id) \
  FROM referrer_link \
 WHERE counter_id = ? \
   AND referrer_id = ? \
   AND stat_date = ?;

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)

EXPLAIN

Table looks like:

webcounter=> \d referrer_link
Table    = referrer_link
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| counter_id                       | int4                             |     4 |
| stat_date                        | datetime                         |     8 |
| referrer_id                      | int4                             |     4 |
| referrer_hits                    | int8                             |     8 |
+----------------------------------+----------------------------------+-------+
Indices:  referrer_link_counter_id
          referrer_link_referrer_id
          referrer_link_stat_date

Why does EXPLAIN only show the use of one of the indices, why counter_id
and why not all three?


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


Sun, 23 Jan 2000 23:06:13 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Use of Indicies ...
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?

			regards, tom lane

Mon, 24 Jan 2000 00:58:38 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Use of Indicies ... 
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.

would it make sense to implement a new (?) "join" strategy for evaluating
AND's 
that gets just tids from affected indices and then performs a multiset AND on
them
sorts the result and then uses the it to access the actual rows.

> 
> Do you have reason to think that one of the other indexes would have
> been cheaper?
> 

It could be faster if one could actually do the above.

It's just another of these unintuitive things I mentioned in the "Happy
dropping" 
thread - common sense guess of optimiser behaviour is not what it actually
done. 

--------------
Hannu

Mon, 24 Jan 2000 12:03:42 +0200
Hannu Krosing <hannu@tm.ee>
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 


Mon, 24 Jan 2000 09:04:37 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Use of Indicies ... 