主页 / dba / 问题 / 55164
Accepted
Alireza
Asked: 2013-12-18 00:55:11 +0800 CST

如何从 Postgres 中高效查询以选择特殊词？

  • 772

假设我有一个words包含很多记录的表。
列是id和name。

在words我的表中，例如：

 'systematic', 'سلام','gear','synthesis','mysterious', etc.

注意：我们也有 utf8 字。
如何有效地查询以查看哪些单词包含字母's'和'm'（'e'全部）？

输出将是：

systematic,mysterious

我不知道该怎么做。它应该是有效的，因为否则我们的服务器会受到影响。

postgresql select

3 个回答

  1. Best Answer

    一种简单的方法是考虑与每个单词对应的字母数组，并使用@>(contains) 数组运算符在其中搜索。这与手册中示例中所示的字母位置无关，即ARRAY[1,4,3] @> ARRAY[3,1]正确。

    这个数组可以很容易地用 获得regexp_split_to_array(name, '')。
    [编辑：根据@Erwin的回答，string_to_array(name, NULL)速度更快，所以更好地使用它。这是其余答案中的直接替代品]

    这是一个演示，它首先将数组具体化为包含英语和法语单词混合的测试表中的列（约 511000 行，平均长度 = 13 个字符），然后是第二个测试表，而不将数组添加为列。

    => CREATE TABLE tstword AS
    SELECT word_id as id,
    wordtext as name,
    regexp_split_to_array(wordtext, '') as arr FROM words;

    要查找相对大量的单词：

    => select count(*) from tstword where arr @> array['s','m','e'];
 count 
-------
 42268
(1 row)

Time: 268.809 ms

    如 EXPLAIN ANALYZE 所示，这将执行顺序扫描：

     explain analyze select name from tstword where arr @> array['s','m','e'];
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Seq Scan on tstword  (cost=0.00..17554.46 rows=21256 width=14) (actual time=0.020..268.525 rows=42268 loops=1)
   Filter: (arr @> '{s,m,e}'::text[])
   Rows Removed by Filter: 468729
 Total runtime: 269.927 ms
(4 rows)

Time: 270.414 ms

    但是我们可以使用 GIN 索引来索引数组：

    CREATE INDEX idx_tst on tstword using gin(arr);

    然后它更快：

    explain analyze select name from tstword where arr @> array['s','m','e'];
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tstword  (cost=252.74..11815.73 rows=21256 width=14) (actual time=46.378..60.203 rows=42268 loops=1)
   Recheck Cond: (arr @> '{s,m,e}'::text[])
   ->  Bitmap Index Scan on idx_tst  (cost=0.00..247.42 rows=21256 width=0) (actual time=45.202..45.202 rows=42268 loops=1)
         Index Cond: (arr @> '{s,m,e}'::text[])
 Total runtime: 61.677 ms
(5 rows)

Time: 70.185 ms

    我们甚至可以通过直接索引表达式来避免将数组具体化为列，因为 postgres 支持函数索引。

    create table tstword2 as select word_id as id,wordtext as name from words;
create index idx_tst2 on tstword2  using gin(regexp_split_to_array(name, ''));

    然后必须使用完全相同的表达式进行搜索，并使用索引：

     explain analyze select name from tstword2 where regexp_split_to_array(name, '') @> array['s','m','e'];
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tstword2  (cost=40.00..44.02 rows=1 width=14) (actual time=39.390..48.435 rows=42268 loops=1)
   Recheck Cond: (regexp_split_to_array((name)::text, ''::text) @> '{s,m,e}'::text[])
   ->  Bitmap Index Scan on idx_tst2  (cost=0.00..40.00 rows=1 width=0) (actual time=39.053..39.053 rows=42268 loops=1)
         Index Cond: (regexp_split_to_array((name)::text, ''::text) @> '{s,m,e}'::text[])
 Total runtime: 49.748 ms
(5 rows)

