提高PostgreSQL聚合查询性能

Question

我正在从一个Postgres表中聚合数据，查询大约需要2秒钟的时间，我希望将其减少到不到1秒钟。

请查看下面的执行详细信息：

---

查询

select
    a.search_keyword,
    hll_cardinality( hll_union_agg(a.users) ):: int as user_count,
    hll_cardinality( hll_union_agg(a.sessions) ):: int as session_count,
    sum(a.total) as keyword_count
from
    rollup_day a
where
    a.created_date between '2018-09-01' and '2019-09-30'
    and a.tenant_id = '62850a62-19ac-477d-9cd7-837f3d716885'
group by
    a.search_keyword
order by
    session_count desc
limit 100;

---

表元数据

1. 总行数 - 506527
2. 基于列的复合索引 : tenant_id和created_date

---

查询计划

Custom Scan (cost=0.00..0.00 rows=0 width=0) (actual time=1722.685..1722.694 rows=100 loops=1)
  Task Count: 1
  Tasks Shown: All
  ->  Task
        Node: host=localhost port=5454 dbname=postgres
        ->  Limit  (cost=64250.24..64250.49 rows=100 width=42) (actual time=1783.087..1783.106 rows=100 loops=1)
              ->  Sort  (cost=64250.24..64558.81 rows=123430 width=42) (actual time=1783.085..1783.093 rows=100 loops=1)
                    Sort Key: ((hll_cardinality(hll_union_agg(sessions)))::integer) DESC
                    Sort Method: top-N heapsort  Memory: 33kB
                    ->  GroupAggregate  (cost=52933.89..59532.83 rows=123430 width=42) (actual time=905.502..1724.363 rows=212633 loops=1)
                          Group Key: search_keyword
                          ->  Sort  (cost=52933.89..53636.53 rows=281055 width=54) (actual time=905.483..1351.212 rows=280981 loops=1)
                                Sort Key: search_keyword
                                Sort Method: external merge  Disk: 18496kB
                                ->  Seq Scan on rollup_day a  (cost=0.00..17890.22 rows=281055 width=54) (actual time=29.720..112.161 rows=280981 loops=1)
                                      Filter: ((created_date >= '2018-09-01'::date) AND (created_date <= '2019-09-30'::date) AND (tenant_id = '62850a62-19ac-477d-9cd7-837f3d716885'::uuid))
                                      Rows Removed by Filter: 225546
            Planning Time: 0.129 ms
            Execution Time: 1786.222 ms
Planning Time: 0.103 ms
Execution Time: 1722.718 ms

---

我尝试过的方法

1. 我尝试在tenant_id和created_date上建立索引，但由于数据量太大，因此它总是进行顺序扫描而不是索引扫描以进行筛选。我阅读了相关资料并发现，如果返回的数据>总行数的5-10%，则Postgres查询引擎会切换到顺序扫描。请单击此处了解更多信息。
2. 我将work_mem增加到100MB，但只能稍微提高性能。

非常感谢您的任何帮助。

---

更新

将work_mem设置为100MB后的查询计划

Custom Scan (cost=0.00..0.00 rows=0 width=0) (actual time=1375.926..1375.935 rows=100 loops=1)
  Task Count: 1
  Tasks Shown: All
  ->  Task
        Node: host=localhost port=5454 dbname=postgres
        ->  Limit  (cost=48348.85..48349.10 rows=100 width=42) (actual time=1307.072..1307.093 rows=100 loops=1)
              ->  Sort  (cost=48348.85..48633.55 rows=113880 width=42) (actual time=1307.071..1307.080 rows=100 loops=1)
                    Sort Key: (sum(total)) DESC
                    Sort Method: top-N heapsort  Memory: 35kB
                    ->  GroupAggregate  (cost=38285.79..43996.44 rows=113880 width=42) (actual time=941.504..1261.177 rows=172945 loops=1)
                          Group Key: search_keyword
                          ->  Sort  (cost=38285.79..38858.52 rows=229092 width=54) (actual time=941.484..963.061 rows=227261 loops=1)
                                Sort Key: search_keyword
                                Sort Method: quicksort  Memory: 32982kB
                                ->  Seq Scan on rollup_day_104290 a  (cost=0.00..17890.22 rows=229092 width=54) (actual time=38.803..104.350 rows=227261 loops=1)
                                      Filter: ((created_date >= '2019-01-01'::date) AND (created_date <= '2019-12-30'::date) AND (tenant_id = '62850a62-19ac-477d-9cd7-837f3d716885'::uuid))
                                      Rows Removed by Filter: 279266
            Planning Time: 0.131 ms
            Execution Time: 1308.814 ms
Planning Time: 0.112 ms
Execution Time: 1375.961 ms

