主页 / dba / 问题 / 140520
Accepted
Dean MacGregor
Asked: 2016-06-07 06:25:33 +0800 CST

是否可以将一张表的两个副本无缝地聚集在不同的排列中？

  • 772

我有一个大约 100M 行的表。它每天只插入一次数据，但我们需要做select很多事情。这些selects 通常很简单，但有时需要返回数千行的数百行。

它基于三列node_id,是唯一的pricedate，hour分别是 integer, timestamp, integer。对于大多数查询来说它很慢，但我将它聚集到node_id，pricedate这解决了大多数查询的缓慢问题。这些查询属于以下类型：

select * from mytable where node_id in (1,2,3,4)

我们仍然偶尔需要做这样的查询：

select * from mytable where pricedate>='2016-05-01'

这些仍然很慢，因为它node_id首先聚集在一起。我们已经有了索引pricedate。问题是用户经常需要足够的数据，以至于查询引擎抛出索引并使用 seq 扫描。一旦它使用序列扫描，它就会从以查询方式聚集数据中获益匪浅。这导致了我遇到的问题，其中一些查询受益于一个集群，而其他查询受益于另一个集群：

如果有一种方法可以拥有表的两个物理副本，其中一个副本以一种方式聚集，另一个以另一种方式聚集，但用户访问它似乎只有一个表，并且数据库引擎将确保它们'同步。显然这样做会有写惩罚，但这对我们的使用来说无关紧要。

这样的事情可能吗？

我猜没有内置的方法来做我描述的事情。无论如何，我想我会创建一个表mytable_dup，该表具有相同的唯一键约束但具有备用集群，然后设置触发器以在插入/更新/删除主表时插入它。这似乎是可行的，但从这里开始，是否有一种合理的方法select可以有效地从重复的表中提取出来？

我在家里运行 PostgreSQL 9.4，在 Google 上运行 9.5。

postgresql performance

3 个回答

  1. Best Answer

    要将数据保存在两个不同的物理序列中，必须将数据存储两次。这可以通过定义第二个覆盖索引来实现。覆盖索引包含查询所需的所有列。这样优化器就不需要参考基表来读取更多的值，并且不太可能恢复到查询计划的基表扫描。优化器执行仅索引扫描。由于索引的选择是由优化器而不是程序员做出的，因此不需要更改应用程序代码以在读取期间利用。在写入期间不需要其他对象来保持一致性。

    WHERE 子句中使用的列将是索引的前导列。其他列的顺序并不重要。当 PostgreSQL 支持 INCLUDE 语法时，可以更改此索引以使用它。

    缺点包括 a) 额外的磁盘来存储此数据 b) 写入期间的额外延迟以维护索引 c) 重组等需要更多的系统维护，以及 d) 当查询更改时，覆盖索引必须更改以匹配 e ) 相应地更大和更长的备份和恢复。

    • 5

  2. 您可以在表上创建物化视图：

    CREATE MATERIALIZED VIEW thecopy AS SELECT * FROM mytable;

    然后添加一个与您的 PK 匹配的唯一索引mytable（您不能在那里添加“真实”PK，因为它不是“真实”表）：

    CREATE UNIQUE INDEX ON thecopy (node_id, pricedate, hour);

    所以你的副本在那里。如果你想聚类它，你需要一个索引：

    CREATE INDEX ON thecopy (pricedate);
CLUSTER thecopy USING thecopy_pricedate_idx;
-- You can also do 
-- ALTER MATERIALIZED VIEW thecopy CLUSTER ON thecopy_pricedate_idx;
-- https://www.postgresql.org/docs/current/static/sql-altermaterializedview.html

    然后每当你需要它时（基本上在每日数据加载完成后），做一个

    REFRESH MATERIALIZED VIEW [CONCURRENTLY] thecopy;

    然后将第二种类型的查询更改为转到 MV 而不是表。

    • 3

  3. 因此，假设您想为 select 子句的用户保持简单，并且不知道他们是如何执行它的……

    使用函数怎么样？

    一种选择：

    将整个 select 子句作为参数传递给函数，

    为 where 子句列解析它

    然后按照@dezso 的建议将其定向到表或物化视图？

    • -1

相关问题