Vertica和联接

Question

我正在调整一个网络分析工具以使用 Vertica 作为数据库，但我在优化联接方面遇到了实际问题。我尝试为一些查询创建预加入投影，虽然它确实使查询变得非常快，但它将数据加载到事实表中的速度放慢到了爬行速度。

我们用来从暂存表中将数据加载到事实表中的简单 "INSERT INTO ... SELECT * FROM" 从需要大约 5 秒变成了需要 20 多分钟。

因此，我放弃了所有预加入投影，并尝试使用数据库设计师来设计特定于查询的投影，但这还不够。即使有了这些投影，一个简单的联接也需要大约 14 秒，而使用预加入投影只需要大约 1 秒。

我的问题是：预加入投影会使数据插入如此缓慢是否正常，如果不是，可能的原因是什么？ 如果正常的话，那对我们来说就是个致命问题，有哪些其他技术可以加速联接？

我们在 5 节点集群上运行 Vertica，每个节点都有 2 个四核 CPU 和 32 GB 内存。 我的示例查询中的表分别具有 188,843,085 和 25,712,878 行。

EXPLAIN 输出如下：

EXPLAIN SELECT referer_via_.url as referralPageUrl, COUNT(DISTINCT sessio
n.id) as visits FROM owa_session as session JOIN owa_referer AS referer_vi
a_ ON session.referer_id = referer_via_.id WHERE session.yyyymmdd BETWEEN 
'20121123' AND '20121123' AND session.site_id = '49' GROUP BY referer_via_
.url  ORDER BY visits DESC LIMIT 250;

Access Path:
+-SELECT  LIMIT 250 [Cost: 1M, Rows: 250 (STALE STATISTICS)] (PATH ID: 0)
|  Output Only: 250 tuples
|  Execute on: Query Initiator
| +---> SORT [Cost: 1M, Rows: 1 (STALE STATISTICS)] (PATH ID: 1)
| |      Order: count(DISTINCT "session".id) DESC
| |      Output Only: 250 tuples
| |      Execute on: All Nodes
| | +---> GROUPBY PIPELINED (RESEGMENT GROUPS) [Cost: 1M, Rows: 1 (STALE 
STATISTICS)] (PATH ID: 2)
| | |      Aggregates: count(DISTINCT "session".id)
| | |      Group By: referer_via_.url
| | |      Execute on: All Nodes
| | | +---> GROUPBY HASH (SORT OUTPUT) (RESEGMENT GROUPS) [Cost: 1M, Rows
: 1 (STALE STATISTICS)] (PATH ID: 3)
| | | |      Group By: referer_via_.url, "session".id
| | | |      Execute on: All Nodes
| | | | +---> JOIN HASH [Cost: 1M, Rows: 1 (STALE STATISTICS)] (PATH ID: 
4) Outer (RESEGMENT)
| | | | |      Join Cond: ("session".referer_id = referer_via_.id)
| | | | |      Execute on: All Nodes
| | | | | +-- Outer -> STORAGE ACCESS for session [Cost: 463, Rows: 1 (ST
ALE STATISTICS)] (PUSHED GROUPING) (PATH ID: 5)
| | | | | |      Projection: public.owa_session_projection
| | | | | |      Materialize: "session".id, "session".referer_id
| | | | | |      Filter: ("session".site_id = '49')
| | | | | |      Filter: (("session".yyyymmdd >= 20121123) AND ("session"
.yyyymmdd <= 20121123))
| | | | | |      Execute on: All Nodes
| | | | | +-- Inner -> STORAGE ACCESS for referer_via_ [Cost: 293K, Rows:
26M] (PATH ID: 6)
| | | | | |      Projection: public.owa_referer_DBD_1_seg_Potency_2012112
2_Potency_20121122
| | | | | |      Materialize: referer_via_.id, referer_via_.url
| | | | | |      Execute on: All Nodes

- user997904

---

我对Vertica一无所知，但像(STALE STATISTICS)这样的提示会让我非常担心。你尝试更新统计信息了吗？ - user330315

我最关注的问题是在 public.owa_referer_DBD_1_seg_Potency_2012112 上缺乏筛选器。我提高查询性能的第一步是确保所有投影都应用了最大数量的筛选器。 - QuinnG

即使统计数据相对较新，有时仍可能报告过时的统计信息。我不指望运行ANALYZE_STATISTICS会突然加快速度，但值得一试。 - kimbo305

你能对查询进行分析并将分析数据上传到某个地方吗？我会尝试告诉你发生了什么。问题这两个投影的编码方式是什么样的？ 表是否已分区？ 计划并发设置是多少？ - Joe

