postgresql - 专门为仪表板构建的表有几个过滤器......最好的索引方式？

Question

我创建了一个物化 View ，用于输入仪表板。

我的目标是以最快的方式使这个表可以选择，我不知道如何处理它。我希望如果我描述这张 table 以及如何使用它，有人可以提供一些方向。

上下文是一个包含漏斗步骤的网站。每一行都是用户触发漏斗步骤的一个实例，例如添加到购物车、结帐、付款详细信息，然后是最后的交易。

由于该表是用于分析的，它会在每天早上用 cron 自动刷新一次，所以我不担心实时更新速度，只用各种 where 子句选择速度。

假设我有以下描述的字段:

(N = ~1300 万，预计到 1 月约为 20，每月增长数百万)
表是唯一的 session id、用户 id 和漏斗步骤的组合。

 - Session Id (Id, so some duplication but generally very very granular - Varchar)
 - User Id (Id, so some duplication but generally very very granular - Varchar)
 - Date (Date)
 - Funnel Step (10 distinct value - Varchar)
 - Device Category (3 distinct values - Varchar)
 - Country (~ 100 distinct values - varchar)
 - City (~1000+ distinct values - varchar)
 - Source (several thousand distinct values, nevertheless, stakeholder would like a filter - varchar)

我会单独索引每个字段吗？或者，我应该索引一个中的所有字段吗？ Per the documentation ，我想我一次最多可以索引 32 个字段。但是，考虑到我的主要目标是选择查询速度超过其他所有内容，这是否可取？

该表将输入仪表板，该仪表板读取该表并将过滤器输入动态转换为 where 子句。每次用户调整过滤器时，都会根据过滤器/where 子句输入读取表格并对其进行分组和聚合。

示例查询:

select 
  event_action,
  count(distinct user_id) as users
from website_data.ecom_funnel
where date >= $input_start_date
and date <= $input_end_date
and device_category in ($mobile, $desktop, $tablet)
and country in ($list of all countries minus any not selected)
and source in ($list of all sources minus any not selected)
group by 1 order by users desc

这将产生一个漏斗形的数据表。

我无法事先汇总，因为关注的主要指标是用户，而不是 session 。这些必须从基础表中删除重复数据。经典示例...假设一个人每周访问一个网站，每天一次。那么那一周的唯一身份访问者的总和是 1，但是如果我按天计算访问者的总和，我会得到 7。与我的表格类似，一些用户需要多次 session 才能完成漏斗。所以，这就是我不能预先聚合表的原因，因为我需要对基础数据应用过滤器，然后 count(distinct user id) .

如果有用的话，这里解释一下字段的子集:

QUERY PLAN
Sort  (cost=862194.66..862194.68 rows=9 width=24)
  Sort Key: (count(DISTINCT client_id)) DESC
  ->  GroupAggregate  (cost=847955.01..862194.51 rows=9 width=24)
        Group Key: event_action
        ->  Sort  (cost=847955.01..852701.48 rows=1898589 width=37)
              Sort Key: event_action
              ->  Seq Scan on ecom_funnel  (cost=0.00..589150.14 rows=1898589 width=37)
                    Filter: ((device_category = ANY ('{mobile,desktop}'::text[])) AND (source = 'google'::text))

我的总体具体问题是，考虑到我的用例，我应该单独索引每个字段还是应该创建一个索引？有关系吗？

最重要的是，任何优化此物化 View 以更快地运行选择查询的提示将不胜感激。

Answer 1

查看您的过滤条件，您应该通过发布检查 device_category 字段的基数

select device_category, count(*) from website_data.ecom_funnel group by device_category

并查看这些值以确定索引是否应首先包含此列。这里可能的索引(不知道基数)将是多列的，包括:

(device_category, date)

话虽如此，在每个单独的列上创建索引没有任何好处，因为您的查询不会全部使用它们，所以这很重要。您会减慢其他不是读取操作的 CRUD 操作。

在所有列上创建索引也可能不会为您加快太多速度，但这取决于引擎盖下的数据(在表中)以及您的过滤器如何与没有它们的整体查询进行比较(值的基数在被过滤的列中)。这很可能会在遍历索引树然后获取 rowid 以返回您需要的数据时产生巨大的开销。

总而言之，我会尝试将索引缩小到过滤中最重要的列，这意味着它们会削减大部分正在检索的数据。如果您的查询旨在返回表中的大多数行，那么不幸的是，需要聚合，因为这不会加快速度。

希望能帮助到你。

编辑 :我刚刚读到您已经在表中发布了不同值的计数。我不确定Funnel Step在您的表中绑定(bind)到什么，但假设它是一个名为event_action的列，那么创建一个有助于分组的索引可能会有所帮助:

(date, event_action)

好像您省略了 GROUP BY子句，应该包括在内，并且应该按 event_action 分组，因为这就是您的选择部分正在做的事情。

如果每次执行选择查询时将日期缩小到几天/几个月，那么使用第一个 date 创建索引可能会带来巨大的好处。柱子。

请记住，索引中列的位置很重要。

如果您查找几个月的值，假设您应该预先汇总每个月的预先计算值并将其存储在另一个表中，然后 UNION ALL该数据到当前查询，该查询只会从当前(仍在更新)时间中选择数据。