Question on Index scan: ---------------------------------------------------------------------------> test=# \d test_seqindex1 Table "public.test_seqindex1" Column | Type | Modifiers --------+-----------------------+----------- sid | character varying(13) | not null name | character varying(80) | Indexes: "test_seqindex1_pkey" PRIMARY KEY, btree (sid)
test=# explain select * from test_seqindex1 where sid='AA023'; QUERY PLAN ------------------------------------------------------------------------------------------- Index Scan using test_seqindex1_pkey on test_seqindex1 (cost=0.00..8.27 rows=1 width=28) Index Cond: ((sid)::text = 'AA023'::text)
test=# \d test_seqindex2 Table "public.test_seqindex2" Column | Type | Modifiers --------+-----------------------+----------- eid | integer | not null sid | character varying(13) | ename | character varying(80) | Indexes: "test_seqindex2_pkey" PRIMARY KEY, btree (eid) "idx_test_seqindex2_sid" btree (sid)
test=# explain select * from test_seqindex2 where sid='AA023'; QUERY PLAN -------------------------------------------------------------------------------------- Bitmap Heap Scan on test_seqindex2 (cost=4.95..275.53 rows=73 width=30) Recheck Cond: ((sid)::text = 'AA023'::text) -> Bitmap Index Scan on idx_test_seqindex2_sid (cost=0.00..4.93 rows=73 width=0) Index Cond: ((sid)::text = 'AA023'::text)
test=explain select * from test_seqindex1 t1,test_seqindex2 t2 where t1.sid=t2.sid; QUERY PLAN ------------------------------------------------------------------------------------ Hash Join (cost=1231.55..46386.19 rows=920544 width=58) Hash Cond: ((t2.sid)::text = (t1.sid)::text) -> Seq Scan on test_seqindex2 t2 (cost=0.00..16225.97 rows=920697 width=30) -> Hash (cost=581.80..581.80 rows=33580 width=28) -> Seq Scan on test_seqindex1 t1 (cost=0.00..581.80 rows=33580 width=28) (5 rows)
I was hoping the optimizer would do a join using index scan.
Could some one please explain me why its not doing an index scan rather than sequential scan .