I enclose a doc patch for the effective_cache_size parameter in
runtime.sgml: efcdoc.patch
Also, another minor patch which prevents effective_cache_size and
random_page_cost from being set incorrectly: plancost.patch
- previously it was possible to set effective_cache_size to 0, which
would then be ignored and treated as 1 at run-time, so set minimum to 1
and remove test at run-time.
- previously it was possible to set random_page_cost as a fractional
value between 0 and 1, which screws up planner estimation, so set
minimum value of 1 (i.e. random cost same as sequential cost).
--
Best Regards, Simon Riggs
Index: src/sgml/runtime.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.289
diff -d -c -r1.289 runtime.sgml
*** src/sgml/runtime.sgml 17 Oct 2004 22:01:49 - 1.289
--- src/sgml/runtime.sgml 4 Nov 2004 16:29:12 -
***
*** 1666,1675
Sets the planner's assumption about the effective size of the
! disk cache (that is, the portion of the kernel's disk cache
! that will be used for PostgreSQL
! data files). This is measured in disk pages, which are
! normally 8192 bytes each. The default is 1000.
--- 1666,1684
Sets the planner's assumption about the effective size of the
! disk cache that is dedicated to a particular index scan, allowing
! estimates of the cost of using an index. A higher value makes it
! more likely an index scan will be used, a lower value makes it more
! likely a sequential scan will be used. When setting this
! parameter you should consider both the PostgreSQL cache in
! shared_buffers, as well as the portion of the kernel's disk cache
! (if any) that will be used for PostgreSQL
! data files, though reducing the value as the number of concurrent
! queries increases since they cannot all be using the same cache.
! This has no effect on the size of shared memory
! allocated by PostgreSQL, nor does it reserve kernel disk cache.
! This is measured in effective disk pages, which are normally 8192
! bytes each. The default is 1000.
Index: optimizer/path/costsize.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v
retrieving revision 1.135
diff -d -c -r1.135 costsize.c
*** optimizer/path/costsize.c 23 Oct 2004 00:05:27 - 1.135
--- optimizer/path/costsize.c 4 Nov 2004 16:26:10 -
***
*** 329,335
/* This part is the Mackert and Lohman formula */
T = (baserel->pages > 1) ? (double) baserel->pages : 1.0;
! b = (effective_cache_size > 1) ? effective_cache_size : 1.0;
if (T <= b)
{
--- 329,335
/* This part is the Mackert and Lohman formula */
T = (baserel->pages > 1) ? (double) baserel->pages : 1.0;
! b = effective_cache_size;
if (T <= b)
{
Index: utils/misc/guc.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.246
diff -d -c -r1.246 guc.c
*** utils/misc/guc.c 22 Oct 2004 19:48:19 - 1.246
--- utils/misc/guc.c 4 Nov 2004 16:26:16 -
***
*** 1344,1350
"pages, which are normally 8 kB each.")
},
&effective_cache_size,
! DEFAULT_EFFECTIVE_CACHE_SIZE, 0, DBL_MAX, NULL, NULL
},
{
{"random_page_cost", PGC_USERSET, QUERY_TUNING_COST,
--- 1344,1350
"pages, which are normally 8 kB each.")
},
&effective_cache_size,
! DEFAULT_EFFECTIVE_CACHE_SIZE, 1, DBL_MAX, NULL, NULL
},
{
{"random_page_cost", PGC_USERSET, QUERY_TUNING_COST,
***
*** 1356,1362
"index scan will be used.")
},
&random_page_cost,
! DEFAULT_RANDOM_PAGE_COST, 0, DBL_MAX, NULL, NULL
},
{
{"cpu_tuple_cost", PGC_USERSET, QUERY_TUNING_COST,
--- 1356,1362
"index scan will be used.")
},
&random_page_cost,
! DEFAULT_RANDOM_PAGE_COST, 1, DBL_MAX, NULL, NULL
},
{
{"cpu_tuple_cost", PGC_USERSET, QUERY_TUNING_COST,
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
