Re: Equivalent queries produce different plans

От: Tom Lane
Тема: Re: Equivalent queries produce different plans
Дата: ,
Msg-id: 13642.1184117103@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Equivalent queries produce different plans  (Craig James)
Ответы: pg_restore causes 100  (Craig James)
Список: pgsql-performance

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

Join with lower/upper limits doesn't scale well  (Craig James, )
 Re: Join with lower/upper limits doesn't scale well  (Gregory Stark, )
  Equivalent queries produce different plans  (Craig James, )
   Re: Equivalent queries produce different plans  (Craig James, )
   Re: Equivalent queries produce different plans  (Tom Lane, )
    pg_restore causes 100  (Craig James, )
     Re: pg_restore causes 100  (Tom Lane, )

Craig James <> writes:
> The two queries below produce different plans.

> select r.version_id, r.row_num, m.molkeys from my_rownum r
> join my_molkeys m on (r.version_id = m.version_id)
> where r.version_id >= 3200000
> and   r.version_id <  3300000
> order by r.version_id;

> select r.version_id, r.row_num, m.molkeys from my_rownum r
> join my_molkeys m on (r.version_id = m.version_id)
> where r.version_id >= 3200000
> and   r.version_id <  3300000
> and   m.version_id >= 3200000
> and   m.version_id <  3300000
> order by r.version_id;

Yeah, the planner does not make any attempt to infer implied
inequalities, so it will not generate the last two clauses for you.
There is machinery in there to infer implied *equalities*, which
is cheaper (fewer operators to consider) and much more useful across
typical queries such as multiway joins on the same keys.  I'm pretty
dubious that it'd be worth the cycles to search for implied
inequalities.

            regards, tom lane


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

От: Tom Lane
Дата:
Сообщение: Re: Equivalent queries produce different plans
От: Dave Cramer
Дата:
Сообщение: best use of an EMC SAN