Re: [ADMIN] indexing issues: what's the fasted join method?

Did you do a VACUUM ANALYZE?

Check out man page on: vacuum

Zachariah Baum wrote:

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