sql - Postgresql 在一列或大表中获取一组唯一值的最快方法

Question

我在 Postgresql 数据库中有一个不断增长的、可能非常大的表，其中包含来自不同“设备”的不同“ channel ”的“数据”，例如:

Table data:
  id (PK)
  device_id (FK -> device)
  channel_id (FK -> channel)
  timestamp (TIMESTAMP)
  value (Float)

我正在使用分区将表分成多个子表，每个子表对应一个设备，因为在同一个查询中我从不需要不同设备的数据。由于并非所有设备都提供所有 channel ，因此我想获得单个设备所有可用 channel 的列表。一个简单的 SQL 查询解决了我的问题:

SELECT DISTINCT(channel_id) FROM data where device_id = 1;

这工作得很好，但对于数据表中有很多条目的设备来说需要相当长的时间。使用分区大大加快了这个过程，因为数据库只需要遍历一个设备的条目，但对于某些设备来说仍然需要很长时间才能完成。

我的应用程序的属性很少会为一台设备添加新的 channel 类型。大多数 channel 是在设备添加到数据库后快速添加的，之后不会添加新 channel 。但是，可用 channel 的数据添加非常频繁。我还需要经常向用户显示每个设备的可用 channel 列表，因此我想加快可用 channel 的查找速度。我已经有了 channel_ids 的索引，希望它能加快获得所需列表的速度，但检索列表仍然需要很长时间。

目前，我能想到几种方法来解决我的问题:

1. 使用 postgres 的一些“神奇”功能，它完全符合我的要求，但我对此一无所知。
2. 使用触发器维护某种额外的表格，其中每个“设备”/“ channel ”组合仅包含一个条目，因此可以快速访问可用 channel 。但是，由于经常插入新数据，我认为触发器会产生大量开销，而它只会在极少数情况下检测到新 channel 。
3. 重新考虑我的数据库设计。也许插入另一个包含“ channel ”和“设备”之间链接的表，然后只在“数据”中对这个表进行 FK。对中间表的查询应该很快，但是，这基本上增加了我将数据插入数据库的应用程序的复杂性。
4. 也许 Views 在这里可以提供帮助？
5. 在我的前端使用缓冲仅定期执行查询，因为可用 channel 不会经常更改。

第 2 点和第 3 点在我看来会增加很多我想避免的不必要的开销。由于我没有找到与此主题相关的任何信息，因此我目前认为解决方案 5. 是我要走的路。但是，我想知道是否有人有更好的、可能基于数据库的解决方案来解决我的问题。

感谢您的帮助。

Answer 1

对于选项 1，您要做的是“松散索引扫描”或“跳过扫描”。

如果 PostgreSQL 能在有益时自动执行这些操作就好了，但到目前为止它还没有这样做。但是你可以trick it into it.我从未在分区表上尝试过此操作，但我认为将合适的 WHERE 子句添加到 union all 的每个分支会很简单。

也许您也想要选项 3，或者没有足够的信息让我说。