Обсуждение: Inconsistent usage of Index

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.

Subra Radhakrishnan writes:

> I have attached file explaining inconsistent usage of
> Index.

In the first case the system thinks it's getting 22 rows back, in the
second 713.  Depending on how many rows are in the table and whether those
estimates are close, choosing a sequential scan in the second case is
probably okay.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

> 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

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

Among other things, "vacuum analyze" calculates statistics for the table
columns, which are used by the optimizer to decide how to most efficiently
execute the query. See
http://www.postgresql.org/idocs/index.php?sql-vacuum.html for an
explanation.

See
http://www.postgresql.org/idocs/index.php?performance-tips.html#USING-EXPLAI
N for information on how to interpret the explain output. The output above
on instance 2 shows 713 rows expected output. If this is a significant
percent of the total number of rows in this table, then it *is* more
efficient to perform a table scan instead of using the index. What is the
total number or rows in this table?

If you still really want to force an index scan, try issuing "SET
ENABLE_SEQSCAN = OFF;". See
http://www.postgresql.org/idocs/index.php?runtime-config.html#RUNTIME-CONFIG
-OPTIMIZER for an explanation.

Hope this helps,

-- Joe