Thread: Use of Indicies ...
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
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
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
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