主页 / dba / 问题 / 310208
Accepted
Ron Piggott
Asked: 2022-03-27 17:56:12 +0800 CST

选择给定数组中包含字母数组的行

  • 772

text[]我想在“拼写”( ) 栏中 搜索字母： annoyt

我需要它来查找单词：any , annoy , no , an , toy 但找不到： annoy 的派生词（烦恼，烦恼），只找到一次。

如果甚至缺少一个字母（例如anoyt），我还需要查询 NOT find annoy 。

我正在使用 PostgreSQL 13.5

ronshome=# SELECT reference, word, spelling FROM word_mash_dictionary
           WHERE word LIKE 'annoy';
 reference | word  |  spelling
-----------+-------+-------------
       420 | annoy | {a,n,n,o,y}
(1 row)

这是表结构：

                                                      Table "public.word_mash_dictionary"
   Column    |  Type  | Collation | Nullable |                         Default                         | Storage  | Stats target | Description
-------------+--------+-----------+----------+---------------------------------------------------------+----------+--------------+-------------
 reference   | bigint |           | not null | nextval('word_mash_dictionary_reference_seq'::regclass) | plain    |              |
 word        | text   |           |          |                                                         | extended |              |
 spelling    | text[] |           |          |                                                         | extended |              |
 ignore      | bigint |           |          |                                                         | plain    |              |
 list_100    | bigint |           |          |                                                         | plain    |              |
 list_300    | bigint |           |          |                                                         | plain    |              |
 list_500    | bigint |           |          |                                                         | plain    |              |
 list_800    | bigint |           |          |                                                         | plain    |              |
 list_1000   | bigint |           |          |                                                         | plain    |              |
 list_2000   | bigint |           |          |                                                         | plain    |              |
 list_3000   | bigint |           |          |                                                         | plain    |              |
 list_5000   | bigint |           |          |                                                         | plain    |              |
 list_7000   | bigint |           |          |                                                         | plain    |              |
 list_10000  | bigint |           |          |                                                         | plain    |              |
 word_length | bigint |           |          |                                                         | plain    |              |
postgresql select

1 个回答

  1. Best Answer

    数组的“包含”运算符<@ 主要是这样做的：

    SELECT reference, word, spelling
FROM   word_mash_dictionary
WHERE  spelling <@ '{a,n,n,o,y,t}'::text[];

    这可以通过数组上的 GIN 索引来支持，这使得它对于大表来说很快。喜欢：

    CREATE INDEX ON word_mash_dictionary USING gin (spelling);

    但是，搜索数组中的一个元素涵盖spelling. 所以'{a,n,o,y}'会发现'{a,n,n,o,y}'等。对于带有重复字母的单词的误报。

    一个集合操作EXCEPT ALL将是精确的（分别考虑相同元素的每个副本）。包装成一个自定义函数：

    CREATE OR REPLACE FUNCTION f_arr_is_contained(arr1 text[], arr2 text[])
  RETURNS bool
  LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
DECLARE
BEGIN
   PERFORM unnest(arr1) EXCEPT ALL SELECT unnest(arr2);

   RETURN NOT FOUND;
END
$func$;

    如果每个字母都包含在第二个术语中，则不返回任何行，并且FOUND为 false。所以返回NOT FOUND。

    我之所以选择LANGUAGE plpgsql该函数，是因为无论如何该函数都不能“内联”，因此 plpgsql 可能会更快。您可以使用以下方法测试等效替代方案LANGUAGE plpgsql：

    CREATE OR REPLACE FUNCTION f_arr_is_contained_sql(arr1 text[], arr2 text[])
  RETURNS bool
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT NOT EXISTS (SELECT unnest (arr1) EXCEPT ALL SELECT unnest (arr2))';

    但是，该函数不能使用任何索引，这将导致对整个表进行昂贵的顺序扫描。

    将两者结合起来既快速又准确：

    SELECT reference, word, spelling
FROM   word_mash_dictionary
WHERE  spelling <@ '{a,n,o,y,t}'::text[]
AND    f_arr_is_contained(spelling, '{a,n,o,y,t}'::text[]);

    db<>在这里摆弄

    第一个谓词在索引支持下快速找到所有匹配项（可能还有一些误报）；第二个谓词消除了（少数！）误报。

    撇开，word并且spelling可能应该被宣布NOT NULL。

    • 2

相关问题