RE: Optimizing performance using indexes

Hi!

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?

> But this will run faster in PostgreSQL until the IN indexing gets
> fixed:
> SELECT *
> FROM aaa
> WHERE EXISTS(SELECT num
> FROM bbb
> WHERE name='123' AND bbb.num=aaa.num);

And next time: why? This query works: for every row in AAA do (subquery)
So there will be count(aaa) queries. As I understand, query above must do
2 select - one for internal and one for external selects. Where I'm wrong?

Thank you :)

--
Vladimir Litovka <doka(at)webest(dot)com>