Re: SQL or table design optimation

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