Lifting WHERE conditions out of inner select

Поиск
Список
Период
Сортировка
От John D. Burger
Тема Lifting WHERE conditions out of inner select
Дата
Msg-id E7518800-8302-4988-A52B-AC8EABD649A2@mitre.org
обсуждение исходный текст
Ответы Re: Lifting WHERE conditions out of inner select  (Richard Broersma Jr <rabroersma@yahoo.com>)
Re: Lifting WHERE conditions out of inner select  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Hi -

A colleague presented the following very slow query to me:

SELECT DISTINCT lemma FROM word
    JOIN sense USING (wordid)
    JOIN synset USING (synsetid)
   WHERE sense.synsetid
     IN (SELECT synset2id FROM semlinkref
          WHERE synset1id
            IN (SELECT synsetid FROM sense
                 WHERE wordid = (SELECT wordid FROM word WHERE
lemma='scramble'))
          AND linkid=1
          AND synset.pos='v')
   ORDER BY lemma;

I realized that the last constraint, synset.pos='v', actually applies
to one of the tables in the main join, and could be lifted out of the
double IN clause.  Doing so sped the query up by a factor of 10,000.

My question is, should the planner have figured this out, and we're
just losing out because we're stuck in 7.4?  Or is there some subtle
difference in semantics I'm missing?  The select results were the
same in both cases, but I'm willing to believe that's an accident of
our data.

(Sorry if no one can answer my question without the table
definitions, etc. - it seemed worthwhile trying to get away without
that for now.)

Thanks.

- John D. Burger
   MITRE



В списке pgsql-general по дате отправления:

Предыдущее
От: "A. Kretschmer"
Дата:
Сообщение: Re: Request: Anyone using bogus / "humorous" X-Message-Flag headers, could we please turn them off
Следующее
От: Richard Broersma Jr
Дата:
Сообщение: Re: Lifting WHERE conditions out of inner select