Re: [SQL] RE: Optimizing performance using indexes

>On Thu, 15 Oct 1998, Jackson, DeJuan wrote:
>
>> > = create table aaa (num int2, name char(16))
>> > = create index ax on aaa (num);
>> > = create table bbb (num int2, name char(16));
>> > = create index bx on bbb (num);
>> >
>> > = explain select * from aaa where num in (select num from bbb where
>> > name = '123');
>> > Seq Scan on aaa (cost=42.00 size=101 width=14)
>> > SubPlan
>> > -> Seq Scan on bbb (cost=0.00 size=0 width=2)
>> >
>> > subquery in the example above must to use indexes. Why it doesn't so?
>> Because you aren't selecting on the indexed field... :)
>
>It seems I don't understand some principles of indexes :-( After creating
>
>= create index bx1 on bbb (num, name);
>
>PostgreSQL still uses Seq scan for subquery, but index only for "name"
>field works fine. Why so? Why index on two fields doesn't works when there
>is one field in WHERE?

An index on (num,name) can be used when looking up by num or num and name.
It has no value when looking up by name alone.

Given this data:
2,a
1,d
3,b
1,a

The index will be in this order:
1,a
1,d
2,a
3,b

The occurances of 'a' in the index order are not contiguous so a linear
search will still have to be done.

In fact AFAIK if your num field is unque number, then there is nothing to be
gained by having additional fields listed after it in an index. It just
makes the index larger than it needs to be.