优化数组的首先N项的查询匹配

Question

我现在正在使用一个Postgres，里面装满了国际象棋游戏数据，每个游戏都是一个“记录”表中的一排。玩家的移动和(可选的)计算机对这些移动的评估都有自己的列，并存储为数组。

我编写了一个查询来检索指定的开始移动序列的所有评估。(你可能认为计算机评估是一致的，但事实并非如此。)开放序列的长度是任意的-它可能是一个移动，它可能是30。

下面是一个示例查询，它发现所有游戏都是从相同的十移动开始顺序开始，然后，对于每个具有评估值的游戏，返回计算机对游戏中该点的评估-

SELECT evaluation[10]
FROM records
WHERE moves[1:10]::text[] = ARRAY['b4', 'e5', 'Bb2', 'd6', 'Nf3', 'Nf6', 'g3', 'Bg4', 'Bg2', 'h5']::text[]
AND evaluation IS NOT NULL;

我不确定这是否相关，但移动数据始终是2-6个字符之间的字母数字字符串，计算机计算值主要是小数(正数和负数)，但也包括偶尔出现的特殊字符(强制检查官的前缀为八进制)。

这是表描述的相关片段-

     Column      |              Type              | 
-----------------+--------------------------------+-
 id              | bigint                         | 
 moves           | character varying(255)[]       |  
 evaluation      | character varying(255)[]       | 
    "records_pkey" PRIMARY KEY, btree (id)
Access method: heap

以下是解释分析的查询计划：

 Gather  (cost=1000.00..736354.70 rows=905 width=516) (actual time=28251.267..28253.139 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on records  (cost=0.00..735264.20 rows=377 width=516) (actual time=28243.233..28243.234 rows=0 loops=3)
         Filter: ((evaluation IS NOT NULL) AND ((moves[1:10])::text[] = '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}'::text[]))
         Rows Removed by Filter: 971361
 Planning Time: 8.275 ms
 Execution Time: 28253.915 ms

这太慢了，但我不知道如何优化它--在Postgres问题上，我不是专家，我所有建立索引的尝试都没有改变查询计划。

一些附加的想法

因为我的开场顺序总是从游戏开始--我可能想在移动1到3或者移动1到30上匹配，但从来没有，比如说，从7到15 --我也考虑过将移动数据存储为空格分隔的文本字符串，并与字符串的开头进行匹配。(我也不知道如何优化该查询，但这可能会更容易一些。)

虽然这些当前是字符串数组，但我可能会将移动和计算都表示为整数数组。(这不是我想要的，但是优化这个查询更重要，如果它有帮助，我会这么做的。)

你认为如何？我该从哪里开始？

Answer 1

您需要index支持才能快速完成。不需要重新设计你的桌子就很棘手。下面的解决方案应该执行得很好(微秒而不是秒)，但是需要一些技巧。系好安全带。

表达式索引在IMMUTABLE函数

上的应用

只需要几个领先的数组元素，比如8，这应该是非常有选择性的。更多只会使指数更大，而不需要额外的过滤。

转换为字符串。没有分离器。这允许假阳性，但不太可能重要。最后，我们过滤准确的结果无论如何。

索引表达式中只允许使用IMMUTABLE函数。但是array_to_string()只是STABLE，而不是IMMUTABLE，因为它采用anyarray，而且某些元素类型没有不变的文本表示。我们只处理text (嗯，varchar(255)无正当理由，但都一样)，这实际上是不可变的。但array_to_string()不知道这点。

这样我们就可以“伪造”一个不变的包装函数。对于任何能够创建函数的用户来说，这都是可能的：

CREATE OR REPLACE FUNCTION public.f_8moves(text[])
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT array_to_string($1[1:8], '')
$func$;

或更好的是，直接基于底层C函数为array_to_string()的text[]输入定义一个诚实的D14变体。更快更干净。为了清楚起见，我们把它称为array_to_string_immutable()。它需要超级用户特权：

-- SET ROLE postgres;  -- you must be superuser

CREATE OR REPLACE FUNCTION public.array_to_string_immutable(text[], text)
 RETURNS text
 LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS 'array_to_text';

其余的工作没有超级用户特权。

CREATE OR REPLACE FUNCTION public.f_8moves(text[])
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT public.array_to_string_immutable($1[1:8], '')
$func$;

相关信息：

• PostgreSQL是否支持“重音不敏感”排序规则？

无论哪种方法，我们现在都有一个函数public.f_8moves(text[])用于以下索引：

CREATE INDEX records_8moves_idx ON records (public.f_8moves(moves) COLLATE "C");

COLLATE "C"正是我们所需要的允许左锚定(您所表达的需求) LIKE表达式。请参见：

• 文本之间有什么区别吗？_模式_行动和整理“C”？
• 当需要有运算符类的索引时，唯一索引比唯一约束更好。

如果一个主要的行百分比有evaluation IS NOT NULL，将该筛选器添加到索引中，使其成为顶部的部分索引：

CREATE INDEX records_8moves_idx ON records (public.f_8moves(moves) COLLATE "C")
WHERE evaluation IS NOT NULL;

查询

对于具有<#>10数组元素或更多的查询：

SELECT evaluation[10]
FROM   records
WHERE  public.f_8moves(moves) = public.f_8moves('{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}') COLLATE "C"
AND    moves[1:10] = '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}'
AND    evaluation IS NOT NULL;

需要COLLATE "C"来匹配索引。

使用表达式public.f_8moves(moves)来匹配函数索引。表达式的右侧可以直接表示为字符串。但为了方便起见，我使用了相同的功能。

然后添加原始的精确筛选器，将结果缩小到精确匹配：

AND    moves[1:10] = '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}'

看起来是多余的，逻辑上是多余的，但允许使用索引产生很大的效果。

对于小于8元素的数组(我们的索引引线)，或者通常是，使用带有左锚定模式的LIKE：

SELECT evaluation[10]
FROM   records
WHERE  public.f_8moves(moves) LIKE (public.f_8moves('{b4,e5}') || '%') COLLATE "C"  -- COLLATE "C" is optional for LIKE
AND    moves[1:2] = '{b4,e5}'
AND    evaluation IS NOT NULL;

db<>fiddle 这里

类似的，还有更多的解释：

• 索引最大行大小误差
• 在Postgres或CouchDB中进行全文搜索？

集群表行

通常情况下，对表行进行物理排序将有助于性能，这样每个查询都可以从一个或几个数据页读取结果，而不是从各地获取结果。

使用CLUSTER或非阻塞替代pg_repack或pg_squeeze。更多在链接在上面。