RE: Optimizing performance using indexes

Hello!

On Thu, 15 Oct 1998, Jackson, DeJuan wrote:

 I've forgotten to say: I'm using PostgreSQL 6.3.2 with patches from
 ftp://ftp.postgresql.org/pub/patches :

  . linux_elf.patch-980421.gz
  . gram.c.patch-980428
  . configure-980430.gz
  . btree_adj-980730.gz

> This table doesn't have many rows in it does it.
> Drop about 100+ rows into the table.  VACUUM the database and re-run the
> explains.
>     Let me know how it turns out,

 I've inserted 1000 rows into table:

= create table aaa (num int2, name char(16))
= create index ax on aaa (num);
= create index ax1 on aaa (num, name);

 and tried explains:

= explain select * from aaa where num > 5;
        Index Scan on aaa  (cost=22.67 size=334 width=14)

= explain select * from aaa where num = 5;
        Index Scan on aaa  (cost=2.05 size=2 width=14)

= explain select * from aaa where num = 5 and name != '123';
        Index Scan on aaa  (cost=2.05 size=1 width=14)

 It seems OK. The next I've tried subqueries and PostgreSQL has falled to Seq
scans:

= create table bbb (num int2, name char(16));
= create index bx on bbb (num);
= insert into bbb values (...)
  ^^^^^^-- less than 10 rows

= 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)

 You said that "v6.3 and before doesn't use indexes on IN queries", but
 subquery in the example above must to use indexes. Why it doesn't so?

 Thank you :)

--
Vladimir Litovka <doka@webest.com>