Hallo ! I want to tune a database. There a many redundant datas in the database , because of all the relations were consider as n:m relations. But the most of them are 1:n Relations. So my approach was to cut the redundancies to get more performance. But .. happens! The query with the 3 tables is faster than the query with 2 tables. That is paradox to the Explain output. And: the real database functions like dbPG95GetIndex and all functions defined by me are slower. The whole program is slower than before. I disabled all the index.(since with index the behavior is the same) The database pacs ist only restructured. They have the same data. With database pacs and compare a vacuum was made. I looked at the user time , since system time is faked because my testprogram hands over the control to the postmaster and the postmaster is doing his own work. So I made a lot of tests to get a average usertime. So escapes will be catched and eliminated. Here are the tabledescriptions for the original database "compare": tables i.e. There is a n:m relationship between patient and study realized with relpatient_study000 relationtable. Table "patient" Attribute | Type | Modifier ----------------------+------------------------+---------- chilioid | character varying(80) | name | text | id | character varying(256) | birthdate | date | birthtime | time | sex | character(1) | medicalrecordlocator | character varying(128) | Table "study" Attribute | Type | Modifier ------------------------+------------------------+---------- chilioid | character varying(80) | instanceuid | character varying(64) | id | character varying(64) | studydate | date | studytime | time | modality | character varying(2) | manufacturer | character varying(128) | referingphysician | text | description | character varying(128) | manufacturersmodelname | character varying(128) | importtime | double precision | chilisenderid | character varying(80) | accessionnumber | character varying(64) | institutionname | character varying(128) | workflowstate | character varying(8) | flags | character varying(8) | performingphysician | character varying(128) | reportingphysician | character varying(128) | Table "relpatient_study000" Attribute | Type | Modifier -----------+-----------------------+---------- chilioid | character varying(80) | parentoid | character varying(80) | childoid | character varying(80) | parentoid is here the oid of the patient and childoid is here the oid of the study. Thats the query with the original database "compare": 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 psql -d compare -c "EXPLAIN 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 NOTICE: QUERY PLAN: Sort (cost=1135170635.79..1135170635.79 rows=748802386 width=296) -> Merge Join (cost=1025510.64..1057837.48 rows=748802386 width=296) -> Sort (cost=1017989.22..1017989.22 rows=2556861 width=96) -> Merge Join (cost=4287.84..4763.21 rows=2556861 width=96) -> Sort (cost=990.43..990.43 rows=8725 width=72) -> Seq Scan on patient (cost=0.00..212.25 rows=8725 width=72) -> Sort (cost=3297.40..3297.40 rows=29305 width=24) -> Seq Scan on relpatient_study000 r0 (cost=0.00..774.05 rows=29305 width=24) -> Sort (cost=7521.42..7521.42 rows=29286 width=200) -> Seq Scan on study (cost=0.00..1116.86 rows=29286 width=200) ----------------------------- Thats the query with the new restructured database "pacs": 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 psql -d pacs -c "EXPLAIN SELECT patient.*,study.* FROM patient,study WHERE (patient.chiliOID=study.patientOID ) order by patient.name using <" > 2tableni NOTICE: QUERY PLAN: Sort (cost=2194791.19..2194791.19 rows=2555204 width=284) -> Merge Join (cost=8978.44..9453.57 rows=2555204 width=284) -> Sort (cost=990.43..990.43 rows=8725 width=72) -> Seq Scan on patient (cost=0.00..212.25 rows=8725 width=72) -> Sort (cost=7988.00..7988.00 rows=29286 width=212) -> Seq Scan on study (cost=0.00..1236.86 rows=29286 width=212) Restructured tables i.e. PAtient-study relationship is 1:n realized with column patientoid in table study. Table "patient" Attribute | Type | Modifier ----------------------+------------------------+---------- chilioid | character varying(80) | name | text | id | character varying(256) | birthdate | date | birthtime | time | sex | character(1) | medicalrecordlocator | character varying(128) | Table "study" Attribute | Type | Modifier ------------------------+------------------------+---------- chilioid | character varying(80) | instanceuid | character varying(64) | id | character varying(64) | studydate | date | studytime | time | modality | character varying(2) | manufacturer | character varying(128) | referingphysician | text | description | character varying(128) | manufacturersmodelname | character varying(128) | importtime | double precision | chilisenderid | character varying(80) | accessionnumber | character varying(64) | institutionname | character varying(128) | workflowstate | character varying(8) | flags | character varying(8) | performingphysician | character varying(128) | reportingphysician | character varying(128) | patientoid | character varying(80) | The times of the processes are escape-eliminated by statistical methods. I determined that the "compare" database is 8% times faster than the new restructured "pacs" database. How can I understand this? Whats my mistake? Anybody who can make some sugestions on the above will receive my enthusiastic gratitude David M. Richter
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:[EMAIL PROTECTED] 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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html