postgresql 当需要每100行扫描时，使用索引比顺序扫描更好，但仅适用于显式值列表

7rtdyuoh  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(2)|浏览(131)

我有一个表（在RDS Postgres v. 15.4示例db.m7g.large下）：

CREATE TABLE MyTable (
    content_id integer, 
    part integer, 
    vector "char"[]
);

字符串
content_id上有一个B树索引。我的数据由1亿行组成。有1 M（0..10^6 -1）个不同的content_id值。对于content_id的每个值，part的（0..99）个值。如果content_id可被100整除且无余数，则列vector包含384字节大小的数字数组。否则为NULL。
我构建了这个人工数据来测试从Python脚本提交的以下查询的性能（稍后将清楚为什么我在Python中留下它来回答这个问题）：

query = f"""
WITH 
    Vars(key) as (
        VALUES (array_fill(1, ARRAY[{384}])::vector)
    ),
    Projection as (
        SELECT *
        FROM MyTable P
        WHERE P.content_id in ({str(list(range(0, 999999, 100)))[1:-1]})
    )
SELECT P.content_id, P.part
FROM Projection P, Vars
ORDER BY P.vector::int[]::vector <#> key
LIMIT {10};
"""

型
<#>是pgvector扩展的点积运算符，vector是该扩展定义的类型，据我所知，它类似于real[]。
注意，WHERE子句指定了content_id的10 K个值的显式列表（对应于1 M行，即表中的每第100行）。由于这个大的显式列表，我不得不将查询留在Python中，无法运行EXPLAIN ANALYZE。
上面的查询需要大约6秒的时间来执行。
但是，当我在这个查询前面加上SET enable_seqscan = off;时，查询只需要大约3秒。

**问题1：**考虑到我们需要每100行，而且大部分计算都是关于计算点积和按点积排序的，为什么顺序扫描并不比使用索引更好？（更重要的是，我不明白使用索引如何能提高2倍。）
**问题2：**如果我像下面所示的那样更改generate_series的显式值列表，这种改进为什么会消失？

WHERE content_id IN (SELECT generate_series(0, 999999, 100))

型
现在，对于后一个查询，我有EXPLAIN ANALYZE的输出：

