Re: SQL or table design optimation

[Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>]

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(at)triosolutions(dot)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