Re: anti-join chosen even when slower than old plan

От: Kevin Grittner
Тема: Re: anti-join chosen even when slower than old plan
Дата: ,
Msg-id: 4CDA3DF302000025000374F7@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: anti-join chosen even when slower than old plan  ("Kevin Grittner")
Список: pgsql-performance

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

anti-join chosen even when slower than old plan  ("Kevin Grittner", )
 Re: anti-join chosen even when slower than old plan  ("Kevin Grittner", )
  Re: anti-join chosen even when slower than old plan  (Tom Lane, )
   Re: anti-join chosen even when slower than old plan  ("Kevin Grittner", )
 Re: anti-join chosen even when slower than old plan  (Tom Lane, )
  Re: anti-join chosen even when slower than old plan  (Grzegorz Jaśkiewicz, )
  Re: anti-join chosen even when slower than old plan  ("Kevin Grittner", )
   Re: anti-join chosen even when slower than old plan  (Tom Lane, )
   Re: anti-join chosen even when slower than old plan  (Robert Haas, )
    Re: anti-join chosen even when slower than old plan  ("Kevin Grittner", )
     Re: anti-join chosen even when slower than old plan  (Tom Lane, )
      Re: anti-join chosen even when slower than old plan  (Robert Haas, )
       Re: anti-join chosen even when slower than old plan  (Kenneth Marshall, )
        Re: anti-join chosen even when slower than old plan  (Mladen Gogala, )
         Re: anti-join chosen even when slower than old plan  (Kenneth Marshall, )
         Re: anti-join chosen even when slower than old plan  (Bob Lunney, )
     Re: anti-join chosen even when slower than old plan  (Mladen Gogala, )
      Re: anti-join chosen even when slower than old plan  ("Kevin Grittner", )
       Re: anti-join chosen even when slower than old plan  (Mladen Gogala, )
        Re: anti-join chosen even when slower than old plan  (Tom Lane, )
         Re: anti-join chosen even when slower than old plan  (Mladen Gogala, )
         Re: anti-join chosen even when slower than old plan  (Bruce Momjian, )
        Re: anti-join chosen even when slower than old plan  (Craig James, )
       Re: anti-join chosen even when slower than old plan  (Robert Haas, )
        Re: anti-join chosen even when slower than old plan  (Tom Lane, )
         Re: anti-join chosen even when slower than old plan  (Tom Lane, )
          Re: anti-join chosen even when slower than old plan  (Robert Haas, )
           Re: anti-join chosen even when slower than old plan  (Tom Lane, )
            Re: anti-join chosen even when slower than old plan  ("Kevin Grittner", )
            Re: anti-join chosen even when slower than old plan  (Robert Haas, )
             Re: anti-join chosen even when slower than old plan  (<>, )
              Re: anti-join chosen even when slower than old plan  (Kenneth Marshall, )
             Re: anti-join chosen even when slower than old plan  (Cédric Villemain, )
              Re: anti-join chosen even when slower than old plan  (Robert Haas, )
               Re: anti-join chosen even when slower than old plan  (Tom Lane, )
                Re: anti-join chosen even when slower than old plan  (Robert Haas, )
                Re: anti-join chosen even when slower than old plan  (Cédric Villemain, )
                 Re: anti-join chosen even when slower than old plan  ("Marc Mamin", )
                  Re: anti-join chosen even when slower than old plan  (bricklen, )
                  Re: anti-join chosen even when slower than old plan  (Robert Haas, )
                Re: anti-join chosen even when slower than old plan  (Bruce Momjian, )
                 Re: anti-join chosen even when slower than old plan  (Cédric Villemain, )
                  Re: anti-join chosen even when slower than old plan  (Robert Haas, )
                   Re: anti-join chosen even when slower than old plan  (Cédric Villemain, )
                   Re: anti-join chosen even when slower than old plan  (Bruce Momjian, )
             Re: anti-join chosen even when slower than old plan  (Bruce Momjian, )
              Re: anti-join chosen even when slower than old plan  (Cédric Villemain, )
            Re: anti-join chosen even when slower than old plan  (Cédric Villemain, )
             Re: anti-join chosen even when slower than old plan  (Vitalii Tymchyshyn, )
              Re: anti-join chosen even when slower than old plan  (Cédric Villemain, )
               Re: anti-join chosen even when slower than old plan  (Vitalii Tymchyshyn, )
                Re: anti-join chosen even when slower than old plan  (Cédric Villemain, )
          Re: anti-join chosen even when slower than old plan  ("Kevin Grittner", )
           Re: anti-join chosen even when slower than old plan  ("Kevin Grittner", )
           Re: anti-join chosen even when slower than old plan  (Tom Lane, )
            Re: anti-join chosen even when slower than old plan  ("Kevin Grittner", )
            Re: anti-join chosen even when slower than old plan  (Jon Nelson, )
          Re: anti-join chosen even when slower than old plan  (Andres Freund, )
         Re: anti-join chosen even when slower than old plan  (Robert Haas, )
          Re: anti-join chosen even when slower than old plan  (Tom Lane, )
    Re: anti-join chosen even when slower than old plan  (Віталій Тимчишин, )
 Re: anti-join chosen even when slower than old plan  ("Kevin Grittner", )

Grzegorz Jaśkiewicz wrote:

> you're joining on more than one key. That always hurts performance.

That's very clearly *not* the problem, as there is a plan which runs
in acceptable time but the optimizer is not choosing without being
coerced.

(1) Virtually every query we run joins on multi-column keys, yet we
have good performance except for this one query.

(2) We're talking about a performance regression due to a new release
picking a newly available plan which it wrongly estimates to be an
order of magnitude faster, when it's actually more than five times
slower.

-Kevin



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

От: Tom Lane
Дата:
Сообщение: Re: anti-join chosen even when slower than old plan
От: Robert Haas
Дата:
Сообщение: Re: anti-join chosen even when slower than old plan