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(at)webest(dot)com>