Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6OBVUf54827 for ; Tue, 24 Jul 2001 07:31:30 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f6OBDnn80063 for pgsql-sql@postgresql.org; Tue, 24 Jul 2001 07:13:49 -0400 (EDT) (envelope-from news) From: "David M. Richter" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Query optimizing - paradox behave Date: Tue, 24 Jul 2001 13:11:33 +0200 Organization: German Cancer Research Center Lines: 62 Message-ID: <3B5D57E5.D4603F92@freenet.de> References: <4085.995648716@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="------------09B96D7F0284F704FC316919" X-Complaints-To: scrappy@hub.org To: Tom Lane X-Mailer: Mozilla 4.7 [en] (X11; I; IRIX64 6.4 IP27) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200107/319 X-Sequence-Number: 4132 This is a multi-part message in MIME format. --------------09B96D7F0284F704FC316919 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hallo! Thanks a lot to You Tom. I stared only at the user and the system time. I didn't found any C-function, with wich I could measure the whole time, so I used getrusage(). I did not recognize, that the whole time is reduced, because the User time is increased. Could that anybody explain to me? Why is the usertime increased and the whole time is decreased? # Anyway .. Thanks all a lot for Your effort. I will now tune my radiology-database further... Thankful Greetings David "David M. Richter" writes: > The query with the 3 tables is faster than the query with 2 tables. How you figure that? > time psql -d compare -c "SELECT patient.*,study.* FROM > patient,study,relpatient_study000 r0 WHERE > (patient.chiliOID=r0.parentOID AND study.chiliOID=r0.childOID) order by > patient.name using <" > 3tableni > 1.671u 0.130s 0:11.14 16.4% 0+0k 0+0io 208pf+0w > time psql -d pacs -c "SELECT patient.*,study.* FROM patient,study WHERE > (patient.chiliOID=study.patientOID ) order by patient.name using <" > > 2tableni > 1.730u 0.120s 0:09.44 19.5% 0+0k 0+0io 208pf+0w 9.44 vs 11.14 seconds looks like a clear advantage for the second query to me... --------------09B96D7F0284F704FC316919 Content-Type: text/x-vcard; charset=us-ascii; name="David.M.Richter.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for David M. Richter Content-Disposition: attachment; filename="David.M.Richter.vcf" begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik version:2.1 email;internet:d.richter@dkfz-heidelberg.de adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-15296 fn:David M. Richter end:vcard --------------09B96D7F0284F704FC316919--