pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInconsistent usage of Index
3+ messages / 3 participants
[nested] [flat]
* Inconsistent usage of Index
@ 2001-06-09 15:18 Subra Radhakrishnan <subra100@yahoo.com>
2001-06-09 16:17 ` Re: Inconsistent usage of Index Peter Eisentraut <peter_e@gmx.net>
2001-06-09 16:23 ` Re: Inconsistent usage of Index Joe Conway <jconway2@home.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 15:18 Inconsistent usage of Index Subra Radhakrishnan <subra100@yahoo.com>
@ 2001-06-09 16:17 ` Peter Eisentraut <peter_e@gmx.net>
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 15:18 Inconsistent usage of Index Subra Radhakrishnan <subra100@yahoo.com>
@ 2001-06-09 16:23 ` Joe Conway <jconway2@home.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