pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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;





view thread (6+ messages)  latest in thread

Message-ID: <5.2.1.1.0.20070314133826.050002b0@pop6.sympatico.ca>
Permalink:  ../5.2.1.1.0.20070314133826.050002b0@pop6.sympatico.ca/
Also on:    postgresql.org/message-id/5.2.1.1.0.20070314133826.050002b0@pop6.sympatico.ca

 · 

reply

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