Return-Path: postman Received: from localhost (localhost [127.0.0.1]) by nobozo.CS.Berkeley.EDU (8.6.4/8.6.3) with SMTP id FAA18436 for postgres-dist; Tue, 16 Nov 1993 05:54:53 -0800 Resent-From: POSTGRES mailing list Resent-Message-Id: <199311161354.FAA18436@nobozo.CS.Berkeley.EDU> X-Authentication-Warning: nobozo.CS.Berkeley.EDU: Host localhost didn't use HELO protocol Sender: owner-postman@postgres.Berkeley.EDU X-Return-Path: owner-postman Received: from eng.ie.cuhk.hk (eng.ie.cuhk.hk [137.189.98.200]) by nobozo.CS.Berkeley.EDU (8.6.4/8.6.3) with SMTP id FAA18427 for ; Tue, 16 Nov 1993 05:54:48 -0800 From: shlam@ie.cuhk.hk Received: by eng.ie.cuhk.hk (5.57/Ultrix3.0-C) id AA10069; Tue, 16 Nov 93 16:56:18 +0800 Message-Id: <9311160856.AA10069@eng.ie.cuhk.hk> To: postgres@postgres.Berkeley.EDU Subject: We need > 1 minute to make a query Date: Tue, 16 Nov 93 16:56:17 +0800 Resent-To: postgres-dist@postgres.Berkeley.EDU X-Mts: smtp Resent-Date: Tue, 16 Nov 93 05:54:52 -0800 Resent-XMts: smtp 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: shlam@ie.cuhk.hk Tel: (852) 609 8364 Fax: (852) 603 5032