---

更新2

在创建了一个按created_date索引并将work_mem增加到120MB后，

create index date_idx on rollup_day(created_date);

总行数为：12,124,608

查询计划如下：

Custom Scan (cost=0.00..0.00 rows=0 width=0) (actual time=2635.530..2635.540 rows=100 loops=1)
  Task Count: 1
  Tasks Shown: All
  ->  Task
        Node: host=localhost port=9702 dbname=postgres
        ->  Limit  (cost=73545.19..73545.44 rows=100 width=51) (actual time=2755.849..2755.873 rows=100 loops=1)
              ->  Sort  (cost=73545.19..73911.25 rows=146424 width=51) (actual time=2755.847..2755.858 rows=100 loops=1)
                    Sort Key: (sum(total)) DESC
                    Sort Method: top-N heapsort  Memory: 35kB
                    ->  GroupAggregate  (cost=59173.97..67948.97 rows=146424 width=51) (actual time=2014.260..2670.732 rows=296537 loops=1)
                          Group Key: search_keyword
                          ->  Sort  (cost=59173.97..60196.85 rows=409152 width=55) (actual time=2013.885..2064.775 rows=410618 loops=1)
                                Sort Key: search_keyword
                                Sort Method: quicksort  Memory: 61381kB
                                ->  Index Scan using date_idx_102913 on rollup_day_102913 a  (cost=0.42..21036.35 rows=409152 width=55) (actual time=0.026..183.370 rows=410618 loops=1)
                                      Index Cond: ((created_date >= '2018-01-01'::date) AND (created_date <= '2018-12-31'::date))
                                      Filter: (tenant_id = '12850a62-19ac-477d-9cd7-837f3d716885'::uuid)
            Planning Time: 0.135 ms
            Execution Time: 2760.667 ms
Planning Time: 0.090 ms
Execution Time: 2635.568 ms

- Ankur Mahajan

---

这个"排序方法：外部合并 磁盘：18496kB"占用了大部分时间。你可能需要将work_mem增加到100MB以上，直到它消失为止。 - user330315

磁盘上的大小比内存中的要小得多（磁盘操作针对小尺寸进行了优化，以使其在性能上至少有些可接受）。内存排序所需的内存通常比较大。也许 hll_union_agg 需要这么多内存。 - user330315

它需要在内存中排序的内存量并不取决于核心数量。它也不取决于可用的RAM数量，尽管显然在正常操作中将work_mem设置得太高而RAM太低是一个糟糕的想法。 - jjanes

@jjanes，请检查我已更新问题。 - Ankur Mahajan

哈哈，@jjanes，我正在使用 t3a.xlarge（4核16GB）上的Postgres 11.6。 - Ankur Mahajan

Answer 1

您应该尝试将work_mem设置得更高，直到获得内存排序。当然，只有在机器有足够的内存时，您才能慷慨地使用它。

如果您使用材料化视图或第二个表和触发器在原始表中保持总和更新，则可以使查询速度更快。我不知道这是否适用于您的数据，因为我不知道hll_cardinality和hll_union_agg是什么。

- Laurenz Albe

---

谢谢您的回复。hll是Postgres用于聚合基数的扩展。参考：https://github.com/citusdata/postgresql-hll - Ankur Mahajan

这张表已经是一个预聚合的表，包含了一天的总和，但由于数据集对于预聚合的表来说非常庞大，所以查询性能不佳。 - Ankur Mahajan

除了为排序提供更多的“work_mem”之外，没有其他的改进可行。 - Laurenz Albe

Answer 2

您尝试过使用覆盖索引吗？这样优化器将使用索引而不是顺序扫描。

create index covering on rollup_day(tenant_id, created_date, search_keyword, users, sessions, total);

如果使用Postgres 11：

create index covering on rollup_day(tenant_id, created_date) INCLUDE (search_keyword, users, sessions, total);

但是由于您在 search_keyword 上也进行了排序/分组，因此可能会有：

create index covering on rollup_day(tenant_id, created_date, search_keyword);
create index covering on rollup_day(tenant_id, search_keyword, created_date);

或者:

create index covering on rollup_day(tenant_id, created_date, search_keyword) INCLUDE (users, sessions, total);
create index covering on rollup_day(tenant_id, search_keyword, created_date) INCLUDE (users, sessions, total);

其中一个索引应该能够加快查询速度。您只需要添加一个索引。

即使它能够加快查询速度，拥有大型索引也可能会使写操作变慢（特别是在索引列上不可用HOT更新）。而且您将使用更多的存储空间。

• 灵感来自这里，还有有关work_mem大小的提示
• 另一个未使用索引的示例

- hsibboni

---

谢谢你的回答，我一定会尝试你提到的索引。 - Ankur Mahajan

