Joins on many-to-many relations. - Mailing list pgsql-sql

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.