sql - postgres大表选择优化

Question

我必须将数据库提取到外部数据库服务器以获得许可软件。数据库必须是 Postgres，我无法从应用程序更改选择查询(无法更改源代码)。

表(必须是 1 个表)包含大约 650 万行，并且在主列(前缀)中具有唯一值。

所有请求都是读取请求，没有插入/更新/删除请求，每天大约有 20 万次选择，峰值为 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;

解释分析显示如下

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,6s(仅测试这部分查询)

部分查询单独测试:

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

关于数据本身:“前缀”列具有相同的前几位数字(前 5 位)，其余的是不同的、唯一的。

Postgres 版本是 9.5我更改了 Postgres 的以下设置:

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

我尝试了几种索引类型(unique、gin、gist、hash)，但在所有情况下都没有使用索引(如上文所述)并且结果速度相同。我也做过，但没有明显的改进:

vacuum analyze verbose table

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

当前硬件是i5、SSD、Win7 上的 16GB 内存，但我可以选择购买更强大的硬件。据我了解，对于读取(无插入/更新)占主导地位的情况，更快的 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

使用并行序列。扫描？

Answer 1

很难为像 strin LIKE pattern 这样的查询创建索引，因为通配符(% 和 _)无处不在。

我可以建议一个有风险的解决方案:

1. 稍微重新设计表格 - 使其可索引。再添加两列固定宽度的 prefix_low 和 prefix_high - 例如 char(32)，或任何足以完成任务的任意长度。还为前缀长度添加一个 smallint 列。用匹配前缀和前缀长度的最低和最高值填充它们。例如:

   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. 用这些值建立索引

   CREATE INDEX table_prefix_low_high_idx ON table (prefix_low, prefix_high);
   

3. 对照表检查修改后的请求:

   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
   

   检查它与索引的配合情况，尝试调整它 - 添加/删除 prefix_length 的索引，将其添加到索引之间等等。

4. 现在您需要将查询重写到数据库中。安装 PgBouncer 和 PgBouncer-RR patch .它允许您使用简单的 python 代码即时重写查询，例如示例:

   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. 运行 pgBouncer 并将其连接到数据库。尝试像您的应用程序一样发出不同的查询，并检查它们是如何被重写的。因为您处理的是文本，所以您必须调整正则表达式以匹配所有必需的查询并正确重写它们。

6. 当代理准备就绪并经过调试后，将您的应用程序重新连接到 pgBouncer。

临:

• 应用程序没有变化
• DB的基本结构没有变化

反对:

• 额外维护 - 您需要触发器来保持所有新列包含实际数据
• 额外的支持工具
• rewrite 使用正则表达式，因此它与您的应用程序发出的特定查询密切相关。您需要运行它一段时间并制定稳健的重写规则。

进一步发展: pgsql 自身中的 highjack 解析查询树 https://wiki.postgresql.org/wiki/Query_Parsing