Thread

1. Re: again on index usage

   Daniel Kalchev <daniel@digsys.bg> — 2002-01-11T17:05:45Z

   >>>Tom Lane said:
    > "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
    > > My preference would actually be a way to make the optimizer
    > > choose a plan that causes minimal workload, and not shortest runtime 
    > 
    > ?? I am not sure that I see the difference.
   
   There can be difference only if the optimizer takes into account already 
   executing plans (by other backends).
   
    > What I think you are saying is that when there's lots of competing work,
    > seqscans have less advantage over indexscans because the
    > sequential-access locality advantage is lost when the disk drive has to
    > go off and service some other request.
   
   This is exactly my point. The primary goal of the optimizer in my opinion 
   should be to avoid trashing. :-) Now, it is not easy to figure out when the 
   system starts trashing - at least not a portable way I can think of 
   immediately.
   
    > I don't think I'd go as far as to lower random_page_cost to 1.0, but
    > certainly there's a case for using an intermediate value.
   
   The question is: how does one find the proper value? That is, is it possible to design planner benchmarking utility to aid in tuning Postgres? One that does not execute single query and optimize on idle system.
   
   Daniel
   
   
   

2. Re: again on index usage

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-01-11T17:42:40Z

   Daniel Kalchev <daniel@digsys.bg> writes:
   >>> I don't think I'd go as far as to lower random_page_cost to 1.0, but
   >>> certainly there's a case for using an intermediate value.
   
   > The question is: how does one find the proper value? That is, is it
   > possible to design planner benchmarking utility to aid in tuning
   > Postgres?
   
   The trouble is that getting trustworthy numbers requires huge test
   cases, because you have to swamp out the effects of the kernel's own
   buffer caching.  I spent about a week running 24-hour-constant-disk-
   banging experiments when I came up with the 4.0 number we use now,
   and even then I didn't feel that I had a really solid range of test
   cases to back it up.
   
   My advice to you is just to drop it to 2.0 and see if you like the plans
   you get any better.
   
   			regards, tom lane
   
   

3. Re: again on index usage

   Bruce Momjian <pgman@candle.pha.pa.us> — 2002-01-11T18:24:20Z

   Daniel Kalchev wrote:
   > >>>Tom Lane said:
   >  > "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
   >  > > My preference would actually be a way to make the optimizer
   >  > > choose a plan that causes minimal workload, and not shortest runtime 
   >  > 
   >  > ?? I am not sure that I see the difference.
   > 
   > There can be difference only if the optimizer takes into account already 
   > executing plans (by other backends).
   > 
   >  > What I think you are saying is that when there's lots of competing work,
   >  > seqscans have less advantage over indexscans because the
   >  > sequential-access locality advantage is lost when the disk drive has to
   >  > go off and service some other request.
   > 
   > This is exactly my point. The primary goal of the optimizer in my opinion 
   > should be to avoid trashing. :-) Now, it is not easy to figure out when the 
   > system starts trashing - at least not a portable way I can think of 
   > immediately.
   
   I have always felt some feedback mechanism from the executor back to the
   optimizer was required but I was never sure quite how to implement it.
   
   -- 
     Bruce Momjian                        |  http://candle.pha.pa.us
     pgman@candle.pha.pa.us               |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
   
   

4. Re: again on index usage

   Don Baccus <dhogaza@pacifier.com> — 2002-01-11T19:29:32Z

   Bruce Momjian wrote:
   
   
   > 
   > I have always felt some feedback mechanism from the executor back to the
   > optimizer was required but I was never sure quite how to implement it.
   
   
   The folks at DEC (rdb???) wrote a paper on it a long time ago (duh, back 
   when DEC existed).   I ran across it in the Tuft's library about a year 
   ago, back when my girlfriend was in grad school.
   
   
   -- 
   Don Baccus
   Portland, OR
   http://donb.photo.net, http://birdnotes.net, http://openacs.org