performance - Postgres 8.3 中的位图扫描比索引扫描 Postgres 9.4 快 2 倍？

Question

在新硬件上将 Postgres 从 8.3.8 升级到 9.4.1。一组有代表性的查询表明，新系统的性能范围提高了 1 倍到 3 倍。但是，我们的高负载区域之一总是较慢。

EXPLAIN 输出

8.3.8:

Nested Loop  (cost=25.78..709859.61 rows=1 width=4) (actual time=14.972..190.591 rows=32 loops=1)
  ->  Bitmap Heap Scan on prime p  (cost=25.78..1626.92 rows=1066 width=4) (actual time=1.567..9.597 rows=10742 loops=1)
        Recheck Cond: ((pid = ANY ('{28226,53915,83421,82118397,95513866}'::integer[])) AND (tid = ANY ('{1,2,3}'::integer[])))
        Filter: (NOT deleted)
        ->  Bitmap Index Scan on FOO_IDX1  (cost=0.00..25.73 rows=1066 width=0) (actual time=1.144..1.144 rows=10742 loops=1)
              Index Cond: ((pid = ANY ('{28226,53915,83421,82118397,95513866}'::integer[])) AND (deleted = false) AND (tid = ANY ('{1,2,3}'::integer[])))
  ->  Index Scan using FOO_IDX2 on data d  (cost=0.00..663.88 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=10742)
        Index Cond: (d.pid = p.pid)
        Filter: (lower("substring"(d.value, 1, 1000)) ~~ '%something%'::text)
Total runtime: 190.639 ms

9.4.1:

Nested Loop  (cost=1.15..335959.94 rows=1 width=4) (actual time=24.712..365.057 rows=32 loops=1)
  ->  Index Scan using FOO_IDX1 on prime p  (cost=0.57..953.17 rows=1033 width=4) (actual time=0.048..13.884 rows=10741 loops=1)
        Index Cond: ((pid = ANY ('{28226,53915,83421,82118397,95513866}'::integer[])) AND (deleted = false) AND (tid = ANY ('{1,2,3}'::integer[])))
        Filter: (NOT deleted)
  ->  Index Scan using FOO_IDX2 on data d  (cost=0.57..324.29 rows=1 width=4) (actual time=0.032..0.032 rows=0 loops=10741)
        Index Cond: (pid = p.pid)
        Filter: (lower("substring"(value, 1, 1000)) ~~ '%something%'::text)
        Rows Removed by Filter: 11
Planning time: 0.940 ms
Execution time: 365.156 ms

索引

…btree (pid);
…btree (lower("substring"(value, 1, 1000)) text_pattern_ops, fid);
…btree (lower("substring"(value, 1, 1000)), fid);

设置

改变以下范围并没有改善这种情况……

checkpoint_completion_target = 0.5
checkpoint_segments = 32
checkpoint_timeout = 30min
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
cpu_tuple_cost = 0.01
default_statistics_target = 500 (evaluated 100 to 10000 analyse after each)
effective_cache_size = 288GB
enable_seqscan = off
from_collapse_limit = 8
geqo = off
join_collapse_limit = 8
random_page_cost = 1.0
seq_page_cost = 1.0
shared_buffers = 96GB
work_mem = 64MB

我们也看到了 something% 的类似结果。

在我们将其投入几年之前，我想知道我是否可以做更多的事情来优化这些重要案例。

声明

SELECT p.pid
FROM prime p
    INNER JOIN data d ON p.pid = d.pid
WHERE LOWER(substring(d.value,1,1000)) LIKE '%something%'
    AND p.tid IN (1,2,3)
    AND p.deleted = FALSE
    AND p.ppid IN (28226, 53915, 83421, 82118397, 95513866)  

表定义

简化和净化。

\d prime

    Column     |            Type             |                    Modifiers
---------------+-----------------------------+-------------------------------------------------
 pid           | integer                     | not null default nextval('prime_seq'::regclass)
 deleted       | boolean                     |
 ppid          | integer                     |
 tid           | integer                     |

\d data

     Column     |  Type   |                      Modifiers
