join tutorial: table joined on itself
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/tutorial-join.html Description: Thanks for the effort for this wonderful tutorial. The last table that illustrates joining a table on itself shows that San Fransisco has `temp_lo` lower than itself and `temp_hi` higher than itself, without including the dates of the temperatures. I believe it would be clearer (data-wise) if the table included the dates fields `W1.date as date` and `W2.date as date`, making the whole example output look as follows ``` SELECT W1.city, W1.date as date, W1.temp_lo AS low, W1.temp_hi AS high W2.city, W2.date as date, W2.temp_lo AS low, W2.temp_hi AS high FROM weather W1, weather W2 WHERE W1.temp_lo < W2.temp_lo AND W1.temp_hi > W2.temp_hi; city | date | low | high | city | low | high ---+-+-+--+---+-+-- San Francisco | 1994-11-29 | 43 | 57 | San Francisco | 1994-11-27 | 46 | 50 Hayward | 1994-11-29 | 37 | 54 | San Francisco | 1994-11-27 | 46 | 50 (2 rows) ```
effective_cache_size
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/runtime-config-query.html Description: Hi, The effective_cache_size documentation (section 19.7) is: "When setting this parameter you should consider both PostgreSQL's shared buffers and the portion of the kernel's disk cache that will be used for PostgreSQL data files.". This description seems essentially stable since v8.1. As far as I understand it means that the value associated to effective_cache_size is the value of the shared_buffers plus the amount of memory often used by the kernel for the buffercache (in Linux's parlance). This explanation is offered by many published documents, for example https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#effective_cache_size Alas, this seems different to what B. Momjian declared in 2012 ( https://www.enterprisedb.com/node/3374 ): "The value doesn't have to be perfect, but just a rough estimate of how much kernel memory is acting as secondary cache for the shared buffers.", which (as far as I understand) means "effective_cache_size = (approx of the often-observed buffercache size)". In the sourcecode v11b3) the module src/backend/utils/misc/guc.c contains "Sets the planner's assumption about the size of the disk cache. That is, the portion of the kernel's disk cache that will be used for PostgreSQL data files." (so effective_cache_size is more or less the "buffercache" size). However in the module src/backend/optimizer/path/costsize.c a comment states that 'We also use a rough estimate "effective_cache_size" of the number of disk pages in Postgres + OS-level disk cache." (so effective_cache_size is more or less the size of the shared_buffers + the size of the "buffercache"). This seems contradictory to me. Thank you!
