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>