----------------+---------+------------------------------------------------------
 pdid           | integer | not null default nextval('data_seq'::regclass)
 pid            | integer |
 value          | text    |

新的测试结果

我尝试了一系列 default_statistics_target。

default_statistics_target = 100  @ 381 ms
default_statistics_target = 500  @ 387 ms
default_statistics_target = 1000 @ 384 ms
default_statistics_target = 5000 @ 369 ms

(在测试周期之间进行分析和预热)

这个值可以对我们应用程序的其他领域产生重大影响。 500 似乎是理想的，5000+ 导致其他区域减速 3 到 10 倍。

---

我们的工具包的设计使得整个数据库应该始终在内存中。

random_page_cost =  1.0 @ 372 ms
random_page_cost =  1.1 @ 372 ms 
random_page_cost =  4.0 @ 370 ms 
random_page_cost = 10.0 @ 369 ms

---

使用 enable_bitmapscan = off @ 362 ms(结果与预期的计划相同)

早些时候我也尝试过 enable_indexscan = off @ 491 ms(当然触发了不同的计划)

是的，pg 8.3 的计划使用索引和位图索引扫描——我认为这是这个问题的“核心”。

感谢您提供指向相关文章的链接。

---

关于列顺序的建议非常有趣。

1. 在我们的规模和不断增长的情况下，以下模式的最佳字段顺序是什么？

2. 重组加载表上的列顺序以实现 yield 的最有效方法是什么？

主要有:

integer
text
boolean
boolean
integer
integer
smallint
integer
timestamp without time zone
timestamp without time zone
timestamp without time zone
text

数据有:

integer
integer
integer
text

---

SELECT pid
FROM data d
  JOIN prime p USING (pid)
WHERE LOWER(substring(d.value,1,1000)) LIKE '%something%'
  AND p.ppid IN (28226, 53915, 83421, 82118397, 95513866) 
  AND p.tid IN (1, 2, 3)
  AND p.deleted = FALSE;

• 在此方法中未观察到可测量的差异，相同的计划(+/-5 毫秒)
• 我们一般会先尝试缩小数据中搜索记录的范围，通过使用prime来检查acl、status等(prime是大小的1/10)

---

lower(substring(d.value,1,1000)) = 355 ms
lower(left(d.value,1000))        = 343 ms (~3% faster over multiple tests, I'll take that!)

---

为了处理 unanchored 情况，我们有第二个索引，使用运算符类“text_pattern_ops”。

我们之前评估过多列 GIN 索引，但没有实现预期的好处。复杂，因为 A) 在 acl、状态等方面满足多个标准，B) 需要命中“精确短语”，这需要重新检查结果短语。我对长期使用全文方法持乐观态度，到目前为止我们尝试过的方法并不比老派的 BTREE 方法；还没有。

Gin 试验 1

CREATE EXTENSION btree_gin
CREATE INDEX FOO_IDX3 ON data USING GIN (to_tsvector('simple', lower(left(value, 1000))), pid)
ANALYSE data

SELECT p.pid
FROM prime p
  INNER JOIN data d ON p.pid = d.pid
WHERE to_tsvector('simple', lower(left(d.value, 1000))) @@ to_tsquery('simple', 'something')
  AND p.tid IN (1,2,3)
  AND p.deleted = FALSE
  AND p.ppid IN (28226, 53915, 83421, 82118397, 95513866)

Execution time: 1034.866 ms (without phrase recheck)

Gin 试验 2

CREATE EXTENSION pg_trgm 
CREATE INDEX FOO_IDX4 ON data USING gin (left(value,1000) gin_trgm_ops, pid);
ANALYSE data

SELECT p.pid
FROM prime p
  INNER JOIN data d ON p.pid = d.pid
WHERE left(d.value,1000) LIKE '%Something%'
  AND p.tid IN (1,2,3)
  AND p.deleted = FALSE
  AND p.ppid IN (28226, 53915, 83421, 82118397, 95513866)


