Effective_cache_size:
Set the planners assumption about
the effective size of the disk cache that is available to a single query. This
is factored into estimates of the cost of using an index. A higher value makes
it more likely index scans will be used, a lower value makes it more likely
sequential scans will be used. 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, though some data might exist in
both places. Also take into account the expected number of concurrent queries
on different tables, since they will have to share the available space. This
parameter has no effect on the size of shared memory allocated by PostgreSQL,
nor does it reserve kernel disk cache, it is used only for estimation purposes.
The system also doest not assume data remains in the disk cache between
queries. The default is 4 gigabytes (4GB).
The same rough of thumb that would put
shared_buffers at 25% of system memory would set effective_cache_size to
between 50% and 75% of RAM. To get a more accurate estimate, first observe the
size of the file system cache.
The Practical impact
of effective_cache_size:-
The cost of this query is 0.43.. 99923664
we set effective_cache_size to 10000 MB size then execute the query statement.
Now you can see the 0.43..109179.59
