postgresql-effective_cache_size参数详解

在 PostgreSQL 中，effective_cache_size 是一个配置参数，用于告诉查询规划器关于系统中可用缓存的估计信息。这个参数并不表示实际的内存量，而是用于告诉 PostgreSQL 查询规划器系统中可用的磁盘缓存和操作系统级别的文件系统缓存的大小。它用于帮助 PostgreSQL 优化器估计特定查询所需的成本，以选择最佳的查询计划。

effective_cache_size 参数的设置可以在 PostgreSQL 的配置文件 (postgresql.conf) 中进行，也可以使用 ALTER SYSTEM 命令在运行时动态修改，但需要重新加载 PostgreSQL 的配置以使更改生效。

这个参数的值通常设置为系统可用缓存的大致大小。一般情况下，你可以设置为系统可用内存的一部分，但不要超过系统实际可用内存的大小。

例如：

effective_cache_size = 4GB

这个设置表示 PostgreSQL 会假设系统中大约有 4GB 的缓存可用于数据块的读取。这个值的设置可以因系统的实际硬件和负载情况而异，需要进行一些试验和性能测试来确定最佳的值。

调整 effective_cache_size 可以影响查询优化器对索引扫描、排序和连接等操作的成本估计，进而影响 PostgreSQL 执行计划的选择。

记住，修改这些参数时最好在测试环境中进行，并且需要仔细监控系统的性能变化，以确定最佳的设置。

这个参数主要用于Postgre查询优化器。是单个查询可用的磁盘高速缓存的有效大小的一个假设，是一个估算值，它并不占据系统内存。由于优化器需要进行估算成本，较高的值更有可能使用索引扫描，较低的值则有可能使用顺序扫描。一般这个值设置为内存的1/2是正常保守的设置，设置为内存的3/4是比较推荐的值。通过free命令查看操作系统的统计信息，您可能会更好的估算该值。

调整effective_cache_size参数可以控制Postgresql必须在buffer cache中提取查询数据的频率，从而提升查询性能。

1. 案例一

