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
-------------------------------- 0000000000 --------------------------------------
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.