From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Joins on many-to-many relations.
Date: Wed, 14 Mar 2007 13:58:01 -0400
Message-ID: <5.2.1.1.0.20070314133826.050002b0@pop6.sympatico.ca> (raw)
In-Reply-To: <et94vu$qdt$1@sea.gmane.org>
At 11:39 AM 3/14/07, Wiebe Cazemier wrote:
>Consider this scenario of three (simplified) tables:
>
>people
>- id
>- name
>
>accounts
>- id
>- owner_id REFERENCES people
>
>account_co_owners
>- co_owner_id REFERENCES people
>- account_id REFERENCES accounts
>
>I need a query that allows the user to search for accounts by giving names of
>either co-owners or owners. Currently, the query responsible is this:
>
>SELECT DISTINCT ON (account.id) account.*
>FROM accounts AS account
>INNER JOIN people AS owner
> ON owner.id = account.owner_id
> OR owner.id IN (SELECT co_owner_id
> FROM account_co_owners
> WHERE account_id = account.id
> AND co_owner_id = owner.id)
>WHERE owner.name LIKE '%user supplied search string%';
>
>But this query is too slow for my taste.
A performance question should always include the output of EXPLAIN ANALYZE.
I think the problem is database design. If you added a boolean column into
accounts table which would indicate owner/co-owner; then all data from
account_co_owner could be merged into accounts and the query would be much
simpler to code.
I don't expect this code to be any quicker; but I think it more clearly
identifies the problem with your design:
SELECT accounts.* from accounts
inner join
( SELECT account.* FROM
( select id,owner_id from accounts
union
select account_id,co_owner_id from account_co_owners
) as account
INNER JOIN
( SELECT id FROM people WHERE name LIKE '%user%' ) AS owner
on account.owner_id = owner.id
) as acct on acct.id=accounts.id;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: fbax@sympatico.ca
Subject: Re: Joins on many-to-many relations.
In-Reply-To: <5.2.1.1.0.20070314133826.050002b0@pop6.sympatico.ca>
References: <et94vu$qdt$1@sea.gmane.org> <5.2.1.1.0.20070314133826.050002b0@pop6.sympatico.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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