我们遇到了类似的问题，但我认为通过优化投影，我们已经取得了一些进展。尽管如此，速度仍然太慢了。参见：http://vertica-forums.com/viewtopic.php?uid=355&f=11&t=731&start=0 自您发布这个问题以来，您有取得任何进展吗？ - Jaka Jančar

如果您除了这个之外还发布了您的实际查询，那么这将成为SE代码审查的一个很好的候选者。http://codereview.stackexchange.com/ - Phrancis

Answer 1

加速连接：

• 设计会话表以在“yyyymmdd”列上进行分区。这将启用分区剪枝。

• 如果可能的话，在_referer_via_列上添加对“yyyymmdd”列的条件，并对其进行分区（很可能不行）

• 在所使用的（超级）投影的会话中，使site_id列尽可能靠近order by列表的开头

  • 相应地，在referer_id和id上同时分段两个表。

拥有更多的节点也有助于提高速度。

- Sergey Cherepanov

Answer 2

我认为你的查询需要更加明确。同时不要使用Devil BETWEEN，可以尝试这样：

EXPLAIN SELECT 
    referer_via_.url as referralPageUrl, 
    COUNT(DISTINCT session.id) as visits 
FROM owa_session as session 
JOIN owa_referer AS referer_via_ 
    ON session.referer_id = referer_via_.id
WHERE session.yyyymmdd <= '20121123' 
AND session.yyyymmdd > '20121123' 
AND session.site_id = '49' 
GROUP BY referer_via_.url
-- this `visits` column needs a table name
ORDER BY visits DESC LIMIT 250;

我得说，我真的很困惑为什么你会在使用BETWEEN时使用相同的DATE，你可能需要进一步研究一下。

- Phrancis

Answer 3

我的问题是：预连接投影会导致数据插入变慢吗？如果不是，可能的原因是什么？如果是正常的，那对我们来说就是一个停滞点，还有其他技术可以用来加速连接吗？

我想受影响的数量会根据您所使用的数据集和结构而有所不同。但是，由于这是您更改的变量，我相信可以肯定地说预连接投影导致了速度变慢。您正在以查询时间为代价获得插入时间。

如果以下任何内容有误，请有人纠正我。我是根据记忆和与他人交谈时获得的信息来判断的。

您可以通过几种方式加速连接而无需进行预连接投影。在这种情况下，是引荐者ID。我认为，如果您使用连接谓词对两个表进行分段投影，这将有所帮助。您可以尽可能地过滤数据。

查看您的解释计划，您正在执行哈希连接而不是合并连接，您可能需要考虑一下。

最后，我想通过解释计划或系统表知道您的查询是否实际上使用了数据库设计师推荐的投影。如果没有，请在查询中明确指定它们，看看是否有所帮助。

- geoffrobinson

Answer 4

这是我从学术背景出发，与列式数据库一起工作的看法，包括Vertica（数据库系统的最新博士研究生）。

引用 我的问题是：预连接投影会减缓数据插入到这个程度，这是正常现象吗？如果不是，可能是什么原因呢？如果是正常的话，那对我们来说就是一个停车点了，是否有其他技术可以加快连接速度呢？ 引用

是的，更新投影非常慢，最好只在大批量的情况下进行以摊销更新成本。根本原因是每个投影都代表了数据的另一个副本（属于投影的每个表列的副本）。

单行插入需要向投影中的每个列添加一个值（一个属性）。例如，在具有20个属性的表中进行单行插入至少需要20次列更新。更糟糕的是，每个列都是排序和压缩的。这意味着在列中插入新值需要对大块数据执行多个操作：读取数据/解压缩/更新/排序/压缩数据/写回数据。Vertica针对更新进行了多个优化，但无法完全隐藏成本。

投影可以被视为传统行存储（MySQL、PostgreSQL、Oracle等）中多列索引的等效物。与传统的B-Tree索引相比，投影的优势在于读取它们（使用它们来回答查询）比使用传统索引要快得多。原因有多个：与非聚集索引相比，无需访问头数据，由于压缩而体积更小等等。反面是它们更新起来更加困难。权衡……

- Radu

Answer 5

你似乎有很多陈旧的统计信息。响应陈旧的统计数据非常重要，因为这是查询变慢的原因。如果没有关于底层数据的统计信息，Vertica的查询优化器无法选择最佳执行计划。而且响应陈旧的统计数据只能提高SELECT性能，而不能提高更新性能。

如果您经常更新数据表，请记住在VERTICA中还有其他要考虑的事项。请查看我在此问题的回答。我希望那能帮助提高您的更新速度。

像回答中解释的那样探索AHM设置。如果您不需要稍后选择表中已删除的行，通常最好不要保留它们。有方法可以仅保留最新时期版本的数据。或手动清除已删除的数据。让我知道进展如何。

- Eakan Gopalakrishnan