Обсуждение: SQL or table design optimation
Hi, now I've got a question about table and/or query design. It's about having persons and contacts. Since every person should have as many contacs as neccessary, I created two separate tables: CREATE TABLE person ( pers_id serial PRIMARY KEY, name character varying(50) ); CREATE TABLE contact ( id serial PRIMARY KEY, kind character varying(20), type small int, value character varying(75), pers_id integer ); where kind describes the kind of the contact: mobile, email, www, ..., type says if this contact should be used as private or business contact and whether or not this is the standard contact for this person for this medium. value is the telephone number, email adress or whatever, pers_id is a foreign key to the person table. Now I'd like to have a query which includes some fields of person combined with the standard business contact for mobile and email, where it might happen that some persons do not have such a contact, but they should be included in this list anyway. I tried it with the following: SELECT DISTINCT name, email, mobile FROM person AS p LEFT OUTER JOIN ( SELECT person.pers_id, value as email FROM person INNER JOIN contact ON person.pers_id=contact.pers_id WHERE kind='email' AND type=1 ) AS q1 ON p.pers_id=q1.pers_id LEFT OUTER JOIN ( SELECT person.pers_id, value as mobile FROM person INNER JOIN contact ON person.pers_id=contact.pers_id WHERE kind='mobile' AND type=1 ) AS q2 ON p.pers_id=q2.pers_id; type=1 means standard business contact This gives exacly the resulted I'd like to have, but it is slow. I've about 45000 entries in person and 44000 entries in contact and this query takes 12 seconds, and if I add more joins for getting website, telephone of course it's getting much worse. There are indexes on person(pers_id), contact(id), contact(pers_id) and one on contact(kind, type). Thanks for reading all this stuff, and now my question: but could I do to get the same result in less time? Changing the table design, changing the query? I'm happy about any hint. Regards, Verena
Verena, You will probably need to post the results of an EXPLAIN output for anyone to help. Sean On 5/30/06 11:15 AM, "Verena Ruff" <lists@triosolutions.at> wrote: > Hi, > > now I've got a question about table and/or query design. It's about > having persons and contacts. Since every person should have as many > contacs as neccessary, I created two separate tables: > > CREATE TABLE person ( > pers_id serial PRIMARY KEY, > name character varying(50) > ); > > CREATE TABLE contact ( > id serial PRIMARY KEY, > kind character varying(20), > type small int, > value character varying(75), > pers_id integer > ); > > where kind describes the kind of the contact: mobile, email, www, ..., > type says if this contact should be used as private or business contact > and whether or not this is the standard contact for this person for this > medium. value is the telephone number, email adress or whatever, pers_id > is a foreign key to the person table. > > Now I'd like to have a query which includes some fields of person > combined with the standard business contact for mobile and email, where > it might happen that some persons do not have such a contact, but they > should be included in this list anyway. > > I tried it with the following: > > SELECT DISTINCT name, email, mobile > FROM person AS p > LEFT OUTER JOIN > ( > SELECT person.pers_id, value as email > FROM person > INNER JOIN contact ON person.pers_id=contact.pers_id > WHERE kind='email' AND type=1 > ) AS q1 ON p.pers_id=q1.pers_id > LEFT OUTER JOIN > ( > SELECT person.pers_id, value as mobile > FROM person > INNER JOIN contact ON person.pers_id=contact.pers_id > WHERE kind='mobile' AND type=1 > ) AS q2 ON p.pers_id=q2.pers_id; > > type=1 means standard business contact > > This gives exacly the resulted I'd like to have, but it is slow. I've > about 45000 entries in person and 44000 entries in contact and this > query takes 12 seconds, and if I add more joins for getting website, > telephone of course it's getting much worse. There are indexes on > person(pers_id), contact(id), contact(pers_id) and one on contact(kind, > type). > Thanks for reading all this stuff, and now my question: but could I do > to get the same result in less time? Changing the table design, changing > the query? I'm happy about any hint. > > Regards, > Verena > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org
On Tue, 30 May 2006, Verena Ruff wrote: > Hi, > > now I've got a question about table and/or query design. It's about > having persons and contacts. Since every person should have as many > contacs as neccessary, I created two separate tables: > > CREATE TABLE person ( > pers_id serial PRIMARY KEY, > name character varying(50) > ); > > CREATE TABLE contact ( > id serial PRIMARY KEY, > kind character varying(20), > type small int, > value character varying(75), > pers_id integer > ); > > where kind describes the kind of the contact: mobile, email, www, ..., > type says if this contact should be used as private or business contact > and whether or not this is the standard contact for this person for this > medium. value is the telephone number, email adress or whatever, pers_id > is a foreign key to the person table. > > Now I'd like to have a query which includes some fields of person > combined with the standard business contact for mobile and email, where > it might happen that some persons do not have such a contact, but they > should be included in this list anyway. > > I tried it with the following: > > SELECT DISTINCT name, email, mobile > FROM person AS p > LEFT OUTER JOIN > ( > SELECT person.pers_id, value as email > FROM person > INNER JOIN contact ON person.pers_id=contact.pers_id > WHERE kind='email' AND type=1 > ) AS q1 ON p.pers_id=q1.pers_id I think a construct like person AS p left outer join contact on (p.pers_id = contact.pers_id and kind='email' and type=1) may give the right results without another scan of person.
Hi, Sean Davis schrieb: > You might also tell us what columns are indexed and be sure that you have > done a vacuum analyze recently on the tables. > About indexed columns: >>> There are indexes on >>> person(pers_id), contact(id), contact(pers_id) and one on contact(kind, >>> type). >>> Vacuum analyze was done quite before doing that testing. I'll send the explain output later, thanks so long. Regards, Verena