pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery optimizing - paradox behave
7+ messages / 5 participants
[nested] [flat]
* Query optimizing - paradox behave
@ 2001-07-19 10:20 David M. Richter <D.Richter@DKFZ-heidelberg.de>
0 siblings, 2 replies; 7+ messages in thread
From: David M. Richter @ 2001-07-19 10:20 UTC (permalink / raw)
To: pgsql-sql
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
Attachments:
D.Richter.vcf (text/x-vcard, 467B) download
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimizing - paradox behave
@ 2001-07-19 15:12 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: David M. Richter <D.Richter@DKFZ-heidelberg.de>
1 sibling, 1 reply; 7+ messages in thread
From: Stephan Szabo @ 2001-07-19 15:12 UTC (permalink / raw)
To: David M. Richter <D.Richter@DKFZ-heidelberg.de>; +Cc: pgsql-sql
What version are you using? (dbPG95GetIndex?)
On Thu, 19 Jul 2001, David M. Richter wrote:
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimizing - paradox behave
@ 2001-07-19 15:31 Josh Berkus <josh@agliodbs.com>
parent: David M. Richter <D.Richter@DKFZ-heidelberg.de>
1 sibling, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2001-07-19 15:31 UTC (permalink / raw)
To: David M. Richter <D.Richter@DKFZ-heidelberg.de>; pgsql-sql
David,
You will no doubt hear later from the tuning experts on the list.
However, let me save everybody some time by verifying some basics:
1. When you restructured the database, you ran VACUUM ANALYZE on the new
database (pacs)?
2. You said that you "eliminated the indexes" because they weren't
helping performance. Is this right? It seems a little hard to figure
from here.
3. General Advice: If you're concerned about query performance, get rid
of those VARCHAR(80) primary keys and replace them with INT4 or INT8!
The math is easy to do:
If you're processing INT8 keys for 1,000,000 table rows that's
8,000,000 (roughly 8mb) data on disk and data being processed.
If you're processing VARCHAR(80) keys for 1,000,000 table rows, thats
82,000,000 bytes (82 mb) on disk and in ram to be processed.
In theory, you could get a 10-fold increase in JOIN performance by
switching to INT8 keys. In practice, its probably more like double or
triple but that ain't bad, either.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Query optimizing - paradox behave
@ 2001-07-20 08:49 David M. Richter <David.M.Richter@freenet.de>
0 siblings, 1 reply; 7+ messages in thread
From: David M. Richter @ 2001-07-20 08:49 UTC (permalink / raw)
To: pgsql-sql
(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
Attachments:
David.M.Richter.vcf (text/x-vcard, 467B) download
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimizing - paradox behave
@ 2001-07-20 17:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: David M. Richter <David.M.Richter@freenet.de>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2001-07-20 17:05 UTC (permalink / raw)
To: David M. Richter <David.M.Richter@freenet.de>; +Cc: pgsql-sql
"David M. Richter" <David.M.Richter@freenet.de> 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...
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimizing - paradox behave
@ 2001-07-24 11:11 David M. Richter <David.M.Richter@freenet.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: David M. Richter @ 2001-07-24 11:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>
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" <David.M.Richter@freenet.de> 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...
Attachments:
David.M.Richter.vcf (text/x-vcard, 467B) download
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimizing - paradox behave
@ 2001-07-24 15:24 David M. Richter <David.M.Richter@freenet.de>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 7+ messages in thread
From: David M. Richter @ 2001-07-24 15:24 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Hallo Stephan!
Are there several versions of dbPG95GetIndex existing, or did you
mention postgres version 7.1.2?
With a little help I have killed the Problem!! Yeah,
But now I trying to improve the C-code. Do You have any experience with
optimizing C-Code. Are there some new miracle-like function, wich
improves the speed dramatically?
Do You know some database options wich I could change for better
performance?
Thanks a lot for Your suggestions!!
David
>What version are you using? (dbPG95GetIndex?)
Attachments:
David.M.Richter.vcf (text/x-vcard, 467B) download
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2001-07-24 15:24 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-19 10:20 Query optimizing - paradox behave David M. Richter <D.Richter@DKFZ-heidelberg.de>
2001-07-19 15:12 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-07-24 15:24 ` David M. Richter <David.M.Richter@freenet.de>
2001-07-19 15:31 ` Josh Berkus <josh@agliodbs.com>
2001-07-20 08:49 Query optimizing - paradox behave David M. Richter <David.M.Richter@freenet.de>
2001-07-20 17:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-07-24 11:11 ` David M. Richter <David.M.Richter@freenet.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox