Subject: We need > 1 minute to make a query

From: [email-masked]
Date: Tue, 16 Nov 93 16:56:17 +0800

We have two tables defined as following:

RELATIONS:

IMGSET ( 
sub_organ::text 
sub_diag2::text 
sub_diag1::text 
od_id::char16 
ref_org::char16 
symptom::_text 
diagnosis::text 
med_code::_char16 
organ::text 
code_no::char16 
site_perm_lst::_char16 
doc_perm_lst::_char16 
p_id::char16 
gen_site::text 
date::char16 
img_no::int2 
img_type::char16 
img_set::text 
img_folder::text 
Doc_id::char16 )

PATIENT ( 
o_p_id::char16 
Doc_name::text 
address::text 
birth::char16 
sex::char 
p_fname::text 
p_lname::char16 
p_id::char16 )

We also define the indexes as

INDEXES:

imgsetindex1 on relation IMGSET

The query is 

QUERY:

retrieve (IMGSET.img_set, PATIENT,p_id, PATIEBT.p_lname,
PATIENT.p_fname, IMGSET.data, IMGSET.gen_site, IMGSET.img_type,
PATIENT.sex, PATIENT.birth, IMGSET.organ, IMGSET.diagnosis,
IMGSET.med_code, IMGSET.img_folder, IMGSET.Doc_id, IMGSET.code_no,
IMGSET.img_no, PATIENT.o_p_id,
IMGSET.symptom, IMGSET.ref_org, IMGSET.od_id, IMGSET.sub_diag1,
IMGSET.sub_diag2, IMGSET.sub_organ)
where PATIENT.p_id=IMGSET.p_id and IMGSET.img_folder="new" and
IMGSET.Doc_id="hectorma" sort by data using >

Now, our database has ~1,200 records. We discover that the time to make
the above query
need  one minute and nineteen seconds. I wonder why the Postgres DBMS
takes so long to 
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
process this query. Is something wrong with our tables, index, or
query? Or is something 
wrong with the Postgress DBMS? Is there any tool to test our Postgres DBMS work 
properly?

I would be most grateful if you could give me some hint to slove the
problem. Thanks.

Alan S. H. Lam
Department of Information Engineering, CUHK, Hong Kong
E-mail: [email-masked] 
Tel: (852) 609 8364 Fax: (852) 603 5032