以下转载自：本文来自博客园，作者：[abce](https://www.cnblogs.com/abclife/)，转载请注明原文链接：[https://www.cnblogs.com/abclife/p/14565691.html](https://www.cnblogs.com/abclife/p/14565691.html)

优化器假设可以用于单个查询的磁盘缓存的有效大小。这个因素会被用到使用索引的成本考虑中：值越大，使用索引扫描的可能性就越大；值越小，使用顺序扫描的可能性就越大。
设置该参数的时候，需要同时考虑到shared buffer和内核对磁盘缓存的使用，尽管有些数据会同时存在shared buffer和内核的磁盘缓存中。同时要考虑到在不同的表上并发查询的数量，因为他们也会使用到共享空间。
该参数不会影响分配给postgresql的共享内存，也不保留内核磁盘缓存。只是用于优化器的评估目的。系统也不会假设不同查询之间的数据保留在磁盘缓存上。默认是4GB。

指定值的时候，如果不指定unit，默认就是block。

select name, setting, unit from pg_settings where name like 'effective_cache_size';

name	setting	unit
effective_cache_size	524288	8kB

成本评估要考虑很多因素：i/o数量、操作调用次数、处理的元组的数量、选择性等等。但是i/o的成本是什么呢？很显然，如果数据已经在cache中或数据在磁盘上，代价显然是不同的。

参数effective_cache_size就是用来告诉优化器，系统可以提供多大的cache。这里的cache不仅仅是内存的cache，也考虑了文件系统cache、cpu的cache等。effective_cache_size是这些cache的总和。

postgres=# create table t_random as select id,random() as r from generate_series(1,1000000) as id order by random();
SELECT 1000000

postgres=# create table t_ordered as select id,random() as r from generate_series(1,1000000) AS id;
SELECT 1000000

postgres=# create index idx_random on t_random(id);
CREATE INDEX

postgres=# create index idx_ordered on t_ordered(id);
CREATE INDEX

postgres=# vacuum analyze t_random;
VACUUM

postgres=# vacuum analyze t_ordered;
VACUUM

postgres=#

两个表都包含相同的数据，一个表是有序的，一个是无序的。

将effective_cache_size设置一个较小的值。优化器会认为系统的内存不是很多：

postgres=# set effective_cache_size to '1 MB';
SET

postgres=# show effective_cache_size;
 effective_cache_size
----------------------
 1MB
(1 row)

 
postgres=# set enable_bitmapscan to on;
SET

postgres=# explain SELECT * FROM t_random WHERE id < 1000;
                                 QUERY PLAN                                
----------------------------------------------------------------------------
 Bitmap Heap Scan on t_random  (cost=19.71..2453.44 rows=940 width=12)
   Recheck Cond: (id < 1000)
   ->  Bitmap Index Scan on idx_random  (cost=0.00..19.48 rows=940 width=0)
         Index Cond: (id < 1000)
(4 rows)
 
postgres=# set enable_bitmapscan to off;
SET

postgres=# explain SELECT * FROM t_random WHERE id < 1000;
                                   QUERY PLAN                                   
---------------------------------------------------------------------------------
 Index Scan using idx_random on t_random  (cost=0.42..3732.86 rows=940 width=12)
   Index Cond: (id < 1000)
(2 rows)
 
postgres=#

通常pg会走bitmap索引扫描，但是这里我们想看看索引扫描会发生什么。所以关闭了bitmap索引扫描。

postgres=# SET effective_cache_size TO '1000 GB';
SET

postgres=# explain SELECT * FROM t_random WHERE id < 1000;
                                   QUERY PLAN                                   
---------------------------------------------------------------------------------
 Index Scan using idx_random on t_random  (cost=0.42..3488.86 rows=940 width=12)
   Index Cond: (id < 1000)
(2 rows)
 
postgres=#

可以看到，索引扫描的成本降低了。

我们必须把成本看作是“相对的”。绝对的数字并不重要——重要的是一个计划与其他计划相比有多贵。
如果顺序扫描的成本保持不变，而索引扫描的价格相对于顺序扫描下降了，PostgreSQL会更倾向于索引。这正是effective_cache_size的核心内容:在有大量RAM的情况下，更有可能进行使用索引扫描。

当谈及如何配置postgres.conf文件中的effective_cache_size的设置的时候，往往没有意识到并不会有什么神奇的效果。

postgres=# set effective_cache_size to '1 MB';
SET
postgres=# explain SELECT * FROM t_ordered WHERE id < 1000;
                                   QUERY PLAN                                   
---------------------------------------------------------------------------------
 Index Scan using idx_ordered on t_ordered  (cost=0.42..38.85 rows=996 width=12)
   Index Cond: (id < 1000)
(2 rows)
 
postgres=# SET effective_cache_size TO '1000 GB';
SET
postgres=# explain SELECT * FROM t_ordered WHERE id < 1000;
                                   QUERY PLAN                                   
---------------------------------------------------------------------------------
 Index Scan using idx_ordered on t_ordered  (cost=0.42..38.85 rows=996 width=12)
   Index Cond: (id < 1000)
(2 rows)
 
postgres=#

优化器使用的表统计信息包含关于物理“相关性”的信息。如果相关性是1，即所有数据是有序的在磁盘上。effective_cache_size并不会改变什么。

如果只有一个列，同样也不会有什么效果：

postgres=# ALTER TABLE t_random DROP COLUMN r;
ALTER TABLE
postgres=# SET effective_cache_size TO '1 MB';
SET
postgres=# explain SELECT * FROM t_random WHERE id < 1000;
                                    QUERY PLAN                                    
-----------------------------------------------------------------------------------
 Index Only Scan using idx_random on t_random  (cost=0.42..28.88 rows=940 width=4)
   Index Cond: (id < 1000)
(2 rows)
 
postgres=# SET effective_cache_size TO '1000 GB';
SET
postgres=# explain SELECT * FROM t_random WHERE id < 1000;
                                    QUERY PLAN                                    
-----------------------------------------------------------------------------------
 Index Only Scan using idx_random on t_random  (cost=0.42..28.88 rows=940 width=4)
   Index Cond: (id < 1000)
(2 rows)
 
postgres=#

调优建议：

effective_cache_size = RAM * 0.7

如果是pg专用服务器，也可以考虑设置为RAM*0.8。

2. 案例二

effective_cache_size 是一个用于告诉 PostgreSQL 查询规划器关于系统中可用缓存的估计信息的配置参数。它的设置影响着 PostgreSQL 查询优化器对于不同查询计划的成本估算，从而影响了 PostgreSQL 查询优化器选择最佳执行计划的方式。

下面是一个使用 effective_cache_size 参数的案例：

假设你有一个数据库，其中包含大量的表和数据。在某次查询中，你需要从一个包含大量数据的表中检索信息，比如一个产品销售表。

SELECT * FROM sales_data WHERE product_id = 'XYZ';

在没有充足的缓存情况下，数据库可能需要频繁地从磁盘读取数据块以满足查询需求，这会增加查询的执行时间。

通过配置 effective_cache_size 参数，你可以告诉 PostgreSQL 查询优化器关于系统中可用缓存的估计信息，从而影响查询优化器的成本估算，这可能导致选择不同的查询执行计划。

假设你的系统有足够的内存，并且已经对 effective_cache_size 进行了适当的设置，PostgreSQL 查询优化器会认为系统中有更多的缓存可用。在这种情况下，它可能更倾向于选择全表扫描而不是索引扫描，因为它认为大部分数据已经在缓存中，并且全表扫描可能更快。

然而，如果 effective_cache_size 设置得太小，查询优化器会认为系统中的缓存较少，可能会倾向于选择索引扫描，因为它认为从磁盘中读取少量数据会更快。

因此，effective_cache_size 参数的调整会影响查询执行计划的选择。合理设置这个参数可以帮助 PostgreSQL 优化器更准确地估计查询成本，选择更有效的执行计划，提高查询性能。

需要注意的是，这个参数的最佳设置取决于实际的硬件资源、负载和数据库工作负载等因素。通常需要进行实际测试和性能监控来确定最佳的配置值。

2.1. 如果字段上增加了索引也不一定

在正常情况下，如果 product_id 字段上存在合适的数据索引，PostgreSQL 应该会倾向于使用该索引来执行与 product_id 相关的查询。使用索引可以大大提高查询性能，因为它允许 PostgreSQL 快速定位到符合条件的数据行，而不必进行全表扫描。

然而，即使存在索引，有时候 PostgreSQL 也可能会选择不使用索引而是进行全表扫描。这种情况可能出现在以下情况下：

1. 成本估算错误： 如果查询优化器错误地估计了索引扫描和全表扫描的成本，它可能会选择全表扫描而不是索引扫描。这种情况下，调整 effective_cache_size 参数可能会影响优化器的估算，使其更倾向于选择索引扫描。

2. 索引不适用： 在某些情况下，即使存在索引，PostgreSQL 也可能选择不使用索引。例如，如果查询条件不是索引的前缀，或者查询条件使用了函数，这可能导致索引无法被使用。此外，如果统计信息不准确或者索引被损坏，也可能导致索引不被选择。

3. 数据分布不均匀： 如果数据分布不均匀，即索引列上的值分布不均匀，有时候优化器可能会认为全表扫描更有效率，尤其是当需要检索大部分数据行时。

在这些情况下，虽然有索引但可能仍然会进行全表扫描。这种情况下可以考虑重新分析索引、收集统计信息、优化查询语句或者调整 PostgreSQL 的配置参数来影响查询规划器的行为。

总的来说，虽然索引可以大幅提高查询性能，但 PostgreSQL 的查询规划器决定执行计划的选择不仅取决于索引的存在，还受到多种因素的影响。合适地配置索引、收集统计信息、优化查询语句以及调整配置参数都是优化查询性能的重要步骤。