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

От: Bruce Momjian
Тема: Re: anti-join chosen even when slower than old plan
Дата: ,
Msg-id: 201101192003.p0JK3Jb27891@momjian.us
(см: обсуждение, исходный текст)
Ответ на: 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)
Список: 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", )

Robert Haas wrote:
> On Thu, Nov 11, 2010 at 2:35 PM, Tom Lane <> wrote:
> > Robert Haas <> writes:
> >> Yeah. ?For Kevin's case, it seems like we want the caching percentage
> >> to vary not so much based on which table we're hitting at the moment
> >> but on how much of it we're actually reading.
> >
> > Well, we could certainly take the expected number of pages to read and
> > compare that to effective_cache_size. ?The thing that's missing in that
> > equation is how much other stuff is competing for cache space. ?I've
> > tried to avoid having the planner need to know the total size of the
> > database cluster, but it's kind of hard to avoid that if you want to
> > model this honestly.
>
> I'm not sure I agree with that.  I mean, you could easily have a
> database that is much larger than effective_cache_size, but only that
> much of it is hot.  Or, the hot portion could move around over time.
> And for reasons of both technical complexity and plan stability, I
> don't think we want to try to model that.  It seems perfectly
> reasonable to say that reading 25% of effective_cache_size will be
> more expensive *per-page* than reading 5% of effective_cache_size,
> independently of what the total cluster size is.

Late reply, but one idea is to have the executor store hit counts for
later use by the optimizer.  Only the executor knows how many pages it
had to request from the kernel for a query.  Perhaps getrusage could
tell us how often we hit the disk.

--
  Bruce Momjian  <>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +


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

От: Craig Ringer
Дата:
Сообщение: Re: Migrating to Postgresql and new hardware
От: "Charles.Hou"
Дата:
Сообщение: Re: the XID question