Hash Join  (cost=2870.42..29050.89 rows=1 width=4) (actual time=668.333..2262.101 rows=32 loops=1)
  Hash Cond: (d.pid = p.pid)
  ->  Bitmap Heap Scan on data d  (cost=230.30..26250.04 rows=25716 width=4) (actual time=653.130..2234.736 rows=38659 loops=1)
        Recheck Cond: ("left"(value, 1000) ~~ '%Something%'::text)
        Rows Removed by Index Recheck: 146677
        Heap Blocks: exact=161810
        ->  Bitmap Index Scan on FOO_IDX4  (cost=0.00..223.87 rows=25716 width=0) (actual time=575.442..575.442 rows=185336 loops=1)
              Index Cond: ("left"(value, 1000) ~~ '%Something%'::text)
  ->  Hash  (cost=2604.33..2604.33 rows=2863 width=4) (actual time=15.158..15.158 rows=10741 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 378kB
        ->  Index Scan using FOO_IDX4 on prime p  (cost=0.57..2604.33 rows=2863 width=4) (actual time=0.064..11.737 rows=10741 loops=1)
              Index Cond: ((ppid = ANY ('{28226,53915,83421,82118397,95513866}'::integer[])) AND (deleted = false) AND (tid = ANY ('{1,2,3}'::integer[])))
              Filter: (NOT deleted)
Planning time: 1.861 ms
Execution time: 2262.210 ms

---

我们已经有一个关于 prime 的索引 w/“ppid、deleted、tid”，抱歉，这最初并不清楚。

Answer 1

糟糕的查询计划最常见的原因是统计数据或成本设置不能很好地代表现实:

• Keep PostgreSQL from sometimes choosing a bad query plan

random_page_cost = 1.0 的设置只有在随机访问实际上与顺序访问一样快的情况下才有意义，这只有在您的数据库完全驻留在 RAM 中时才成立。具有 80M 和 750M 行表的数据库可能太大了。如果我的假设是正确的，稍微提高成本设置可能会解决问题。 至少 1.1 尝试，可能更多。运行测试以找到设置中的最佳点。

通常我会先运行:

SET enable_bitmapscan = off;

在 pg 9.4 的当前 session 中，然后再次测试。棘手的部分是您的查询可能需要:索引扫描和位图索引扫描。我需要查看查询。

random_page_cost 的极低设置有利于索引扫描而不是位图索引扫描。如果该成本设置具有误导性，您将获得劣质查询计划。

dba.SE 上这个相关问题的答案有更多解释:

• Pagination with PostgreSQL 9.3: counting number of pages

表格设计

您的表设计已简化，但通常最好不要在整数列之间放置 bool 列*，因为这样会浪费磁盘空间来填充。更好:

pid           | integer  | not null default nextval('prime_seq'::regclass)
tid           | integer  | 
deleted       | boolean  |

• Calculating and saving space in PostgreSQL

这只是一个小改进，但没有任何缺点。

查询

可以通过多种方式改进:

SELECT pid
FROM   data  d
JOIN   prime p USING (pid)
WHERE  left(d.value,1000) LIKE '%something%'
AND    p.pid IN (28226, 53915, 83421, 82118397, 95513866) 
AND    p.tid IN (1, 2, 3)
AND    p.deleted = FALSE;

• left(d.value,1000) 比 substring(d.value,1,1000) 更短更快(需要 pg 9.1+)。

• text_pattern_ops 索引仅对使用 LIKE 的左锚定模式匹配有用。你的表情没有固定下来。 (我看到你也在使用锚定模式。)为此，使用附加模块 pg_trgm 提供的三元组 GIN 索引。这对于大表来说非常更快，尤其是在 pg 9.4 中(改进的 GIN 索引)。

  • PostgreSQL LIKE query performance variations
  • Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

指数

要在下面的 GIN 索引中包含 integer 列 pid，首先安装附加模块 btree_gin ，它提供了必要的 GIN 运算符类。 每个数据库运行一次:

CREATE EXTENSION btree_gin;

做出一些假设，这将非常适合您的查询。 data 上的多列 trigram GIN 索引:

CREATE INDEX data_value_gin_trgm_idx ON data
USING gin (left(value,1000) gin_trgm_ops, pid);

prime 上的部分多列索引:

CREATE INDEX prime_pid_tip_idx ON prime (pid, tip)
WHERE  deleted = FALSE;

在这里谈论数量级。