The comment describing cost_nonsequential_access() says that the two
functions "meet in the middle".  They meet at random_page_cost/2,
however, not in the middle between 1 and random_page_cost.  For
random_page_cost < 2 the result can be less than 1 for relpages near
effective_cache_size.  I don't think that this was intended.

This patch makes sure that cost_nonsequential_access() is always between
1 and randon_page_cost and the functions meet a (1+random_page_cost)/2.

Servus
 Manfred
diff -Ncr ../base/src/backend/optimizer/path/costsize.c 
src/backend/optimizer/path/costsize.c
*** ../base/src/backend/optimizer/path/costsize.c       Tue Jun  1 05:02:52 2004
--- src/backend/optimizer/path/costsize.c       Tue Jun  8 08:34:27 2004
***************
*** 189,199 ****
   * for now by assuming we are given an effective_cache_size parameter.
   *
   * Given a guesstimated cache size, we estimate the actual I/O cost per page
!  * with the entirely ad-hoc equations:
   *    if relpages >= effective_cache_size:
!  *            random_page_cost * (1 - (effective_cache_size/relpages)/2)
   *    if relpages < effective_cache_size:
!  *            1 + (random_page_cost/2-1) * (relpages/effective_cache_size) ** 2
   * These give the right asymptotic behavior (=> 1.0 as relpages becomes
   * small, => random_page_cost as it becomes large) and meet in the middle
   * with the estimate that the cache is about 50% effective for a relation
--- 189,200 ----
   * for now by assuming we are given an effective_cache_size parameter.
   *
   * Given a guesstimated cache size, we estimate the actual I/O cost per page
!  * with the entirely ad-hoc equations (writing rpc for random_page_cost and
!  * relsize for relpages/effective_cache_size):
   *    if relpages >= effective_cache_size:
!  *            rpc - (rpc-1)/2 * 1/relsize
   *    if relpages < effective_cache_size:
!  *            1 + (rpc-1)/2 * relsize
   * These give the right asymptotic behavior (=> 1.0 as relpages becomes
   * small, => random_page_cost as it becomes large) and meet in the middle
   * with the estimate that the cache is about 50% effective for a relation
***************
*** 213,221 ****
        relsize = relpages / effective_cache_size;
  
        if (relsize >= 1.0)
!               return random_page_cost * (1.0 - 0.5 / relsize);
        else
!               return 1.0 + (random_page_cost * 0.5 - 1.0) * relsize * relsize;
  }
  
  /*
--- 214,222 ----
        relsize = relpages / effective_cache_size;
  
        if (relsize >= 1.0)
!               return random_page_cost - (random_page_cost - 1.0) / 2.0 / relsize;
        else
!               return 1.0 + (random_page_cost - 1.0) / 2.0 * relsize;
  }
  
  /*
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to