(Here again; my email adress was killed)
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