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

От: Tom Lane
Тема: Re: anti-join chosen even when slower than old plan
Дата: ,
Msg-id: 19088.1289501929@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: 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  (Andres Freund)
Список: 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", )

I wrote:
> I do think that something based around a settable-per-table caching
> percentage might be a reasonable way to proceed.

BTW ... on reflection it seems that this would *not* solve the use-case
Kevin described at the start of this thread.  What he's got AIUI is some
large tables whose recent entries are well-cached, and a lot of queries
that tend to hit that well-cached portion, plus a few queries that hit
the whole table and so see largely-not-cached behavior.  We can't
represent that very well with a caching knob at the table level.  Either
a high or a low setting will be wrong for one set of queries or the
other.

It might work all right if he were to partition the table and then have
a different caching value attached to the currently-latest partition,
but that doesn't sound exactly maintenance-free either.  Also, that only
works with the current statically-planned approach to partitioned
tables.  I think where we're trying to go with partitioning is that
the planner doesn't consider the individual partitions, but the executor
just hits the right one at runtime --- so cost modifiers attached to
individual partitions aren't going to work in that environment.

The most practical solution for his case still seems to be to twiddle
some GUC or other locally in the maintenance scripts that do the
full-table-scan queries.  Unfortunately we don't have an equivalent
of per-session SET (much less SET LOCAL) for per-relation attributes.
Not sure if we want to go there.

            regards, tom lane


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

От: Ben
Дата:
Сообщение: Re: equivalent queries lead to different query plans for self-joins with group by?
От: Jon Nelson
Дата:
Сообщение: Re: postmaster consuming /lots/ of memory with hash aggregate. why?