Postgres查询优化

Question

我们的数据库中有一张表格叫做menus，它有515502行。其中有一列名为status，其类型为smallint。

目前，一个简单的计数查询对于那些status值为3的文档集合需要花费700毫秒。

explain analyze select count(id) from menus where status = 2;
Aggregate  (cost=72973.71..72973.72 rows=1 width=4) (actual time=692.564..692.565 rows=1 loops=1)
->  Bitmap Heap Scan on menus  (cost=2510.63..72638.80 rows=133962 width=4) (actual time=28.179..623.077 rows=135429 loops=1)
         Recheck Cond: (status = 2)
         Rows Removed by Index Recheck: 199654
         ->  Bitmap Index Scan on menus_status  (cost=0.00..2477.14 rows=133962 width=0) (actual time=26.211..26.211 rows=135429 loops=1)
               Index Cond: (status = 2)
 Total runtime: 692.705 ms
(7 rows)

有一些行的列值为1，对于这些行，查询速度非常快。

 explain analyze select count(id) from menus where status = 4;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=7198.73..7198.74 rows=1 width=4) (actual time=24.926..24.926 rows=1 loops=1)
   ->  Bitmap Heap Scan on menus  (cost=40.53..7193.53 rows=2079 width=4) (actual time=1.461..23.418 rows=2220 loops=1)
         Recheck Cond: (status = 4)
         ->  Bitmap Index Scan on menus_status  (cost=0.00..40.02 rows=2079 width=0) (actual time=0.858..0.858 rows=2220 loops=1)
               Index Cond: (status = 4)
 Total runtime: 25.089 ms
(6 rows)

我观察到，最常用的btree索引是针对简单的基于等式查询的最佳索引策略。相比于gin和hash，btree更快。

有什么方法可以使使用索引的任何过滤器的count查询更快吗？

我知道这是一个初学者问题，因此请提前谅解我可能犯的任何错误。

- user2512324

---

如果您有一个大表，索引使用良好，您可以尝试优化流程。我的意思是：状态是否经常更改？您能否准备一个带有总和的表，并使用触发器或函数进行维护？ - user_0

这个表的 id 是主键吗？你能尝试使用 count(*) 吗？ - Ihor Romanchenko

好的，所以如果查询和索引适合，你应该能够从索引唯一扫描（自9.2版本以来）中受益，这就是我想知道版本的主要原因。 - Craig Ringer

@user2512324 count(status) 也应该可以工作。count(id) 不行的原因是 PostgreSQL 没有优化 count(id)，以证明 id 是 NOT NULL，所以它认为必须获取 id 字段，而这不是索引的一部分，因此无法使用索引扫描来获取。 - Craig Ringer

如果您需要超快速度，您可能希望考虑使用触发器或定期刷新的物化视图来维护。请注意，由触发器维护的物化视图往往会对插入/更新/删除的并发性产生影响，而定期更新的物化视图则不会完全准确。 - Craig Ringer

Answer 1

也许您的表中有更多行的状态为2而不是4，因此第二种情况下总表访问时间更长。 因此，对于状态=2，要考虑的行太多，因此位图堆扫描的位图进入“有损”模式，并且需要在操作后重新检查。因此，有两件事需要考虑：要么您的结果太大（但如果没有重新组织表格，例如使用分区，您无法做任何事情），要么您的'work_mem'参数太小，无法保留中间结果。如果有可能，请尝试增加其值。

- onerror