Обсуждение: IN optimization in 7.2 ?????

hi
was there optimization for IN (SELECT ... ) usage in 7.2?
we just got query which runs 7 times as fast with in than with exists !
the query is like select field from table where id in (select ... where
 fieldx in (... IN (...)));

depesz

--
hubert depesz lubaczewski                          http://www.depesz.pl/
------------------------------------------------------------------------
... vows are spoken to be broken ...                 [enjoy the silence]
... words are meaningless and forgettable ...             [depeche mode]

hubert depesz lubaczewski wrote:
> hi
> was there optimization for IN (SELECT ... ) usage in 7.2?
> we just got query which runs 7 times as fast with in than with exists !
> the query is like select field from table where id in (select ... where
>  fieldx in (... IN (...)));

Not that I know of.

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

On Mon, Feb 18, 2002 at 09:52:19AM -0500, Bruce Momjian wrote:
> Not that I know of.

strange. what could lead to this results then?
i used to think that IN (SELECT ...) is the slowest possible way at all.

depesz

p.s. of course both select's use indices, and table is vacuumed

--
hubert depesz lubaczewski                          http://www.depesz.pl/
------------------------------------------------------------------------
... vows are spoken to be broken ...                 [enjoy the silence]
... words are meaningless and forgettable ...             [depeche mode]

hubert depesz lubaczewski wrote:
> On Mon, Feb 18, 2002 at 09:52:19AM -0500, Bruce Momjian wrote:
> > Not that I know of.
>
> strange. what could lead to this results then?
> i used to think that IN (SELECT ...) is the slowest possible way at all.
>
> depesz
>
> p.s. of course both select's use indices, and table is vacuumed

I have always wondered this too.  Seems IN evaluates the entire query
while EXISTS evaluates it for each row, or at least that is how I
understand it, so saying EXISTS is always faster may be wrong.
Comments?


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

hi,

reading this I remembered that I had a performance problem with "IN" too,
but not with a subselect but a list of values.

I had (computed) queries like:
  ....
  and xyz in (1,3,7,234......)
  ....
with up to 20 such numbers, and found out the queries to be much faster when
I wrote it this way:
  ....
  and xyz >= 1
  and xyz <= 234
  and xyz in (1,3,7,234......)
  ....
where 1 is the minimum and 234 is the maximum of all values.  The query plan
was much much better in the latter case. Don't know if something minor is
worth optimizing, but I just want let you know.

Best regards,
    Mario Weilguni
----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
To: <depesz@depesz.pl>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, February 18, 2002 3:52 PM
Subject: Re: [GENERAL] IN optimization in 7.2 ?????


> hubert depesz lubaczewski wrote:
> > hi
> > was there optimization for IN (SELECT ... ) usage in 7.2?
> > we just got query which runs 7 times as fast with in than with exists !
> > the query is like select field from table where id in (select ... where
> >  fieldx in (... IN (...)));
>
> Not that I know of.
>
> --
>   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 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

IIRC, the subselect is executed only once, but using IN (...) can be slow for
large result sets because the searching in IN is just a sequential scan of
that result set.

Not sure why it'd be faster to crop of the first and last ones though..

--
Shane

On Monday 18 Feb 2002 3:42 pm, Bruce Momjian wrote:
> hubert depesz lubaczewski wrote:
> > On Mon, Feb 18, 2002 at 09:52:19AM -0500, Bruce Momjian wrote:
> > > Not that I know of.
> >
> > strange. what could lead to this results then?
> > i used to think that IN (SELECT ...) is the slowest possible way at all.
> >
> > depesz
> >
> > p.s. of course both select's use indices, and table is vacuumed
>
> I have always wondered this too.  Seems IN evaluates the entire query
> while EXISTS evaluates it for each row, or at least that is how I
> understand it, so saying EXISTS is always faster may be wrong.
> Comments?