dcc310's questions

假设我有一个包含列customer_id（随机生成的字符串）和的仅附加表x，并且查找总是在customer_id.

假设数据如下所示，就好像我们在客户最初注册某项时获得了一批行，然后再也没有为该客户注册。

customer_id=XCVFY0001, x=...
customer_id=XCVFY0001, x=...
(continues for ~1 page with same customer_id)
customer_id=HUMBN0001, x=...
customer_id=HUMBN0001, x=...
(continues for ~1 page with same customer_id)
(and so on...)

因此，虽然customer_id的字母顺序与物理行无关，但我们可以做出如下陈述：

• 每页很少有不同的客户 ID
• 每个 ID 有几页
• 有很长的“运行”ID，或者，如果您需要一个customer_id，您可能会在几个连续的页面上找到它
• 就信息论而言，我认为他们会说没有相关性，但存在很高的“互信息”

如果没有明确运行，查询规划器可以在估计中使用这样的信息CLUSTER吗？我假设如果correlation报告中的低pg_stats，它会猜测行在整个页面中均匀分布，并且可能对各种计划持悲观态度。

（在我的现实世界模拟中，一个普通的非聚集索引无论如何都能让事情变得又好又快，但是当我注意到数据中的模式时我只是好奇。）

另一个问题来自我发现EXPLAINPostgreSQL 中很棒的新选项。这一个侧重于BUFFERS选项。

这是解释：

EXPLAIN (ANALYZE, BUFFERS) SELECT event_time FROM ui_events_v2  WHERE page ~ 'foo' LIMIT 1;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..1539.68 rows=1 width=8) (actual time=0.858..0.858 rows=1 loops=1)
   Buffers: shared read=10
   I/O Timings: read=0.735
   ->  Seq Scan on ui_events_v2  (cost=0.00..3313394.58 rows=2152 width=8) (actual time=0.857..0.857 rows=1 loops=1)
         Filter: (page ~ 'foo'::text)
         Rows Removed by Filter: 112
         Buffers: shared read=10
         I/O Timings: read=0.735
 Planning Time: 6.455 ms
 Execution Time: 0.877 ms

它非常快 - 而且这个查询在冷启动时非常慢。这是一个 30M 行的表，七行page包含 substring foo。与此查询匹配的第一行似乎显示了 604k 页面，这将是中提到的 ~3M 页面的 ~20% pg_class：

SELECT min(ctid) FROM ui_events_v2 WHERE page ~ 'foo';
     min
-------------
 (604435,10)

我的假设是，即使该表的每个页面都在 PostgreSQL 或操作系统缓存中，它仍然需要遍历一些线性页面列表以进行顺序扫描。sectionBuffers: shared read=10和0.877 ms执行时间向我表明，它以某种方式“从中断的地方开始”并从远离开始的页面开始。我认为它通过页面 ID 移动并在移动时尝试查看每个页面的缓存，但它是否可能从缓存本身开始？如果是这样，它如何发现非缓存页面？

我知道不能保证以任何特定顺序找到行，但我认为在相同的查询策略中，遵循的路径会相对相似。

在提出最近的问题时，我的 EXPLAIN ANALYZE 输出中出现了一些神秘的启动时间组件。我玩得更远了，发现如果我删除正则表达式WHERE子句，启动时间会下降到接近 0。

我运行了以下 bash 脚本作为测试：

for i in $(seq 1 10)
do
    if (( $RANDOM % 2 == 0 ))
    then
        echo "Doing plain count"
        psql -e -c "EXPLAIN ANALYZE SELECT count(*) FROM ui_events_v2"
    else
        echo "Doing regex count"
        psql -e -c "EXPLAIN ANALYZE SELECT count(*) FROM ui_events_v2 WHERE page ~ 'foo'"
    fi
done

第一个查询返回约 3000 万行的计数，第二个查询仅计算 7 行。它们在 RDS 中的 PG 12.3 只读副本上运行，其他活动最少。正如我所料，这两个版本所花费的时间大致相同。下面是一些用 过滤的输出grep：

Doing plain count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060374.07 rows=12632507 width=0) (actual time=0.086..38622.215 rows=10114306 loops=3)
Doing regex count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3091955.34 rows=897 width=0) (actual time=16856.679..41398.062 rows=2 loops=3)
Doing plain count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060374.07 rows=12632507 width=0) (actual time=0.162..39454.499 rows=10114306 loops=3)
Doing plain count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060374.07 rows=12632507 width=0) (actual time=0.036..39213.171 rows=10114306 loops=3)
Doing regex count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3091955.34 rows=897 width=0) (actual time=12711.308..40015.734 rows=2 loops=3)
Doing plain count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060374.07 rows=12632507 width=0) (actual time=0.244..39277.683 rows=10114306 loops=3)
Doing regex count
^CCancel request sent

所以，有几个问题：

1. 正则表达式扫描中“实际时间”的启动组件是什么，为什么它要大得多？（10-20 秒对 0-1 秒）

