[SQL] Index usage

[Subra Radhakrishnan]

Hi All,

The index created by me is not being used while doing
select. I found that out by using the EXPLAIN. For
example:

Table department has 

dept_num
dept_desc


Table 'employee' looks like this:

emp_num primary key,
emp_name,
dept_num (this is by way of foreign key relation from
department table)

Now, I create an index on the employee table using
'dept_num' as the attribute. And when I run select
which looks like:

Explain Select * from employee where dept_num =
'Finance';

It does not use the index and tells me that a
Sequential scan will be done on the table employee.

How do I fix this? 

Thanx in advance,

Subra

__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 
a year!  http://personal.mail.yahoo.com/

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[SQL] Inconsistent usage of Index

[Subra Radhakrishnan]

Hi All,

I have attached file explaining inconsistent usage of
Index. 

Thanks,

Subra

__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 
a year!  http://personal.mail.yahoo.com/

---
 Table "valid_testtypes"
   Attribute   | Type | Modifier
---+--+--
 v_ttyp_code   | varchar(100) | not null
 v_ttyp_description| varchar(50)  | not null
 v_ttyp_classification | varchar(15)  | not null
 v_ttyp_status | char(1)  | not null
 v_ttyp_status_date| timestamp| not null
 v_ttyp_user_created   | varchar(30)  | not null
 v_ttyp_date_created   | timestamp| not null
 v_ttyp_user_modified  | varchar(30)  |
 v_ttyp_date_modified  | timestamp|
 v_ttyp_reptype| varchar(10)  | not null
Index: valid_testtypes_pkey

 select v_ttyp_code from valid_testtypes ;
v_ttyp_code

 XRAY
 SCAN
 ECG
 PT
 CTG
 Bio-Chemistry
 Biopsy
 Clinical Pathology
 Complete Haematogram
 Endocrinology
 Haematology


 Table "allied_medical_req_main"
   Attribute   | Type | Modifier
---+--+--
 amr_sequence_num  | bigint   | not null
 amr_id| bigint   | not null
 pat_reg_no| bigint   | not null
 visit_id  | bigint   |
 emp_referral  | varchar(30)  |
 v_eref_id | bigint   |
 amr_date_created  | timestamp| not null
 amr_user_created  | varchar(30)  | not null
 amr_date_modified | timestamp|
 amr_user_modified | varchar(30)  |
 amr_complete_flag | char(1)  | not null
 amr_report_req_flag   | char(1)  |
 amr_film_req_flag | char(1)  |
 amr_request_date  | varchar(10)  | not null
 amr_request_time  | varchar(12)  | not null
 amr_remarks   | varchar(200) |
 amr_report_to_be_collected_on | timestamp| not null
 amr_report_collected_on   | timestamp|
 v_ttyp_code   | varchar(30)  | not null
Indices: allied_medical_req_main_pkey,
 ind_amr_v_ttyp_code,
 uk_amr_id_v_ttyp_code

Instance #1
---
 explain select * from allied_medical_req_main where v_ttyp_code = 'XRAY'
mnh_hospdb-# ;
NOTICE:  QUERY PLAN:

Index Scan using ind_amr_v_ttyp_code on allied_medical_req_main  (cost=0.00..20.35 row
s=22 width=192)

EXPLAIN


Instance #2
---
 explain select * from allied_medical_req_main where v_ttyp_code = 'XRAY';
NOTICE:  QUERY PLAN:

Seq Scan on allied_medical_req_main  (cost=0.00..79.49 rows=713 width=192)

EXPLAIN


 00 --


As you can see from Instance #1 and #2 above, the usage of Index is not consistent. Do 
you
have any suggestions? 

Thanks,

Subra

P.S: I also did vacuum on the database. However, I am not clear as to what actually it 
does. 



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])