Обсуждение: Slow SELECT...IN statements

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?

PS: The mailinglist archives' search engine wasn't working, so please
forgive me if that was already asked and answered on this list.

--
Jan Wessely
Vienna Knowledge Net
mailto:jawe@jawe.net

[ 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

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
>
>

"Matt Friedman" <matt@daart.ca> writes:
> I am working on a select that would use the IN statement as you can view
> below.

> WHERE
>   index_word.word IN ('radio','spry')

The above is perfectly OK.  It's really just a shorthand for
    index_word.word = 'radio' OR index_word.word = 'spry'
anyway.

> After reading this thread, which says that the IN statement is "slow" I am
> wondering how I would rewrite using "EXISTS...IN"

The thread was about "foo IN (SELECT ...)".  A sub-select IN is a
completely different animal from IN (list-of-values).

            regards, tom lane