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

f4t66c6m 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(2)|浏览(148)

在新硬件上将Posterre从8.3.8升级到9.4.1。一组具有代表性的查询显示，新系统的性能提高了1到3倍。然而，我们的一个高负载区域总是较慢。

输出

第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（当然触发了不同的计划）
是的，第8.3页的计划使用了索引和位图索引扫描--我认为这是这个问题的“坚果”。
感谢您提供相关文章的链接。
关于列顺序的建议非常有趣。
1.在我们的规模和增长中，以下模式的最佳字段顺序是什么？
1.在已加载表上重新组织列顺序以实现此好处的最有效方法是什么？
底漆具有：

integer
text
boolean
boolean
integer
integer
smallint
integer
timestamp without time zone
timestamp without time zone
timestamp without time zone
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;

的内存

在该方法中未观察到可测量的差异，相同的计划（+/-5msec）
我们通常会先尝试使用prime来检查acl、status等，以缩小在data中搜索的记录的范围。（质数为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!)

型
为了处理未锚定的情况，我们有一个使用操作符类“text_pattern_ops”的第二个索引。
我们以前评估过多列GIN索引，但没有实现预期的好处。复杂的原因是：A）acl、status和类似项需要满足多个标准，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”的索引，很抱歉，这一点最初并不清楚。

postgresql

来源：https://stackoverflow.com/questions/29137610/bitmap-scan-in-postgres-8-3-2x-faster-than-index-scan-postgres-9-4

2条答案

按热度按时间

hrirmatl1#

坏查询计划的最常见原因是列统计信息或成本设置不能很好地表示实际情况：

避免PostgreSQL有时选择错误的查询计划

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

SET enable_bitmapscan = off;

字符串
第9.4页中的当前会话，然后再次测试。棘手的部分是，您的查询可能需要两者：索引-和位图索引扫描。我需要查看查询。
random_page_cost的极低设置有利于索引扫描而不是位图索引扫描。如果该成本设置具有误导性，则会得到较差的查询计划。
dba.SE上对这个相关问题的回答有更多的解释：

Pagination with PostgreSQL 9.3: counting number of pages

表格设计

表的设计很简单，但通常最好不要在整数列之间放置布尔列 *，因为这样会浪费磁盘空间进行填充。更好：

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

型

在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的左锚模式匹配。您的表达式未被锚定。（我看到你也在使用锚定模式。）为此，使用一个三元组GIN索引，由额外的模块pg_trgm提供，它在大表中 * 非常 * 快，特别是在第9.4页（改进的GIN索引）。
PostgreSQL LIKE查询性能变化
Pattern matching with LIKE, SIMILAR TO or regular expressions的

索引

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

CREATE EXTENSION btree_gin;

型
做一些假设，这将是完美的你的查询。data上的多列三元组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;

型
在这里谈论数量级。

赞(0）回复(0）举报 2023-08-04

lqfhib0f2#

一种稍微不同的方法，首先通过Common Table Expression（CTE）从“数据表”中产生最大可能的结果集，然后返回到prime以通过acl，status等进行细化，将时间从365毫秒减少到142毫秒（节省223毫秒）。该技术似乎比8.3基线更快。

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

字符串
计划时间：1.417毫秒
执行时间：141.508毫秒
我会进一步评估慢性创伤性脑水肿是否有意想不到的影响。

赞(0）回复(0）举报 2023-08-04

我来回答

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

输出

索引

设置

声明

表定义

新测试结果

2条答案

表格设计

查询

索引

相关问题

热门标签

最新问答