好的，如果有任何最佳解决方案或者需要解释的地方，请告诉我。我也考虑过将 search_keyword 放在第一位，但我认为这样行不通。 - hsibboni

只有一个顺序索引在我的情况下起作用 'create index s_k_idx on rollup_day(search_keyword desc);' 我已经尝试了其他的，但没有成功。 - Ankur Mahajan

你的意思是当你添加它们并查询计划时，其他索引都不会被使用吗？ 即使是 create index covering on rollup_day(search_keyword, tenant_id, created_date) INCLUDE (users, sessions, total); 或者 create index covering on rollup_day(search_keyword, tenant_id, created_date, users, sessions, total); 或者 create index covering on rollup_day(search_keyword, tenant_id, created_date);？ - hsibboni

是的，我会分享包含你提到的所有索引的查询计划。我认为由于group by子句，这些索引都没有起作用。 - Ankur Mahajan

Answer 3

使用表分区和创建复合索引可以降低总成本，因为:

• 它将为您节省大量的扫描成本。
• 分区将隔离数据，并且在未来的清除操作中也非常有帮助。

• 我个人尝试过并测试了这种情况下的表分区，结合分区和复合索引的吞吐量是惊人的。

• 分区可以根据创建日期范围进行，然后在日期和租户上创建复合索引。

• 请记住，如果查询中有非常特定的条件，您始终可以在复合索引中设置条件。这样，数据将已经在索引中排序，并且还将为排序操作节省大量成本。

希望这能帮到您。

PS：另外，是否可以共享相应的测试样本数据？

- Raj Verma

---

谢谢，Raj。我知道分区，但只有在特定范围的数据集受限或适合于该范围时才有效。但在我的情况下，数据集非常庞大。如果created_date适合分区范围，我已经优化了查询以达到900毫秒。一旦查询超出范围，就需要合并分区，这是一个繁重的操作。 - Ankur Mahajan

我理解你的担忧，但在这种情况下查询优化器将跳过排序和 top-N 堆，试一试，如果不起作用，你总是可以调整工作内存。使用这个方法，我过去已经成功地将查询时间从 84000 毫秒降至 10 毫秒！真实的故事。 - Raj Verma

感谢，很高兴听到这个消息。work_mem仅需要将排序或连接计算移动到内存中而不是磁盘中，我已经实现了这一点，因此我认为增加work_mem不会对查询产生更多影响。 - Ankur Mahajan

如果需要更详细的信息，请告诉我，如果您能分享一些样本，我可以看一下。祝好运！ :) - Raj Verma

当然，我的领英账号在我的个人资料中，随意添加联系！ - Raj Verma

Answer 4

我的建议是将选择操作分解。

现在，我会尝试与此同时在表上设置两个索引。一个是日期索引，另一个是ID索引。奇怪的ID之一的问题是，比较需要时间，并且它们可以在后台被视为字符串比较。这就是为什么要进行分解，以在执行between命令之前对数据进行预过滤。现在，between命令可能会使选择变慢。在这里，我建议将其分解为2个选择和内部连接（我知道内存消耗是一个问题）。

这是一个例子，我希望优化器足够聪明，能够重构您的查询。

SELECT 
    a.search_keyword,
    hll_cardinality( hll_union_agg(a.users) ):: int as user_count,
    hll_cardinality( hll_union_agg(a.sessions) ):: int as session_count,
    sum(a.total) as keyword_count
FROM
    (SELECT
        *
    FROM
        rollup_day a
    WHERE
        a.tenant_id = '62850a62-19ac-477d-9cd7-837f3d716885') t1 
WHERE
    a.created_date between '2018-09-01' and '2019-09-30'
group by
    a.search_keyword
order by
    session_count desc

如果这样不起作用，那么您需要更具体的优化。例如，如果总数可以等于0，则需要在数据上创建过滤索引，其中总数> 0。是否有其他标准可以轻松地从选择中排除行。

下一个考虑是创建一行，其中有一个短ID（而不是62850a62-19ac-477d-9cd7-837f3d716885 -> 62850），它可以是一个数字，这将使预选非常容易且内存消耗较少。

- Marko Bencik

---

这个查询的性能更差了。你的查询已经超过3秒钟了。 - Ankur Mahajan

我认为你需要重新索引，复合索引无法与该查询一起使用，你能发布执行计划吗？它将显示需要索引的内容。只需编辑我的帖子，这将让我了解优化器的工作原理。 - Marko Bencik

你需要摆脱UUID，最好创建一个整数和UUID关系的表格，参考链接：https://www.percona.com/blog/2019/11/22/uuids-are-popular-but-bad-for-performance-lets-discuss/ - Marko Bencik