Search Postgresql Archives

Question on Explain : Index Scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux