在PostgreSQL中启用查询缓存以提高性能。

Question

我的应用程序非常依赖于数据库，因此我正在努力减轻数据库的负载。我使用PostgreSQL作为关系型数据库管理系统，使用Python编程语言。

为了减轻负载，我已经在应用程序中使用了缓存机制。我使用的缓存类型是服务器缓存、浏览器缓存。

目前，我正在调整PostgreSQL查询缓存，使其与服务器上运行的查询特性相符。

问题：

1. 是否可以对每个数据库级别进行查询缓存微调？
2. 是否可以对每个表格基础进行查询缓存微调？
3. 请提供学习PostgreSQL查询缓存的教程。

- iDhavalVaja

---

如果您正在寻找Oracle中可用的那种微调功能，答案是 - 没有。但是这里有一些链接，您可能想要阅读http://www.postgresql.org/docs/current/static/pgprewarm.html https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server https://wiki.postgresql.org/wiki/Performance_Optimization - Jayadevan

Answer 1

调整PostgreSQL不仅仅是调整缓存。实际上，主要的高级事项是"共享缓存"(将其视为主数据和索引缓存)和work_mem。

共享缓存有助于读写。你需要给它一个合适的大小，但它是针对整个集群的...你不能真正按表或特别查询基础来调整它。重要的是，它并没有真正存储查询结果...它存储的是表、索引和其他数据。在符合ACID标准的数据库中，缓存查询结果并不是非常高效或有用。

"work_mem"用于在内存中排序查询结果，而不必写入磁盘...根据你的查询，这个区域可能与缓冲区缓存一样重要，并且更容易调整。在运行需要进行较大排序的查询之前，可以发出如"SET work_mem = '256MB'"之类的设置命令。

正如其他人建议的那样，你可以使用"explain"找出为什么查询运行缓慢。我个人建议学习postgresql获取数据的"访问路径"。这比简单地考虑"缓存结果"更加复杂，也更加有效。

你也可以通过数据设计以及使用分区、函数索引和其他技术等功能来显著改善性能。

还有一件事情是，通过编写更好的查询，你可以获得更好的性能。例如，"with"子句可以防止postgresql优化器完全优化查询。

优化器本身也有一些可以调整的参数——因此，数据库将在执行查询之前花费更多(或更少)的时间来优化查询...这可能会产生差异。

你还可以使用某些技术来编写查询以帮助优化器。其中一种技术是使用绑定变量(冒号变量)——这将导致优化器反复获取相同的查询，并传递不同的数据。这样，结构就不必反复评估...查询计划可以以这种方式被缓存。

如果没有看到一些查询、表和索引设计以及解释计划，很难提出具体建议。

总的来说，你需要找出不如你认为应该的那么有效的查询，并找出争用的地方。可能是磁盘访问，然而，原因最终是最重要的部分...它是否必须去磁盘上进行排序？它是否在内部选择了一个不良路径来获取数据，以至于它正在读取可以在查询过程中更早被消除的数据...我已经成为Oracle认证的DBA超过20年了，PostgreSQL确实不同，然而，当涉及到诊断查询的性能问题时，许多相同的技术被使用。虽然你不能真正提供提示，但你仍然可以重写查询或调整某些参数以获得更好的性能...总的来说，我发现postgresql在长期调整中更容易。如果你能提供一些具体信息，比如查询和解释信息，我很乐意给你一些具体的建议。不幸的是，"缓存调优"可能会为你提供你想要的速度。

- Joe Love

Answer 2

我开发了一个缓存结果的系统，加速从基于Web的解决方案查询结果。以下是其主要功能：

以下是通用缓存处理表和函数。

CREATE TABLE cached_results_headers (
  cache_id serial NOT NULL PRIMARY KEY,
  date timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_access timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  relid regclass NOT NULL,
  query text NOT NULL,
  rows int NOT NULL DEFAULT 0
  );
CREATE INDEX ON cached_results_headers (relid, md5(query));

CREATE TABLE cached_results (
  cache_id int NOT NULL,
  row_no int NOT NULL  
  );

CREATE OR REPLACE FUNCTION f_get_cached_results_header (p_cache_table text, p_source_relation regclass, p_query text, p_max_lifetime interval, p_clear_old_data interval) RETURNS cached_results_headers AS $BODY$
DECLARE
  _cache_id int;
  _rows int;
BEGIN
  IF p_clear_old_data IS NOT NULL THEN
    DELETE FROM cached_results_headers WHERE date < CURRENT_TIMESTAMP - p_clear_old_data;
  END IF;

  _cache_id := cache_id FROM cached_results_headers WHERE relid = p_source_relation AND md5(query) = md5(p_query) AND query = p_query AND date > CURRENT_TIMESTAMP - p_max_lifetime;
  IF _cache_id IS NULL THEN
    INSERT INTO cached_results_headers (relid, query) VALUES (p_source_relation, p_query) RETURNING cache_id INTO _cache_id;
    EXECUTE $$ INSERT INTO $$||p_cache_table||$$ SELECT $1, row_number() OVER (), r.r FROM ($$||p_query||$$) r $$ USING _cache_id;
    GET DIAGNOSTICS _rows = ROW_COUNT;
    UPDATE cached_results_headers SET rows = _rows WHERE cache_id = _cache_id;
  ELSE
    UPDATE cached_results_headers SET last_access = CURRENT_TIMESTAMP;
  END IF;
  RETURN (SELECT h FROM cached_results_headers h WHERE cache_id = _cache_id);
END;
$BODY$ LANGUAGE PLPGSQL SECURITY DEFINER;

以下是如何使用上述表格和函数的示例，针对一个名为my_view的视图，其中选择一个key字段，在整数值范围内进行筛选。您需要根据自己的需求替换所有以下内容，并将my_view替换为表、视图或函数。还需根据需要替换过滤参数。

CREATE VIEW my_view AS SELECT ...; -- create a query with your data, with one of the integer columns in the result as "key" to filter by

CREATE TABLE cached_results_my_view (
  row my_view NOT NULL,
  PRIMARY KEY (cache_id, row_no),
  FOREIGN KEY (cache_id) REFERENCES cached_results_headers ON DELETE CASCADE
  ) INHERITS (cached_results);

CREATE OR REPLACE FUNCTION f_get_my_view_cached_rows (p_filter1 int, p_filter2 int, p_row_from int, p_row_to int) RETURNS SETOF my_view AS $BODY$
DECLARE
  _cache_id int;
BEGIN
  _cache_id := cache_id 
    FROM f_get_cached_results_header('cached_results_my_view', 'my_view'::regclass,
                                     'SELECT r FROM my_view r WHERE key BETWEEN '||p_filter1::text||' AND '||p_filter2::text||' ORDER BY key',
                                     '15 minutes'::interval, '1 day'::interval); -- cache for 15 minutes max since creation time; delete all cached data older than 1 day old

  RETURN QUERY
    SELECT (row).*
    FROM cached_results_my_view
    WHERE cache_id = _cache_id AND row_no BETWEEN p_row_from AND p_row_to
    ORDER BY row_no;
END;
$BODY$ LANGUAGE PLPGSQL;

示例：从缓存的my_view结果中检索从1到2000行，过滤条件为key BETWEEN 30044 AND 10610679。第一次运行时，查询结果将被缓存到表cached_results_my_view中，并返回前2000条记录。之后不久再次运行该查询，结果将直接从表cached_results_my_view中检索，而无需执行查询。

SELECT * FROM f_get_my_view_cached_rows(30044, 10610679, 1, 2000);

- Ezequiel Tolnay

---

恭喜您。您刚刚开发了物化视图。 - pensnarik

@pensnarik，这个解决方案与物化视图完全无关（在2016年还不可用于PG）。如果您愿意的话，它相当于重复读隔离级别中的游标。 - Ezequiel Tolnay