2. 虽然“成本”和“时间”不是可比单位，但规划者似乎认为启动成本在所有情况下都应该为 0——这是被愚弄了吗？

3. 为什么这些策略看起来不同？两个计划都提到Partial Aggregate，但是正则表达式查询说实际行是2，但是普通版本说实际行是 ~1000 万（我猜这是 2 个工人和 1 个领导者之间的某种平均值，总和为 ~3000 万）。如果我必须自己实现这个，我可能会将几个count(*)操作的结果相加，而不是合并行和计数 - 计划是否表明它是如何做到这一点的？

所以我不隐藏任何东西，下面是每个查询计划的完整版本：

EXPLAIN ANALYZE SELECT count(*) FROM ui_events_v2
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=3093171.59..3093171.60 rows=1 width=8) (actual time=39156.499..39156.499 rows=1 loops=1)
   ->  Gather  (cost=3093171.37..3093171.58 rows=2 width=8) (actual time=39156.356..39157.850 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=3092171.37..3092171.38 rows=1 width=8) (actual time=39154.405..39154.406 rows=1 loops=3)
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060587.90 rows=12633390 width=0) (actual time=0.033..38413.690 rows=10115030 loops=3)
 Planning Time: 7.968 ms
 Execution Time: 39157.942 ms
(8 rows)


EXPLAIN ANALYZE SELECT count(*) FROM ui_events_v2 WHERE page ~ 'foo'
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=3093173.83..3093173.84 rows=1 width=8) (actual time=39908.495..39908.495 rows=1 loops=1)
   ->  Gather  (cost=3093173.61..3093173.82 rows=2 width=8) (actual time=39908.408..39909.848 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=3092173.61..3092173.62 rows=1 width=8) (actual time=39906.317..39906.318 rows=1 loops=3)
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3092171.37 rows=897 width=0) (actual time=17250.058..39906.308 rows=2 loops=3)
                     Filter: (page ~ 'foo'::text)
                     Rows Removed by Filter: 10115028
 Planning Time: 0.803 ms
 Execution Time: 39909.921 ms
(10 rows)

我EXPLAIN在 Postgres 12.3 上无法理解这一点：

EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT count(1) FROM mytable WHERE page ~ 'foo';

这是一个包含 3000 万行的 22GB 表，位于具有 16GB 内存的服务器上。该查询计算 7 个匹配行。

我将输出解释为 I/O 花费了 164 秒，但整个查询只用了 65 秒。我认为这可能会重复计算一些并行工作人员，但是当我添加时VERBOSE，它似乎也没有加起来。

看起来好像是说 2 名工人中的每一个都花了大约 55 秒的时间阅读。如果总和为 110 秒，我如何获得 164 秒的 I/O？（由于缓存页面时此查询需要约 10 秒，我猜实际读取时间与此处的 50 秒相差不远，FWIW）

我也很困惑这Parallel Seq Scan似乎需要 32 秒，但还有 30 多秒才能得到最终结果。我认为由于它找到了 7 行，除了扫描之外几乎没有其他工作可做。我读错了这个部分吗？

                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=3092377.14..3092377.15 rows=1 width=8) (actual time=65028.818..65028.818 rows=1 loops=1)
   Output: count(1)
   Buffers: shared hit=75086 read=2858433 dirtied=1
   I/O Timings: read=164712.060
   ->  Gather  (cost=3092376.92..3092377.13 rows=2 width=8) (actual time=65028.732..65030.093 rows=3 loops=1)
         Output: (PARTIAL count(1))
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=75086 read=2858433 dirtied=1
         I/O Timings: read=164712.060
         ->  Partial Aggregate  (cost=3091376.92..3091376.93 rows=1 width=8) (actual time=65026.990..65026.990 rows=1 loops=3)
               Output: PARTIAL count(1)
               Buffers: shared hit=75086 read=2858433 dirtied=1
               I/O Timings: read=164712.060
               Worker 0: actual time=65026.164..65026.164 rows=1 loops=1
                 Buffers: shared hit=25002 read=952587
                 I/O Timings: read=54906.994
               Worker 1: actual time=65026.264..65026.264 rows=1 loops=1
                 Buffers: shared hit=25062 read=954370 dirtied=1
                 I/O Timings: read=54889.244
               ->  Parallel Seq Scan on public.ui_events_v2  (cost=0.00..3091374.68 rows=896 width=0) (actual time=31764.552..65026.980 rows=2 loops=3)
                     Filter: (ui_events_v2.page ~ 'foo'::text)
                     Rows Removed by Filter: 10112272
                     Buffers: shared hit=75086 read=2858433 dirtied=1
                     I/O Timings: read=164712.060
                     Worker 0: actual time=16869.988..65026.156 rows=2 loops=1
                       Buffers: shared hit=25002 read=952587
                       I/O Timings: read=54906.994
                     Worker 1: actual time=64091.539..65026.258 rows=1 loops=1
                       Buffers: shared hit=25062 read=954370 dirtied=1
                       I/O Timings: read=54889.244
 Planning Time: 0.333 ms
 Execution Time: 65030.133 ms