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

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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match