Re: A question about indexes...

Hello,

I have the following tables in my database
Painter(id integer, uri varchar(256))
paints(id1 integer, id2 integer)

in order to speed up the join (select * from painter, paints where
painter.id= paints.id1) between these two tables I have created indexes
on the field painter.id and/or paints.id1.

But as I see from the query plan the indexes are not used, instead
sequential search is done either I define indexes or not.
As you can see below the query plan remains the same.
Is that reasonable??? Shouldn't Postgresql use the indexes in order
to optimize question???I can't see why is better to make sequential search
since the size of tables is relatively big.

A) No indexes are defined on the tables
Hash Join (cost=12269.78 rows=60014 width=24)
-> Seq Scan on painter1 (cost=4234.97 rows=99999 width=16)
-> Hash (cost=1931.92 rows=50331 width=8)
-> Seq Scan on paints (cost=1931.92 rows=50331 width=8)

B1)
BTree index on painter.id

Hash Join (cost=12269.78 rows=60014 width=24)
-> Seq Scan on painter (cost=4234.97 rows=99999 width=16)
-> Hash (cost=1931.92 rows=50331 width=8)
-> Seq Scan on paints (cost=1931.92 rows=50331 width=8)

B2)
Primary Key on painter.id
Hash Join (cost=12269.78 rows=60014 width=24)
-> Seq Scan on painter (cost=4234.97 rows=99999 width=16)
-> Hash (cost=1931.92 rows=50331 width=8)
-> Seq Scan on paints (cost=1931.92 rows=50331 width=8)

C1)
BTree index on painter.id and Btree on paints.id1
Hash Join (cost=12269.78 rows=60014 width=24)
-> Seq Scan on painter (cost=4234.97 rows=99999 width=16)
-> Hash (cost=1931.92 rows=50331 width=8)
-> Seq Scan on paints (cost=1931.92 rows=50331 width=8)

C2)
Primary Key on painter.id and Btree on paints.id1
Hash Join (cost=12269.78 rows=60014 width=24)
-> Seq Scan on painter (cost=4234.97 rows=99999 width=16)
-> Hash (cost=1931.92 rows=50331 width=8)
-> Seq Scan on paints (cost=1931.92 rows=50331 width=8)

Regards,
Sofia Alexaki