pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Inconsistent usage of Index
3+ messages / 3 participants
[nested] [flat]

* Inconsistent usage of Index
@ 2001-06-09 15:18  Subra Radhakrishnan <subra100@yahoo.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Subra Radhakrishnan @ 2001-06-09 15:18 UTC (permalink / raw)
  To: pgsql-sql

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/

Attachments:

Inconsistent index usage.txt (text/plain, 2.9K) download ------------------------------------------------------------------------------- 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.
^ permalink raw reply [nested|flat] 3+ messages in thread

* Re: Inconsistent usage of Index
@ 2001-06-09 16:17  Peter Eisentraut <peter_e@gmx.net>
  parent: Subra Radhakrishnan <subra100@yahoo.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Peter Eisentraut @ 2001-06-09 16:17 UTC (permalink / raw)
  To: Subra Radhakrishnan <subra100@yahoo.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Inconsistent usage of Index
@ 2001-06-09 16:23  Joe Conway <jconway2@home.com>
  parent: Subra Radhakrishnan <subra100@yahoo.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Joe Conway @ 2001-06-09 16:23 UTC (permalink / raw)
  To: Subra Radhakrishnan <subra100@yahoo.com>; pgsql-sql

> 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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-06-09 16:23 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-09 15:18 Inconsistent usage of Index Subra Radhakrishnan <subra100@yahoo.com>
2001-06-09 16:17 ` Peter Eisentraut <peter_e@gmx.net>
2001-06-09 16:23 ` Joe Conway <jconway2@home.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox