DeekSeek辅助总结PostgreSQL Mistakes and How to Avoid Them 的一个例子

原文地址：https://www.postgresql.org/docs/books/

假设你有一个包含数十万条客户支持工单的表，其模型如下（过度简化）：

CREATETABLEsupport.tickets(idint,contenttext,statussmallint);

对于我们的示例数据，我们假设工单的状态 status = 10 表示“开放”，status = 20 表示“已关闭”。让我们插入几十万行已关闭的工单：

INSERTINTOsupport.ticketsSELECTid,'case description text',20FROMgenerate_series(1,499750)ASid;

现在再插入几百行最近的、仍处于开放状态的工单：

INSERTINTOsupport.ticketsSELECTid,'case description text',10FROMgenerate_series(499751,500000)ASid;

为了简单起见，我们假设并行化不可能实现，因此我们通过以下命令禁用它：

SETmax_parallel_workers_per_gather=0;

我们将通过启用 psql 中的计时功能来跟踪查询持续时间：

\timing 计时已开启。

现在假设对于你的客户支持应用程序，只有开放工单是相关的。因此，你希望计算有多少个开放工单，因为你只关心这些。让我们尝试以下操作。

尝试解决问题

SELECTcount(*)FROMsupport.ticketsWHEREstatus=10;

确实，这个开放工单计数返回了正确结果。但它很慢：

count-------250(1行)耗时：110.036ms

让我们通过运行 EXPLAIN 来看看为什么它慢，EXPLAIN 会向我们展示PostgreSQL将如何执行查询（查询计划）：

EXPLAINSELECTcount(*)FROMsupport.ticketsWHEREstatus=10;查询计划----------------------------------------------------------------Aggregate(成本=9927.28..9927.29rows=1width=8)->在 tickets 上的顺序扫描(成本=0.00..9927.28rows=1width=0)过滤器:(status=10)(3行)

这告诉我们，为了运行聚合 count()，PostgreSQL 计划在 tickets 表上使用顺序扫描，然后通过 status = 10 来过滤结果。顺序扫描（也称为全表扫描）很慢。所以你想，我来创建一个索引。索引能让一切变快，对吧？

CREATEINDEXONsupport.tickets(status);CREATEINDEX耗时：732.403ms

现在索引已经创建，我们再试一次：

SELECTcount(*)FROMsupport.ticketsWHEREstatus=10;count-------250(1行)耗时：3.715ms

这下好多了。EXPLAIN 会确认为什么现在快得多：它使用了仅索引扫描：

查询计划----------------------------------------------------------------------------Aggregate(成本=4.44..4.45rows=1width=8)->仅索引扫描使用 tickets_status_idx 在 tickets ↪(成本=0.42..4.44rows=1width=0)索引条件:(status=10)(3行)

然而，这个索引相当大。

为什么这个不行？

\x \di+support.tickets*关系列表-[记录1]-+-------------------模式|support 名称|tickets_status_idx 类型|索引 拥有者|frogge 表|tickets 持久性|永久 访问方式|btree大小|3408kB 描述|

想象一下，你的客户支持历史中有数亿条工单，但同一时间只有大约最新的250条是开放的。大索引当然会占用更多磁盘空间。但它们的速度也更慢，因为有更多数据需要遍历，并且它们会减慢写入操作，因为每次 INSERT 或 UPDATE 都需要更新它们。

在我们的案例中，我们只关心相对较少的开放工单。因此，我们可以通过使用所谓的部分索引来节省索引大小，只包含我们感兴趣的行：WHERE status = 10。

我们现在删除之前的索引，并创建一个新的部分索引。

正确的解决方案

DROPINDEXsupport.tickets_status_idx;CREATEINDEXONsupport.tickets(status)WHEREstatus=10;

看看这个索引现在小了多少！这是有道理的，因为我们只索引了总行数的大约 0.05%，对吧？

\di+support.tickets*关系列表-[记录1]-+-------------------模式|support 名称|tickets_status_idx 类型|索引 拥有者|frogge 表|tickets 持久性|永久 访问方式|btree大小|16kB 描述|

这展示了在将索引大小减少了200多倍之后，现在的执行时间看起来如何：

SELECTcount(*)FROMsupport.ticketsWHEREstatus=10;count-------250(1行)耗时：0.762ms

EXPLAIN 现在显示如下：

查询计划----------------------------------------------------------------------------Aggregate(成本=4.16..4.17rows=1width=8)->仅索引扫描使用 tickets_status_idx 在 tickets ↪(成本=0.14..4.16rows=1width=0)(2行)

操作仍然是仅索引扫描，但使用的是小得多的索引，因此执行所需时间也少得多。

所以，我们已经看到，简单地在列上放置一个索引在技术上可行，但当你考虑到大数据量和性能要求等因素时，这并不是最优的解决方案。