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 .
Thanks
Deepak
