使用聚合表和分区优化查询性能

Question

我们的设置有点复杂，所以我将尝试简化我们的表的外观，但仍然试图描述真正的问题：

我们有一个大表(items)，大约有5亿行(每天增长150万行)。其中每个项目都有一个时间戳(除其他事项外)。我们有5个元数据表(基于项目类型)，使用FKs到items。它们每一行约有1亿行。

在对items表执行查询时，我们需要对元数据表中的值进行筛选和排序。这是基于UI中的筛选器参数，所以我们可以从所有5个元数据表中筛选东西，或者没有一个元数据表。查询将如下所示：

SELECT 
    i.id
FROM items i
LEFT JOIN itemmetadata1 im1 ON im1.itemid = i.id AND i.type = 1
LEFT JOIN itemmetadata2 im1 ON im2.itemid = i.id AND i.type = 2
LEFT JOIN itemmetadata3 im1 ON im3.itemid = i.id AND i.type = 3
LEFT JOIN itemmetadata4 im1 ON im4.itemid = i.id AND i.type = 4
LEFT JOIN itemmetadata5 im1 ON im5.itemid = i.id AND i.type = 5
WHERE
    i.timestamp > '2019-02-01'
    AND i.timestamp < '2019-02-07'
    -- These aren't always here, query is dynamically generated (based on user input)
    AND im1.somevalue = TRUE
    AND im3.anothervalue > 5
    ... etc
ORDER BY 
    -- This can also change dynamically
    im5.value DESC

这使得这个查询在某些情况下非常慢。例如，在长时间查看时，元数据表中几乎没有匹配的行。

所以我有两个问题：

1.聚合

创建一个名为aggregateditems的新的非规范化表是否有意义?它包含我们需要过滤和排序的所有项和元数据列？这样，我们的查询就会简化如下：

SELECT 
    i.id
FROM aggregateditems ai
WHERE
    ai.timestamp > '2019-02-01'
    AND ai.timestamp < '2019-02-07'
    -- These aren't always here, query is dynamically generated (based on user input)
    AND ai.metadata1somevalue = TRUE
    AND ai.metadata3anothervalue > 5
    ... etc
ORDER BY
    -- This can also change dynamically
    ai.metadata5value DESC

我想，如果有正确的指数，这将大大加快速度。

因此，我的想法是在items和元数据表上放置触发器，当数据发生变化时会更新aggregateditems，这是个好主意吗？或者这会扼杀表演吗？

2.分区

创建聚合表将加快查询的速度，但我们仍然有非常大的表，具有很大的索引，所以我想我们也需要进行一些分区。

假设我们在items和aggregateditems上划分timestamp，我们如何解析元数据表？(他们没有任何时间戳)。

Answer 1

我建议试一试单桌设计。将有只与单个类型相关的列组，这意味着对于任何其他类型的行，它们都是空的。对于表的物理布局，您可能首先需要公共列，然后是频繁过滤的类型特定列，然后是很少/从未过滤过的类型特定列。

这将消除需要做左联接的问题。它还可以方便地在时间戳上进行分区，因为您不再有缺少时间戳的侧表。希望大型文本字段能够积极地获得TOASTed，降低主表的大小(但这不是一件容易控制的事情)。

如果具有最新时间戳的分区是最热门的分区，这将有助于保持它们的数据和索引足够小以适应RAM缓存，并且足够热到可以自然地保存在那里。