Join faster than single table query - Mailing list pgsql-general

Hi:

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.