sql - 为什么 Redshift 需要进行全表扫描才能找到 DIST/SORT 键的最大值？

Question

我正在对 Redshift 进行简单测试，以尝试加快将数据插入 Redshift 表的速度。我今天注意到的一件事是做这样的事情

CREATE TABLE a (x int) DISTSTYLE key DISTKEY (x) SORTKEY (x);
INSERT INTO a (x) VALUES (1), (2), (3), (4);
VACUUM a; ANALYZE a;

EXPLAIN SELECT MAX(x) FROM a;

产量

QUERY PLAN
XN Aggregate  (cost=0.05..0.05 rows=1 width=4)
  ->  XN Seq Scan on a  (cost=0.00..0.04 rows=4 width=4)

我知道这只有 4 行，但它仍然不应该进行全表扫描来查找预排序列的最大值。元数据不是包含在 ANALYZE 完成的工作中吗？

作为健全性检查，SELECT x FROM a WHERE x > 3 的 EXPLAIN 仅扫描 2 行而不是整个表。

编辑:我在表中插入了 1,000,000 多行，随机值从 1 到 10,000。抽真空并分析。查询计划仍然表示它必须扫描所有 1,000,004 行。

Answer 1

分析小型数据集中的查询计划不会对数据库如何执行查询产生任何实际见解。

优化器有阈值，当不同计划之间的成本差异足够小时，它就会停止考虑替代计划。这个想法是，对于简单的查询，搜索“完美”执行计划所花费的时间可能会超过次优计划的总执行时间。

Redshift 是基于 ParAccel DB 的代码开发的。 ParAccel 实际上有数百个参数，可以更改/调整以针对不同的工作负载/情况优化数据库。

由于 Redshift 是一种“托管”产品，因此在给定“预期”工作负载的情况下，它已将这些设置预设为 Amazon 工程师认为最佳的水平。

一般来说，Redshift 和 ParAccel 不太适合单片查询。无论如何，这些查询往往会在所有切片中运行，即使它们只会在单个切片中查找数据。

一旦查询在切片中执行，读取的最小数据量就是一个 block 。根据 block 大小，这可能意味着数十万行。

请记住，Redshift 没有索引。所以你不会有一个简单的记录查找，它会从索引中读取一些条目，然后激光聚焦在磁盘上的单个页面上。它将始终至少读取该表的整个 block ，并将在每个切片中执行此操作。

---

如何拥有有意义的数据集来评估查询计划？

简短的回答是您的表每个切片将有“大量”数据 block 。

我的表需要每个切片多少个 block ？答案取决于几个因素:

1. 集群中的节点数
2. 集群中的节点类型 - 每个节点的切片数
3. 数据类型 - 每个值需要多少字节。
4. 涉及的列的压缩编码类型询问。最佳编码取决于数据人口统计

所以让我们从头开始。

Redshift is an MPP Database, where processing is spread accross multiple nodes. See Redshift's architecture here.

Each node is further sub-divided in slices, which are dedicated data partitions and corresponding hardware resources to process queries on that partition of the data.

当在 Redshift 中创建表并插入数据时，Redshift 会为每个切片分配至少一个 block 。

---

这是一个简单的例子:

如果您创建了一个包含两个 ds1.8xlarge 节点的集群，那么每个节点将有 16 个切片乘以两个节点，总共有 32 个切片。

假设我们正在查询，并且 WHERE 子句中的列是类似于“ITEM_COUNT”的整数。一个整数占用 4 个字节。

Redshift uses a block size of 1MB.

因此，在这种情况下，您的 ITEM_COUNT 列至少有 32 个 block 乘以 1MB 的 block 大小，这相当于 32MB 的存储空间。

如果您有 32MB 的存储空间并且每个条目只占用 4 个字节，那么您可以拥有超过 800 万个条目，并且它们都可以放在一个 block 中。

In this example in the Amazon Redshift documentation they load close to 40 million rows to evaluate and compare different encoding techniques. Read it here.

---

但是等等......

有压缩，如果您有 75% 的压缩率，这意味着即使 3200 万条记录仍然能够放入单个 block 中。

底线是什么？

为了分析您的查询计划，您需要具有多个 block 的表和列。在我们上面的示例中，3200 万行仍然是一个 block 。

这意味着在上面的配置中，根据所有假设，具有单个记录的表基本上很可能与具有 3200 万条记录的表具有相同的查询计划，因为在这两种情况下，数据库只需要读取每片一个 block 。

---

如果您想了解您的数据如何跨切片分布以及使用了多少 block ，您可以使用以下查询:

每片有多少行:

Select trim(name) as table_name, id, slice, sorted_rows, rows
from stv_tbl_perm
where name like '<<your-tablename>>'
order by slice;

如何计算有多少 block :

select trim(name) as table_name, col,  b.slice, b.num_values, count(b.slice)
from stv_tbl_perm a, stv_blocklist b
where a.id = b.tbl
  and a.slice = b.slice
and name like '<<your-tablename>>'
group by 1,2,3,4
order by col, slice;