I am working on a select that would use the IN statement as you can view
below.
After reading this thread, which says that the IN statement is "slow" I am
wondering how I would rewrite using "EXISTS...IN"
I've searched the docs for references to "EXISTS IN" but haven't found
anything with regards to selects.
Can you tell me how I can write this using "exists"? Would I reap a
significant performance gain by using "exists" instead of just "in"
SELECT
index_uri.uri,
index_uri.description,
index_uri.title,
index_type.type,
index_type.icon,
SUM(index.word_count) AS score
FROM
index,index_word,index_uri,index_type
WHERE
index_word.word IN ('radio','spry')
AND
index_word.word_id=index.word_id
AND
index_uri.uri_id = index.uri_id
AND
index_type.type_id = index_uri.type_id
GROUP BY
index_uri.uri,
index_uri.description,
index_uri.title,
index_type.type,
index.word_count,
index_type.icon
ORDER BY
score DESC
----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
To: "Jan Wessely" <jawe@jawe.net>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, March 23, 2001 9:12 AM
Subject: Re: Slow SELECT...IN statements
> [ Charset ISO-8859-1 unsupported, converting... ]
> > The FAQ states in entry 4.23 that SELECT...IN statements are slow and
> > recommends to use EXISTS...IN statements instead. It also states that
this
> > will be resolved in some future version.
> > I didn't find any entries about that in the TODO list, does anybody know
> > when this will be fixed?
>
> It will be fixed when we do the query tree rewrite, which is on the TODO
> list, hopefully for 7.2.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>
>
