I must have missed something, but how is it possible that a join on tables A and B is faster (a lot faster) than a query to one of the tables with the same conditions?
The problem seems to be with the query plan, in the case os a query to table_a only, the planner executes a "Seq Scan", in the case of a join, an "Index Scan". table_a has about 4M records, so the difference is quite noticeable.
explain
select * from table_a where field_1=1 and field_2='20030808' and field_3='963782342';
NOTICE: QUERY PLAN:
Seq Scan on table_a (cost=0.00..373661.73 rows=12 width=227)
EXPLAIN
explain select * FROM table_b, table_a WHERE table_b.field_1 = table_a.field_1 AND table_b.field_3 = table_a.field_3 AND table_b.field_3 in ('963782342')
AND table_a.field_2 = '20030808' ; NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..317.07 rows=3 width=351)
-> Seq Scan on table_b (cost=0.00..308.80 rows=1 width=124)
-> Index Scan using table_a_i01 on table_a (cost=0.00..8.24 rows=2 width=227)
EXPLAIN
Index on table_a is defined on field_1, field_2 and field_3.
Thanks a lot for any help. Ruben.
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
