PostgreSQL大表查询优化

Question

我需要将数据库提取到外部数据库服务器上，用于授权软件。 数据库必须是Postgres，而且我不能更改应用程序中的选择查询（无法更改源代码）。

表格（必须为1个表格）包含大约650万行，并在主列（前缀）中具有唯一值。

所有请求均为读取请求，没有插入/更新/删除操作，每天有大约200,000个选择操作，峰值为15 TPS。

选择查询为：

SELECT prefix, changeprefix, deletelast, outgroup, tariff FROM table 
WHERE '00436641997142' LIKE prefix 
AND company = 0  and ((current_time between timefrom and timeto) or (timefrom is null and timeto is null)) and (strpos("Day", cast(to_char(now(), 'ID') as varchar)) > 0  or "Day" is null )  
ORDER BY position('%' in prefix) ASC, char_length(prefix) DESC 
LIMIT 1;

Explain analyze 显示如下

Limit  (cost=406433.75..406433.75 rows=1 width=113) (actual time=1721.360..1721.361 rows=1 loops=1)
  ->  Sort  (cost=406433.75..406436.72 rows=1188 width=113) (actual time=1721.358..1721.358 rows=1 loops=1)
        Sort Key: ("position"((prefix)::text, '%'::text)), (char_length(prefix)) DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Seq Scan on table  (cost=0.00..406427.81 rows=1188 width=113) (actual time=1621.159..1721.345 rows=1 loops=1)
              Filter: ((company = 0) AND ('00381691997142'::text ~~ (prefix)::text) AND ((strpos(("Day")::text, (to_char(now(), 'ID'::text))::text) > 0) OR ("Day" IS NULL)) AND (((('now'::cstring)::time with time zone >= (timefrom)::time with time zone) AN (...)
              Rows Removed by Filter: 6417130
Planning time: 0.165 ms
Execution time: 1721.404 ms`

查询中最慢的部分是：

 SELECT prefix, changeprefix, deletelast, outgroup, tariff FROM table 
 WHERE '00436641997142' LIKE prefix 

这段代码生成了1.6秒的执行时间（仅测试此查询的这一部分）

单独测试的查询部分：

Seq Scan on table  (cost=0.00..181819.07 rows=32086 width=113) (actual time=1488.359..1580.607 rows=1 loops=1)
  Filter: ('004366491997142'::text ~~ (prefix)::text)
  Rows Removed by Filter: 6417130
Planning time: 0.061 ms
Execution time: 1580.637 ms

关于数据本身： 列"prefix"具有相同的前几位数字（前5位），其余是不同的，唯一的。

Postgres版本为9.5 我已更改以下Postgres设置：

random-page-cost = 40
effective_cashe_size = 4GB
shared_buffer = 4GB
work_mem = 1GB

我尝试了几种索引类型（unique、gin、gist、hash），但在所有情况下，索引都没有被使用（如上所述的explain语句），结果速度相同。 我也做了一些尝试，但没有明显的改进：

vacuum analyze verbose table

请推荐数据库和/或索引配置的设置，以加快此查询的执行时间。

当前硬件为i5、SSD、16GB RAM在Win7上，但我有购买更强大硬件的选项。据我所知，在读取（无插入/更新）占主导地位的情况下，更快的CPU核心比核心数量或磁盘速度更重要，请确认。

附加1： 添加了9个索引后，索引仍未被使用。

附加2： 1）我发现不使用索引的原因是查询中的单词顺序问题。如果查询如下：

SELECT prefix, changeprefix, deletelast, outgroup, tariff FROM table WHERE prefix like '00436641997142%'
AND company = 0  and 
((current_time between timefrom and timeto) or (timefrom is null and timeto is null)) and (strpos("Day", cast(to_char(now(), 'ID') as varchar)) > 0  or "Day" is null )
 ORDER BY position('%' in prefix) ASC, char_length(prefix) DESC LIMIT 1

它使用索引。

注意区别：

... WHERE '00436641997142%' like prefix ...

正确使用索引的查询：

... WHERE prefix like '00436641997142%' ...

由于我无法更改查询本身，有什么方法可以克服这个问题吗？我可以更改数据和Postgres设置，但无法更改查询本身。

2）此外，我安装了Postgres 9.6版本，以使用并行seq.scan。在这种情况下，只有当查询的最后一部分被省略时才会使用并行扫描。因此，查询如下：

SELECT prefix, changeprefix, deletelast, outgroup, tariff FROM table WHERE '00436641997142' LIKE prefix 
AND company = 0  and 
((current_time between timefrom and timeto) or (timefrom is null and timeto is null))
 ORDER BY position('%' in prefix) ASC, char_length(prefix) DESC LIMIT 1

使用并行模式。

有什么办法可以强制执行原始查询（我无法更改查询）：

SELECT prefix, changeprefix, deletelast, outgroup, tariff FROM erm_table WHERE '00436641997142' LIKE prefix 
AND company = 0  and 
((current_time between timefrom and timeto) or (timefrom is null and timeto is null)) and (strpos("Day", cast(to_char(now(), 'ID') as varchar)) > 0  or "Day" is null )
 ORDER BY position('%' in prefix) ASC, char_length(prefix) DESC LIMIT 1

如何使用并行顺序扫描？

- positive

---

你使用 like 而不是 = 有什么原因吗？ - Abelisto

我无法更改它>它在源代码中，我无法访问。使用“=”而不是“like”的查询速度快3倍，但我无法更改它。 - positive

random-page-cost = 40 为什么这么高？在 SSD 上... - wildplasser

默认值是4，我增加到8来看是否有任何差异。基本上响应时间对于4或8都是相同的。 - positive

它返回多少行？尝试优化查询的这一部分。我不知道如何优化像const LIKE column这样的条件（而不改变查询/数据结构/应用程序逻辑）。 - Abelisto

Answer 1

对于像 string LIKE pattern 这样的查询，要创建索引太困难了，因为通配符（%和_）可以出现在任何地方。

我可以提供一个有风险的解决方案：

1. Slightly redesign the table - make it indexable. Add two more column prefix_low and prefix_high of fixed width - for example char(32), or any arbitrary length enough for the task. Also add one smallint column for prefix length. Fill them with lowest and highest values matching prefix and prefix length. For example:

   select rpad(rtrim('00436641997142%','%'), 32, '0') AS prefix_low, rpad(rtrim('00436641997142%','%'), 32, '9') AS prefix_high, length(rtrim('00436641997142%','%')) AS prefix_length;
   
          prefix_low                 |               prefix_high             |   prefix_length
   ----------------------------------+---------------------------------------+-----
    00436641997142000000000000000000 | 00436641997142999999999999999999      |   14
   

2. Make index with these values

   CREATE INDEX table_prefix_low_high_idx ON table (prefix_low, prefix_high);
   

3. Check modified requests against table:

   SELECT prefix, changeprefix, deletelast, outgroup, tariff 
   FROM table 
   WHERE '00436641997142%' BETWEEN prefix_low AND prefix_high
     AND company = 0  
     AND ((current_time between timefrom and timeto) or (timefrom is null and timeto is null)) and (strpos("Day", cast(to_char(now(), 'ID') as varchar)) > 0  or "Day" is null )
   ORDER BY prefix_length DESC 
   LIMIT 1
   

   Check how well it works with indexes, try to tune it - add/remove index for prefix_length add it to between index and so on.

4. Now you need to rewrite queries to database. Install PgBouncer and PgBouncer-RR patch. It allows you rewrite queries on-fly with easy python code like in example:

   import re
   
   def rewrite_query(username, query):
      q1=r"""^SELECT [^']*'(?P<id>\d+)%'[^'] ORDER BY (?P<position>\('%' in prefix\) ASC, char_length\(prefix\) LIMIT """
      if not re.match(q1, query):
         return query  # nothing to do with other queries
      else:
         new_query = # ... rewrite query here
      return new_query
   

5. Run pgBouncer and connect it to DB. Try to issue different queries like your application does and check how they are getting rewrited. Because you deal with text you have to tweak regexps to match all required queries and rewrite them properly.

6. When proxy is ready and debugged reconnect your application to pgBouncer.

优点：

• 不需要更改应用程序
• 不需要更改数据库的基本结构

缺点：

• 额外的维护 - 需要触发器来保持所有新列的实际数据
• 需要额外的工具支持
• 使用正则表达式重写与特定查询紧密相关，需要运行一段时间并制定强健的重写规则。

进一步的发展：

在pgsql本身中劫持解析的查询树 https://wiki.postgresql.org/wiki/Query_Parsing

- Eugene Lisitsky

---

非常感谢您的想法和答案，这将对我有很大帮助。我会实施并测试它；但基本上这个想法是完美的。谢谢。 - positive

不需要为前缀长度创建额外的列，可以为其创建索引。CREATE INDEX table_prefix_length_idx ON table (char_length(prefix)) - dev7532

Answer 2

如果我正确理解您的问题，创建一个重写查询的代理服务器可能是解决方案。

这里有一个来自另一个问题的示例。

然后，您可以将查询中的“LIKE”更改为“=”，它将运行得更快。

- dev7532

Answer 3

根据文档，您应该通过添加适当的操作符类来更改索引：

操作符类text_pattern_ops、varchar_pattern_ops和bpchar_pattern_ops支持类型为text、varchar和char的B-tree索引。与默认操作符类的区别在于，值是严格按字符比较而不是按特定于语言环境的排序规则比较。这使得这些操作符类适用于涉及模式匹配表达式（LIKE或POSIX正则表达式）的查询，当数据库不使用标准的“C”语言环境时。例如，您可以像这样为varchar列创建索引：

CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

- Borys

---

我刚刚添加了以下内容：CREATE INDEX test_index ON erm_table (prefix bpchar_pattern_ops);因为前缀字段是字符类型，但查询速度仍然是>1.7秒。 - positive

你放弃了旧的吗？设置 SET enable_seqscan = OFF; 用于测试。 - Borys

我已经在初始帖子中发布了分析器；基本上，结果是相同的，并且使用了顺序扫描，尽管它被关闭了（奇怪？） - positive

好的，现在我已经阅读了整个查询。问题可能出现在其他地方，例如这些表达式：(strpos("Day", cast(to_char(now(), 'ID') as varchar)) > 0 对于优化器来说是黑匣子。也许可以尝试添加一个部分索引到where子句中进行验证：((current_time between timefrom and timeto) or (timefrom is null and timeto is null)) and (strpos("Day", cast(to_char(now(), 'ID') as varchar)) > 0 or "Day" is null )。 - Borys

我完全删除了查询的那部分，性能结果相同（我在初始帖子中放置了分析器的结果，在where子句中除了前缀部分没有其他子句）。 - positive

我认为你无法创建一个引用current_time的部分索引。 - David Aldridge