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
