From: Wiebe Cazemier <halfgaar@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Joins on many-to-many relations.
Date: Wed, 14 Mar 2007 16:39:42 +0100
Message-ID: <et94vu$qdt$1@sea.gmane.org> (raw)
Hi,
Doing a join on one-to-many relations (like "orders" joining "custumors") is
easy, but what if there are many-to-many relations involved?
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. It takes about 3 seconds, for only 800
accounts). Without the subselect in the JOIN statement (and therefor without
the ability to search based on the co-owner names), it is significantly
faster.
My question is, can joining many-to-many relations be done in a better way than
what I'm doing here?
Thanks in advance.
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: halfgaar@gmx.net
Subject: Re: Joins on many-to-many relations.
In-Reply-To: <et94vu$qdt$1@sea.gmane.org>
* 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