Limit  (cost=1591694.63..1591695.46 rows=10 width=24) (actual time=6169.118..6169.125 rows=10 loops=1)
   ->  Result  (cost=1591694.63..2731827.31 rows=13819790 width=24) (actual time=6169.117..6169.122 rows=10 loops=1)
         ->  Sort  (cost=1591694.63..1626244.11 rows=13819790 width=424) (actual time=6169.114..6169.117 rows=10 loops=1)
               Sort Key: ((((p.vector)::integer[])::vector <#> '[1,1,...,1]'::vector))
               Sort Method: top-N heapsort  Memory: 34kB
               ->  Nested Loop  (cost=194.30..1293053.94 rows=13819790 width=424) (actual time=2.629..6025.693 rows=1000000 loops=1)
                     ->  HashAggregate  (cost=175.02..177.02 rows=200 width=4) (actual time=2.588..5.321 rows=10000 loops=1)
                           Group Key: generate_series(0, 999999, 100)
                           Batches: 1  Memory Usage: 929kB
                           ->  ProjectSet  (cost=0.00..50.02 rows=10000 width=4) (actual time=0.002..0.674 rows=10000 loops=1)
                                 ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
                     ->  Bitmap Heap Scan on mytable p  (cost=19.28..4204.85 rows=1382 width=416) (actual time=0.007..0.020 rows=100 loops=10000)
                           Recheck Cond: (content_id = (generate_series(0, 999999, 100)))
                           Heap Blocks: exact=64444
                           ->  Bitmap Index Scan on idx_content_on_mytable  (cost=0.00..18.93 rows=1382 width=0) (actual time=0.005..0.005 rows=100 loops=10000)
                                 Index Cond: (content_id = (generate_series(0, 999999, 100)))
 Planning Time: 0.213 ms
 Execution Time: 6169.260 ms
(18 rows)

型

更新@jjanes评论了我的第一个问题：

假设你的数据是在content_id上聚集的，你需要在每10，000行中有100个连续的行，这与需要每100行有很大的不同。
如果我理解正确的话，这意味着索引的10 K次查找中的每一次都返回一个范围，而不是100个单独的行。
以下是EXPLAIN (ANALYZE, BUFFERS)对所有三个查询的输出：
1.原始查询：

Limit  (cost=1430170.64..1430171.81 rows=10 width=16) (actual time=6300.232..6300.394 rows=10 loops=1)
   Buffers: shared hit=55868 read=436879
   I/O Timings: shared/local read=1027.617
   ->  Gather Merge  (cost=1430170.64..2773605.03 rows=11514348 width=16) (actual time=6300.230..6300.391 rows=10 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=55868 read=436879
         I/O Timings: shared/local read=1027.617
         ->  Sort  (cost=1429170.62..1443563.55 rows=5757174 width=16) (actual time=6291.083..6291.085 rows=8 loops=3)
               Sort Key: ((((p.vector)::integer[])::vector <#> '[1,1,...,1]'::vector))
               Sort Method: top-N heapsort  Memory: 25kB
               Buffers: shared hit=55868 read=436879
               I/O Timings: shared/local read=1027.617
               Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
               ->  Parallel Seq Scan on mytable p  (cost=25.00..1304760.16 rows=5757174 width=16) (actual time=1913.156..6237.441 rows=333333 loops=3)
                     Filter: (content_id = ANY ('{0,100,...,999900}'::integer[]))
                     Rows Removed by Filter: 33000000
                     Buffers: shared hit=55754 read=436879
                     I/O Timings: shared/local read=1027.617
 Planning:
   Buffers: shared hit=149
 Planning Time: 8.444 ms
 Execution Time: 6300.452 ms
(24 rows)

型
1.查询SET enable_seqscan = off;

Limit  (cost=1578577.14..1578578.31 rows=10 width=16) (actual time=3121.539..3123.430 rows=10 loops=1)
   Buffers: shared hit=95578
   ->  Gather Merge  (cost=1578577.14..2922011.54 rows=11514348 width=16) (actual time=3121.537..3123.426 rows=10 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=95578
         ->  Sort  (cost=1577577.12..1591970.05 rows=5757174 width=16) (actual time=3108.995..3108.997 rows=9 loops=3)
               Sort Key: ((((p.vector)::integer[])::vector <#> '[1,1,...,1]'::vector))
               Sort Method: top-N heapsort  Memory: 25kB
               Buffers: shared hit=95578
               Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
               ->  Parallel Bitmap Heap Scan on mytable p  (cost=184260.30..1453166.66 rows=5757174 width=16) (actual time=42.277..3057.887 rows=333333 loops=3)
                     Recheck Cond: (content_id = ANY ('{0,100,...,999900}'::integer[]))
                           Buffers: shared hit=40000
 Planning:
   Buffers: shared hit=149
 Planning Time: 8.591 ms
 Execution Time: 3123.638 ms
(23 rows)

型
1.类似于2，但使用generate_series：

Limit  (cost=1591694.63..1591694.66 rows=10 width=16) (actual time=6155.109..6155.114 rows=10 loops=1)
   Buffers: shared hit=104447
   ->  Sort  (cost=1591694.63..1626244.11 rows=13819790 width=16) (actual time=6155.107..6155.111 rows=10 loops=1)
         Sort Key: ((((p.vector)::integer[])::vector <#> '[1,1,...,1]'::vector))
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=104447
         ->  Nested Loop  (cost=194.30..1293053.94 rows=13819790 width=16) (actual time=2.912..6034.798 rows=1000000 loops=1)
               Buffers: shared hit=104444
               ->  HashAggregate  (cost=175.02..177.02 rows=200 width=4) (actual time=2.870..5.484 rows=10000 loops=1)
                     Group Key: generate_series(0, 999999, 100)
                     Batches: 1  Memory Usage: 929kB
                     ->  ProjectSet  (cost=0.00..50.02 rows=10000 width=4) (actual time=0.002..0.736 rows=10000 loops=1)
                           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
               ->  Bitmap Heap Scan on mytable p  (cost=19.28..4204.85 rows=1382 width=416) (actual time=0.007..0.020 rows=100 loops=10000)
                     Recheck Cond: (content_id = (generate_series(0, 999999, 100)))
                     Heap Blocks: exact=64444
                     Buffers: shared hit=104444
                     ->  Bitmap Index Scan on idx_content_on_mytable  (cost=0.00..18.93 rows=1382 width=0) (actual time=0.005..0.005 rows=100 loops=10000)
                           Index Cond: (content_id = (generate_series(0, 999999, 100)))
                           Buffers: shared hit=40000
 Planning:
   Buffers: shared hit=180
 Planning Time: 1.012 ms
 Execution Time: 6155.251 ms
(24 rows)

型

postgresql

2条答案

按热度按时间
trnvg8h3

trnvg8h31#

计划2 vs 3

计划2（带有位图的文本IN列表）和计划3（带有位图的generate_series）之间的区别很容易解释。PostgreSQL不知道如何在generate_series上并行化函数scan，所以在这种情况下它不使用并行化。这是由该计划中缺少“Gather...”节点指示的。
我对AWS graviton示例的理解是，它们的vCPU计数等于它们的实际CPU计数，所以你的“.large”机器有2个实际的处理器来分散工作。这个计划的速度大约是它的两倍，所以一切都有意义。（它试图将工作分散到3个进程（2个工人加上领导者），但这3个进程必须共享2个处理器）。但是，这实际上是一件好事吗？如果其他处理器将不被使用，是的。但是如果您的真实的服务器将足够忙碌，所有的CPU几乎总是在使用中，那么您只是在拆东墙补西墙。您的查询在隔离中运行的速度将更快，但是您的总吞吐量在不隔离运行时不会更好。

计划1 vs 2

计划1和计划2之间实际执行时间不同的原因也很容易理解，大部分时间都花在处理元组上（6300 ms中只有1028 ms花费在IO上），第一个计划必须处理所有的元组，而第二计划使用索引来排除这些元组中的大多数，而不实际处理它们。（另一个不同之处可能只是计划2的所有内容都已经在内存中了，大概是因为它是用“热缓存”运行的，但这只能解释最多1/3的差异，因为IO在较慢的计划上只花了一秒钟）
那么，为什么规划者不选择第二种方案呢？这是一种推测。看起来规划者高估了IO的成本，因此高估了优化IO的重要性。顺序计划完全是顺序的，而位图扫描只是一种顺序，所以第一种方案应该更好。然后它高估了差异的重要性。
此外，它大大高估了计划的这一部分实际返回的元组数量，预期为5757174，实际为333333。对于seq扫描本身，这并不重要，因为检查和丢弃元组与检查和保留元组的工作是一样的。但是对于位图堆扫描，事实并非如此，元组可以在使用索引时被丢弃，而不必检查它们，因此过高估计返回的元组数量会低估位图的实用性。（现在我把这个打出来了，我认为这部分比我前面一段中给出的部分更重要。）为什么估算这么差？首先，尝试分析表格，看看是否解决了问题。如果没有，然后检查（或向我们展示）关系“MyTable”的属性“content_id”的pg_stats的内容。我还注意到计划2是不完整的。您显示了位图堆扫描，但没有相应的位图索引扫描。但您必须删除它，因为我认为它不可能不存在。在这种情况下，我怀疑看到这一点会改变我的任何结论，但它的缺失令人不安。
为什么我说它高估了IO的重要性？seq扫描访问55754 + 436879 = 492633个缓冲区，这些是实际的计数，但是规划器没有理由为简单的顺序扫描使用未显示的估计（不使用吐司，我假设是这种情况）与实际总数有很大的不同。假设你没有改变seq_page_cost的默认值1.0，这贡献了该节点的计划成本的492633/1304760.16 = 37.8%，而实际时间要少得多，为1027.617/6237.441 = 16.5%。

赞(0）分享  回复(0）举报  2023-11-18
643ylb08

643ylb082#

既然我们需要每100行，而且大部分计算都是关于计算点积和按点积排序的，为什么顺序扫描不比使用索引更好呢？
点积计算只在检索记录后进行（不管检索它们的计划是什么）。在我的Google Cloud示例中，计算10 k对向量的点积并按它们排序需要46 ms，所以这不是增加最多开销的原因。
顺序扫描需要评估表中的每一条记录，并确保它符合WHERE条件。
索引扫描会首先遍历索引（比堆短得多），只检索需要检索的堆页。这会导致在循环中执行堆查找的额外成本（如果两个记录最终位于同一页上，则可能每页两次或更多次），这可能会因需要更少的堆页读取而抵消。
索引扫描的位图类型试图更智能地确定检索堆页的顺序（它首先从索引中检索所有指针，将它们存储在位图中，然后按堆顺序检索所有页，跳过不需要的页）。额外的成本是维护位图;胜利是每个堆表只需要检索一次。
您的第二个查询也执行了一个冗余的HashAggregate（以确保从GENERATE_SERIES输出的值是唯一的），但这不是什么大问题。
在您的计划中：

  1. Seq扫描：Buffers: shared hit=55754 read=436879，与2个工作人员并行
    1.位图索引扫描：Buffers: shared hit=95578（其中40000是索引），也与2个工作并行
  2. GENERATE_SERIES：Buffers: shared hit=104444（其中40000是索引），顺序
    seq扫描需要执行的页面读取次数几乎是索引扫描的五倍，而且大多数页面都没有缓存在共享缓冲区中。
    位图索引扫描需要先读取索引，然后构建符合条件的元组的位图，直到它适合内存。由于表很大，它会在某个时候切换到页面的位图，并执行重新检查它所获取的页面中的每个元组的额外步骤。位图确实需要一些资源来构建，但并不太多，与计划必须执行的其他工作相比。
    使用GENERATE_SERIES的是顺序的，这会使您的整体性能降低一半（因为此时，所有内容都被缓存，您的工作负载纯粹是CPU和RAM绑定的）。
    在PostgreSQL中，嵌套循环的内部部分不能并行化，外部部分是一个函数调用。您可以将IN条件替换为从0到500 k和从500 k到1 M的两个unioned GENERATE_SERIES调用的join。这可能会生成一个并行追加计划，这将恢复并行性，并且作为附带奖励将摆脱不必要的聚合。
    与第一个查询相比，您还可以观察到堆缓冲区读取略有增加：104444 vs 95578。这是因为您的this查询多次构建位图，并且一些堆页面重叠（因此一个页面具有多个索引键的条目）。
赞(0）分享  回复(0）举报  2023-11-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com