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 -0000 1.289 --- src/sgml/runtime.sgml 4 Nov 2004 16:29:12 -0000 *************** *** 1666,1675 **** <listitem> <para> 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 <productname>PostgreSQL</productname> ! data files). This is measured in disk pages, which are ! normally 8192 bytes each. The default is 1000. </para> </listitem> </varlistentry> --- 1666,1684 ---- <listitem> <para> 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 <productname>PostgreSQL</productname> ! 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. </para> </listitem> </varlistentry>
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 -0000 1.135 --- optimizer/path/costsize.c 4 Nov 2004 16:26:10 -0000 *************** *** 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 -0000 1.246 --- utils/misc/guc.c 4 Nov 2004 16:26:16 -0000 *************** *** 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]