I'm running PostgreSQL 6.4 on Linux, and have the following database setup:
i have 2 tables, table1 and table2, both with an 'id' field, and both with
an index on the 'id' field.
Usually my selects involve a join between the tables, but the join is quite
slow. I played around with the EXPLAIN command, and got the following
results:
EXPLAIN SELECT table1.id FROM table1,table2 WHERE table1.id = 100 AND table2.id = 100\g
NOTICE: QUERY PLAN:
Nested Loop (cost=4.10 size=2 width=8)
-> Index Scan using table1_idx3 on table1 (cost=2.05 size=1 width=8)
-> Index Scan using table2_idx on table2 (cost=2.05 size=2 width=0)
Which was to be expected, since the 2 indices that are used are on the 'id'
field.
However, when I also get these disappointing results:
EXPLAIN SELECT table1.version FROM table1,table2 WHERE table2.id = table1.id\g
NOTICE: QUERY PLAN:
Hash Join (cost=92.95 size=772 width=16)
-> Seq Scan on table1 (cost=37.44 size=771 width=12)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on table2 (cost=18.03 size=304 width=4)
How come it doesn't use the indices here? Ideas? Is there a better way to do
this?
--
Yes is a BARGAIN
No is a TURNOFF - Wire
--Zachariah - Studio Archetype - 415-659-4435 - mailto:zack@studioarchetype.com
