Postgresql 10:具有精确堆块的位图堆扫描

Question

我有以下查询：

select ro.*
from courier c1
    join courier c2 on c2.real_physical_courier_1c_id = c1.real_physical_courier_1c_id
    join restaurant_order ro on ro.courier_id = c2.id
    left join jsonb_array_elements(items) jae on true
    left join jsonb_array_elements(jae->'options') ji on true
    inner join catalogue c on c.id in ((jae->'id')::int, (ji->'id')::int)
    join restaurant r on r.id = ro.restaurant_id
where c1.id = '7b35cdab-b423-472a-bde1-d6699f6cefd3' and ro.status in (70, 73)
group by ro.order_id, r.id ;

下面是一个查询计划的一部分，花费了95%的时间：

->  Parallel Bitmap Heap Scan on restaurant_order ro  (cost=23.87..2357.58 rows=1244 width=1257) (actual time=11.931..38.163 rows=98 loops=2)"
      Recheck Cond: (status = ANY ('{70,73}'::integer[]))"
      Heap Blocks: exact=28755"
      ->  Bitmap Index Scan on ro__status  (cost=0.00..23.34 rows=2115 width=0) (actual time=9.168..9.168 rows=51540 loops=1)"
            Index Cond: (status = ANY ('{70,73}'::integer[]))"

我有几个问题要问。

1. 首先是位图索引扫描部分。Postgres遍历基于ro__status的51540条Index Cond: (status = ANY ('{70,73}'::integer[]))"索引记录，并创建一个包含28755个元素的位图。它的键是对应表行的物理位置(由exact在Heap Blocks部分表示)。这是正确的吗？
2. 第二，将此映射传递到位图堆扫描阶段。实际上没有执行Recheck Cond，因为堆块不是有损的样式。位图堆扫描按元组的物理位置对位图进行排序，以启用顺序访问。然后，它按两次顺序读取表数据(loops=2)，不超过196个表行。对吗？
3. 在Heap Blocks: exact=28755行中所反映的位图大小随时间变化很大。差别是两个数量级。例如，昨天大约有500人。为什么是这样？
4. 现在，为什么在位图索引扫描阶段创建的位图有这么多键？有一个ro__status索引可以表示只有大约200个记录，状态为70和73。我想不出有什么理由阻止postgres只保留那些实际上满足index cond的密钥。开销似乎很大:不是200个键，而是28755！
5. 为什么位图堆扫描要花这么长时间？据我所见，有两个顺序读取(loops=2)，它应该花费更少的时间，不是吗？或者，按元组的物理位置进行位图排序是罪魁祸首吗？
6. 我是否应该担心糟糕的估计呢？如果是这样的话，增加default_statistics_target应该会有帮助，对吗？现在它是默认的100。

以防万一，这里有一个完整的计划：

