indexing issues: what's the fasted join method?
От | Zachariah Baum |
---|---|
Тема | indexing issues: what's the fasted join method? |
Дата | |
Msg-id | 199811122222.OAA03988@JC.StudioArchetype.COM обсуждение исходный текст |
Список | pgsql-admin |
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
В списке pgsql-admin по дате отправления: