Postgres表函数的索引结果

Question

我希望创建一个Postgres函数，可以缓存长时间运行查询的结果，这样每个事务只需要执行一次：

CREATE OR REPLACE FUNCTION get_records (
  _state VARCHAR(10)
)
RETURNS TABLE (
  id UUID
) AS
$$
DECLARE
  _temptable VARCHAR;
BEGIN
  _temptable := FORMAT('temp_state_%s', _state);

  IF NOT EXISTS(SELECT 1 FROM pg_tables WHERE tablename = _temptable) THEN
    EXECUTE FORMAT('CREATE TEMPORARY TABLE %I (id UUID NOT NULL, PRIMARY KEY(_uid)) ON COMMIT DROP', _temptable);
    EXECUTE FORMAT('INSERT INTO %I SELECT id FROM very_complex_nested_query', _temptable);
    EXECUTE FORMAT('ANALYZE %I', _temptable);
  END IF;

  RETURN QUERY EXECUTE FORMAT('SELECT id FROM %I', _temptable);
END;
$$
LANGUAGE 'plpgsql';

现在，我可以运行所有的查询并连接这个函数：

SELECT mt.*
FROM my_table1 mt
  INNER JOIN get_records('new') nr ON mt.id = nr.id

SELECT mt.*
FROM my_table2 mt
  INNER JOIN get_records('new') nr ON mt.id = nr.id

SELECT mt.*
FROM my_table3 mt
  INNER JOIN get_records('new') nr ON mt.id = nr.id

-- ... many more

我有一堆这样的东西，没有保证哪个会先运行或以什么顺序运行。

这个方法运行得相当好，但是临时表上的主键索引没有被使用。

如何从Postgres函数中返回一个“表”，而不仅仅是查询结果？

1. 我正在使用一个函数来构建临时表，而不是使用材料化视图来解决“聚合视图中的where子句不起作用”问题。
2. 我可以创建临时表，然后直接引用它在所有的查询中，但这意味着必须构建某种阻塞机制，以确保查询不会过早地执行，而我使用的工具不太支持这样的机制。

- Monkey Boson

---

不要创建临时表，也不要使用临时表。不要分析临时表。将“very_complex_nested_query”放入一个普通的sql函数中。使用该sql函数的查询可以比PL/pgSQL呈现给优化器的黑匣子更好地进行优化。 - user330315

@a_horse_with_no_name 但是...“very_complex_nested_query”需要5分钟才能执行，由于我将结果连接到数百个单独的查询中，因此会增加总执行时间。这难道不是使用临时表的确切用例吗？你所说的“纯SQL函数”是什么意思？ - Monkey Boson

我是指使用 语言sq 而不是 语言plpgsql - 但如果你想给它创建索引，你需要在临时表上创建一个索引。 - user330315

@a_horse_with_no_name 你如何使用普通SQL创建一个动态命名的临时表并在同一查询中加入它？我无法保证查询的顺序，因此临时表可能会在任何查询中被创建，而我不想多次创建它。我希望实现一种“缓存未命中：执行工作、缓存并返回”的设计。 - Monkey Boson

SQL函数将仅包含SELECT语句，没有临时表。您仍然可以以相同的方式使用它，但Postgres能够优化整个查询，包括函数内部的查询。类似于这样：http://dpaste.com/1J5REFJ - user330315

Answer 1

您可以尝试使用修饰符STABLE，该修饰符表示：

STABLE表示该函数不能修改数据库，对于相同的参数值，在单个表扫描中它将始终返回相同的结果，但是其结果可能在SQL语句之间发生变化。

新版本的Postgres还支持材料化视图。您可以为连接创建一个材料化视图。据我所知，材料化视图也支持索引。

- clemens

---

通过返回在FROM子句中使用的表，无论如何都只会扫描一次，对吧？在任何情况下，STABLE和IMMUTABLE似乎都没有暴露临时表的底层索引。至于物化视图，问题在于如果你正在进行聚合操作，则无法从检查约束（例如分区表中）中受益，这将导致扫描所有分区（我的5分钟查询变成了18小时的查询）-当我看到一个有一万行的查询计划时，这真是个惊喜！ - Monkey Boson