Postgres 不使用索引来实现慢速功能

在我的数据库设计中，用到了很多函数。而且其中许多都非常慢。因此，我认为在其中一些上创建索引以使执行速度更快一点可能是一个明智的想法。 然而，我没有成功说服 PostgreSQL (9.6) 实际使用我的索引。

考虑这个表“用户”

id integer | name jsonb
1          | {"last_names": ["Tester"], "first_names": ["Teddy","Eddy"]} 
2          | {"last_names": ["Miller"], "first_names": ["Lisa","Emma"]}   

通常，我需要将名称作为一个字符串，这是通过类似（称为“concat_name”）的查询来完成的

SELECT array_to_string(jsonb_arr2text_arr(name->'last_names'), ' ') || ', ' || array_to_string(jsonb_arr2text_arr(name->'first_names'), ' ');

我决定将该功能放入一个函数中，因为它用于多个表：

CREATE OR REPLACE FUNCTION public.concat_name(name jsonb)
  RETURNS text AS
$BODY$
  SELECT pg_sleep(50);
  SELECT array_to_string(jsonb_arr2text_arr(name->'last_names'), ' ') || ', ' || array_to_string(jsonb_arr2text_arr(name->'first_names'), ' ');
$BODY$
  LANGUAGE sql IMMUTABLE SECURITY DEFINER
  COST 100;

你看，为了实际测试它是否有效，我添加了“人为”超时。 现在，我创建了一个索引，例如：

CREATE INDEX user_concat_name_idx ON "user" (concat_name(name));

它成功并花费了预期的时间（因为 pg_sleep）。然后我运行一个查询：

SELECT concat_name(name) FROM "user";

但是，索引没有被使用，查询速度非常慢。反而，EXPLAIN告诉我刨床对“用户”进行序列扫描。

我做了一些研究，很多人指出查询规划器认为，如果表很小或检索的数据集（几乎）是整个表，它认为进行序列扫描比查找索引更有效。 然而，对于功能，尤其是慢速功能，这对我来说没有任何意义。即使您查询仅包含一行的表 - 如果您的查询包含每次需要 50 秒执行的函数，则使用函数索引也可以显着减少执行时间。

因此，在我看来，查询规划器必须比较查找索引值所需的时间与执行函数所需的时间。表或查询本身的大小（返回多少行）在这里根本不重要。而且，如果函数执行需要 50 秒，那么查找索引应该总是获胜。

那么，我可以在这里做什么来让查询规划器使用索引而不是每次都重新执行该函数呢？

首先，索引(id, concat_name(name))如果您想在仅选择的查询中使用它，则毫无意义concat_name(name)。索引应该是：

create index user_concat_name_idx on "user" (concat_name(name));

其次，索引将在需要时使用，例如当你添加order by concat_name(name):

explain analyse
select concat_name(name)
from "user"
order by 1;

                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using user_concat_name_idx on "user"  (cost=0.42..29928.42 rows=100000 width=82) (actual time=0.157..1046.168 rows=100000 loops=1)
 Planning time: 0.753 ms
 Execution time: 1048.862 ms
(3 rows)

此外，您可以使您的函数更简单、更快：

create or replace function concat_name(name jsonb)
returns text language sql immutable as $$
    select concat_ws(', ',
        (select string_agg(value, ' ')
        from jsonb_array_elements_text(name->'last_names')),
        (select string_agg(value, ' ')
        from jsonb_array_elements_text(name->'first_names'))
    )
$$;

我可以在这里做什么来使查询规划器使用索引而不是每次都重新执行该函数？

您应该声明该函数的更大成本，例如：

create or replace function concat_name(name jsonb)
returns text language sql immutable as $$
-- ...
$$
cost 1000;

Per 文档 https://www.postgresql.org/docs/current/static/sql-createfunction.html:

执行成本

一个正数，给出函数的估计执行成本，以 cpu_operator_cost 为单位。如果函数返回一个集合，则这是每个返回行的成本。如果未指定成本，则假定 C 语言和内部函数为 1 个单位，所有其他语言的函数为 100 个单位。较大的值会导致规划器尝试避免不必要地频繁地评估函数。