Time: 50.193 ms

    有关这些索引类型的注意事项，请参阅手册中的GiST 和 GIN 索引类型。

    • 5

  2. 测试用例

    我建立了一个半现实的测试用例：

    CREATE TABLE word(word_id int, word text);
INSERT INTO word (word_id, word)
SELECT g%(2500000/25) -- max length 25
      ,left(string_agg(chr(97 + (random()^2 * 31)::int), ''), 3 + (random()^2 * 25)::int)
FROM  (SELECT generate_series(1, 2500000) AS g) g
GROUP  BY 1
ORDER  BY 1;  --> 100k rows

    包含 3 到 25 个字母的小写单词，一些附加字符作为非 ASCII 字母的替代。较短的单词更常见，一些字母比其他字母更常见。用于random()^2获得倾斜的数据分布。

    使用这个我运行了一些测试来比较@Daniel 的方法和一些替代方法。

    word LIKE ALL(arr)

    我发现这个查询非常有效，即使没有索引：

    SELECT * FROM word
WHERE  word LIKE ALL('{%l%,%w%,%x%,%y%,%z%,%~%}'::text[]);

    为了使它工作，你可以用 . 填充你的字母%。即：a->%a%并形成一个像上面一样的数组。

    对 100k 行进行顺序扫描的速度与基于 GIN 索引的其他解决方案差不多。我还在 Postgres 9.1 上测试了 10k 和 40k 行。类似的结果。

    这将随着更大的桌子而恶化。但是，如果您的表包含 100k 行或更少（并且没有其他大列），那么这个简单的查询可能就是您所需要的。

    功能指标

    string_to_array()

    首先，使用string_to_array(word, NULL)) 代替regexp_split_to_array(word, ''). 我发现它在 Postgres 9.1 中快 5-6 倍，在 Postgres 9.3 中快 2-3 倍。主要影响索引维护，但在较小程度上也影响查询性能。

    这些表达式中的任何一个都会导致一个未排序的数组，其中可能存在重复项。

    CREATE INDEX word_arr_gin_idx ON word USING gin(string_to_array(word, NULL));

    无效变体

    从数组中删除重复项是无效的，因为 GIN 算法本身就是这样做的。引用手册：

    每个键值只存储一次，因此对于同一个键多次出现的情况，GIN 索引非常紧凑。

    无论如何，我在小提琴中包含了这个无效的变体来证明这一点并作为概念证明 - 如何IMMUTABLE在索引中使用用户定义的函数：

    CREATE OR REPLACE FUNCTION uniq_arr(text)
  RETURNS text[] AS
$func$
SELECT ARRAY(
   SELECT DISTINCT i
   FROM   unnest(string_to_array($1, NULL)) t(i))
$func$ LANGUAGE sql IMMUTABLE;

CREATE INDEX word_uniq_arr_gin_idx ON word USING gin(uniq_arr(word));

    不同的排序数组上的索引也显示相同的搜索时间，但更昂贵。我没有包括它。

    -> SQLfiddle 演示。

    折叠字母

    但是，IMMUTABLE可以使用上面演示的函数来折叠字母。一个典型的用例是使用unaccent模块é来删除重音符号（变音符号） ，即è在查找e.

    这将是一个非常有效的解决方案：

    CREATE OR REPLACE FUNCTION unaccent_arr(text) RETURNS text[] AS
$func$
SELECT string_to_array(unaccent('unaccent', $1), NULL)
$func$ LANGUAGE sql IMMUTABLE SET search_path = public, pg_temp;

CREATE INDEX word_unaccent_arr_gin_idx ON word USING gin(unaccent_arr(word));

    在这个相关答案中的详细解释（请务必阅读）：
    PostgreSQL 是否支持“不区分重音”排序规则？

    我在 Postgres 9.1 和 9.3 上进行了本地测试，但无法将其包含在小提琴中，因为我无法安装其他模块。

    • 2

  3. Postgresql 有许多索引选项。它还有一些建立在这些索引选项之上的强大组件。

    其中一项功能是全文搜索。见http://www.postgresql.org/docs/9.3/static/textsearch.html

    • 1

相关问题