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;
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
