Woops, this might not go through via the address I used :> (not subscribed with that address)..
At 01:46 PM 7/13/03 -0700, Steve Wampler wrote:
The following left join should work if I've done my select right, you might want to play with a left versus right to see which will give you a better result, but this query should help:
SELECT * FROM attributes_table att LEFT JOIN attributes at ON (at.name = 'obsid' AND at.value = 'oid00066') WHERE att.id = at.id;
On Sun, Jul 13, 2003 at 08:09:17PM +0100, Richard Huxton wrote: > > I'm not an SQL or PostgreSQL expert. > > > > I'm getting abysmal performance on a nested query and > > need some help on finding ways to improve the performance: > [snip] > > select * from attributes_table where id in (select id from > > attributes where (name='obsid') and (value='oid00066')); > > This is the classic IN problem (much improved in 7.4 dev I believe). The > recommended approach is to rewrite the query as an EXISTS form if > possible. See the mailing list archives for plenty of examples. > > Could you not rewrite this as a simple join though?
Hmmm, I don't see how. Then again, I'm pretty much the village idiot w.r.t. SQL...
The inner select is locating a set of (2049) ids (actually from the same table, since 'attributes' is just a view into 'attributes_table'). The outer select is then locating all records (~30-40K) that have any of those ids. Is that really something a JOIN could be used for?
-Steve -- Steve Wampler -- [EMAIL PROTECTED] Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]