"Group  (cost=51297.15..52767.65 rows=19998 width=1261) (actual time=42.555..42.555 rows=0 loops=1)"
"  Group Key: ro.order_id, r.id"
"  ->  Gather Merge  (cost=51297.15..52708.83 rows=11764 width=1261) (actual time=42.554..45.459 rows=0 loops=1)"
"        Workers Planned: 1"
"        Workers Launched: 1"
"        ->  Group  (cost=50297.14..50385.37 rows=11764 width=1261) (actual time=38.850..38.850 rows=0 loops=2)"
"              Group Key: ro.order_id, r.id"
"              ->  Sort  (cost=50297.14..50326.55 rows=11764 width=1261) (actual time=38.850..38.850 rows=0 loops=2)"
"                    Sort Key: ro.order_id, r.id"
"                    Sort Method: quicksort  Memory: 25kB"
"                    Worker 0:  Sort Method: quicksort  Memory: 25kB"
"                    ->  Nested Loop  (cost=31.84..45709.27 rows=11764 width=1261) (actual time=38.819..38.819 rows=0 loops=2)"
"                          ->  Nested Loop Left Join  (cost=27.21..5194.50 rows=5882 width=1325) (actual time=38.819..38.819 rows=0 loops=2)"
"                                ->  Nested Loop Left Join  (cost=27.20..5076.49 rows=59 width=1293) (actual time=38.818..38.818 rows=0 loops=2)"
"                                      ->  Nested Loop  (cost=27.20..5074.49 rows=1 width=1261) (actual time=38.818..38.818 rows=0 loops=2)"
"                                            ->  Hash Join  (cost=26.93..5073.59 rows=1 width=1257) (actual time=38.817..38.818 rows=0 loops=2)"
"                                                  Hash Cond: (c2.real_physical_courier_1c_id = c1.real_physical_courier_1c_id)"
"                                                  ->  Nested Loop  (cost=24.28..5068.22 rows=1038 width=1267) (actual time=11.960..38.732 rows=98 loops=2)"
"                                                        ->  Parallel Bitmap Heap Scan on restaurant_order ro  (cost=23.87..2357.58 rows=1244 width=1257) (actual time=11.931..38.163 rows=98 loops=2)"
"                                                              Recheck Cond: (status = ANY ('{70,73}'::integer[]))"
"                                                              Heap Blocks: exact=28755"
"                                                              ->  Bitmap Index Scan on ro__status  (cost=0.00..23.34 rows=2115 width=0) (actual time=9.168..9.168 rows=51540 loops=1)"
"                                                                    Index Cond: (status = ANY ('{70,73}'::integer[]))"
"                                                        ->  Index Scan using courier_pkey on courier c2  (cost=0.41..2.18 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=195)"
"                                                              Index Cond: (id = ro.courier_id)"
"                                                  ->  Hash  (cost=2.63..2.63 rows=1 width=10) (actual time=0.039..0.039 rows=1 loops=2)"
"                                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"                                                        ->  Index Scan using courier_pkey on courier c1  (cost=0.41..2.63 rows=1 width=10) (actual time=0.034..0.034 rows=1 loops=2)"
"                                                              Index Cond: (id = '7b35cdab-b423-472a-bde1-d6699f6cefd3'::uuid)"
"                                            ->  Index Only Scan using restaurant_pkey on restaurant r  (cost=0.27..0.89 rows=1 width=4) (never executed)"
"                                                  Index Cond: (id = ro.restaurant_id)"
"                                                  Heap Fetches: 0"
"                                      ->  Function Scan on jsonb_array_elements jae  (cost=0.00..1.00 rows=100 width=32) (never executed)"
"                                ->  Function Scan on jsonb_array_elements ji  (cost=0.01..1.00 rows=100 width=32) (never executed)"
"                          ->  Bitmap Heap Scan on catalogue c  (cost=4.63..6.87 rows=2 width=4) (never executed)"
"                                Recheck Cond: ((id = ((jae.value -> 'id'::text))::integer) OR (id = ((ji.value -> 'id'::text))::integer))"
"                                ->  BitmapOr  (cost=4.63..4.63 rows=2 width=0) (never executed)"
"                                      ->  Bitmap Index Scan on catalogue_pkey  (cost=0.00..0.97 rows=1 width=0) (never executed)"
"                                            Index Cond: (id = ((jae.value -> 'id'::text))::integer)"
"                                      ->  Bitmap Index Scan on catalogue_pkey  (cost=0.00..0.97 rows=1 width=0) (never executed)"
"                                            Index Cond: (id = ((ji.value -> 'id'::text))::integer)"
"Planning Time: 1.113 ms"
"Execution Time: 45.588 ms"

Answer 1

它构建了一个由51 540个项目组成的位图。然后将其分成两部分(大致)，两个并行进程各一个。exact=28755的报告显然只针对其中一个过程。(如果通过set max_parallel_workers_per_gather TO 0禁用并行查询，则生成的计划将更容易理解。在查看查询性能时，这通常是我做的第一件事，除非并行化是我正在研究的问题。无论我做什么改进，通常都会转化为并行执行。)

位图本身就是按物理顺序排列的。对其进行排序与创建它并不是一个单独的步骤。PostgreSQL按该顺序逐个读取块。如果决定，则由操作系统/文件系统将这些单独的读取合并为顺序读取。根据我的经验，你必须阅读几乎每一个街区，然后才能取得良好的效果。如果你只读了五分之一(随机)块，你可能会做随机阅读。我无法从您的数据中看出28755块表中所代表的部分。

现在，为什么在位图索引扫描阶段创建的位图有这么多键？有一个ro__status索引可以指示只有大约200个记录，状态为70和73。

PostgreSQL中的索引本身并不包含任何可见性信息。"ro__status“不知道哪些ctid仍然可见，所以它们都必须填充到位图中。然后，它们中的大多数在堆扫描阶段被拒绝，因为它们是不可见的。(没有明确地报告这一点，即重新检查和筛选拒绝的方式。您必须根据位图大小和最后一行计数之间的差异来推断它。对于BitmapAnd和BitmapOr，您甚至很难做到这一点，因为位图大小不准确)。

这就是问题的症结所在，你访问了5万多个元组，仅仅是为了找到195个活的。把那些死掉的元组从指数中清除出来是吸尘的主要工作之一。所以很可能你的桌子还不够真空。您可以非常简单地测试这个问题，将表真空，看看它是否解决了问题。如果它没有，那么你可能有长期持有的快照，这是防止真空是有效的，所以去寻找那些。

Btree索引确实有一个“微真空”特性，常规索引扫描会杀死索引元组，它发现索引元组指向所有堆元组。但是位图索引扫描没有实现这一点，因为它们在最初的咨询之后不会重新访问索引，因此没有好的机会杀死索引元组。位图扫描将受益于这种微真空，但不会执行微真空本身。这可能导致一种不正常的情况，即与常规索引扫描相比，位图扫描越多，索引的相关部分就越臃肿，直到位图扫描开始变得更糟。增加空分可以解决这个问题，但是如果您不想再增加它，那么您就可以一般地阻止位图。增加effective_cache_size是实现这一目的的一种方法(但前提是您实际上有足够的内存来备份这种增加)。