Re: optimizing queries using IN and EXISTS

От: Tom Lane
Тема: Re: optimizing queries using IN and EXISTS
Дата: ,
Msg-id: 3691.1342650974@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: optimizing queries using IN and EXISTS  (Nick Hofstede)
Ответы: Re: optimizing queries using IN and EXISTS  (Nick Hofstede)
Список: pgsql-performance

Скрыть дерево обсуждения

optimizing queries using IN and EXISTS  (Nick Hofstede, )
 Re: optimizing queries using IN and EXISTS  (Peter Geoghegan, )
  Re: optimizing queries using IN and EXISTS  (Nick Hofstede, )
 Re: optimizing queries using IN and EXISTS  (Tom Lane, )
  Re: optimizing queries using IN and EXISTS  (Nick Hofstede, )

Nick Hofstede <> writes:
> I'm surprised at the difference in speed/execution plan between two logically equivalent queries, one using IN, the
otherusing EXISTS. (At least I think they are logically equivalent) 

> SELECT *
> FROM   foo
> WHERE  'text6' IN (SELECT value
>                    FROM   bar
>                           JOIN foo AS foo2
>                             ON bar.foo_ref = foo2.id
>                    WHERE  foo2.id = foo.id)

Hm.  convert_ANY_sublink_to_join() rejects subqueries that contain any
Vars of the parent query level, so the reference to foo.id prevents this
from being converted to a semijoin.  However, it seems like that's
overly restrictive.  I'm not sure that we could remove the test
altogether, but at least outer vars used in WHERE seem safe.

In the meantime, you can recast like this:

SELECT *
FROM   foo
WHERE  ('text6', id) IN (SELECT value, foo2.id
                   FROM   bar
                          JOIN foo AS foo2
                            ON bar.foo_ref = foo2.id)

and still get a semijoin plan from an IN-style query.

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: David Kerr
Дата:
Сообщение: Re: Process 11812 still waiting for ExclusiveLock on extension of relation
От: Felix Scheicher
Дата:
Сообщение: queries are fast after dump->restore but slow again after some days dispite vacuum