文章/答案/技术大牛

发布

社区首页 >问答首页 >PostgreSQL:多个表之间的缓慢连接。

问PostgreSQL:多个表之间的缓慢连接。
EN

Database Administration用户

提问于 2018-10-15 17:45:45

回答 1查看 3.9K关注 0票数 1

上下文：

我正在开发一个数据仓库，它需要有响应性的查找，因为它被用作操作员控制台的源。目标是实现亚1秒的查找(理想是100毫秒)。

预计数据的规模将增加到数百万，但由于较早的数据不那么相关，这可能会受到限制。

表：

表要么包含数据作为仅附加的日志，要么充当标识符，说明哪些记录是每个资源的“最新”记录。

首先，有关表格上的一些信息：

活动:有350万行，34列(只附加日志)

activitylatest:有110万行，4列(活动标识符)

圆形:350万行，17列(仅附日志)

最近:70万行，4列(圆形标识符)

所有列(大文本除外)上都有索引，因为用户可以使用任何字段进行筛选，而"updateId“是数据表的主要键。

查询

我试图获取活动中的数据，这取决于用户的过滤，其中可能包括圆桌会议中的一列。

select
activity.update_id,
...
activity.activity_id,
round.round_external_ref
from activity
join round
    on activity.round_id = round.round_id
join roundlatest
    on round.update_id = roundlatest.update_id
join activitylatest
    on activity.update_id = activitylatest.update_id
where activity.community_id = 1
order by activity.update_id desc
limit 1001

执行此查询将传递以下计划(EXPLAIN ANALYZE)：

    Limit  (cost=1.71..12367.37 rows=1001 width=2056) (actual time=2324.231..2341.835 rows=1001 loops=1)
  ->  Nested Loop  (cost=1.71..14219657.65 rows=1151081 width=2056) (actual time=2324.231..2341.721 rows=1001 loops=1)
        ->  Nested Loop  (cost=1.29..6270077.60 rows=5932256 width=2064) (actual time=2324.204..2333.877 rows=5158 loops=1)
              ->  Nested Loop  (cost=0.86..3654699.78 rows=1187917 width=2019) (actual time=0.024..1765.586 rows=390452 loops=1)
                    ->  Index Only Scan Backward using "IDX_datawarehouse_gameActivitylatest_update_id" on datawarehouse_gameactivitylatest  (cost=0.43..60604.50 rows=1199068 width=8) (actual time=0.015..165.819 rows=390452 loops=1)
                          Heap Fetches: 239183
                    ->  Index Scan using "PK_gameActivity_update_id" on datawarehouse_gameactivity  (cost=0.43..2.99 rows=1 width=2019) (actual time=0.002..0.003 rows=1 loops=390452)
                          Index Cond: (update_id = datawarehouse_gameactivitylatest.update_id)
                          Filter: (community_id = 1)
              ->  Index Scan using "IDX_datawarehouse_gameRound_round_id" on datawarehouse_gameround  (cost=0.43..2.15 rows=5 width=53) (actual time=0.001..0.001 rows=0 loops=390452)
                    Index Cond: (round_id = datawarehouse_gameactivity.round_id)
        ->  Index Only Scan using "IDX_datawarehouse_gameRoundlatest_update_id" on datawarehouse_gameroundlatest  (cost=0.42..1.33 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=5158)
              Index Cond: (update_id = datawarehouse_gameround.update_id)
              Heap Fetches: 1067
Planning time: 1.153 ms
Execution time: 2341.989 ms

第一次运行查询超过30秒(最多60秒)。连续运行似乎保持了大约5秒的持续时间。我假设这是由于系统缓存，我确实需要将第一次和连续的查询都修正为1秒以下。

在我的测试中，我注意到简单地将顺序从DESC切换到ASC，在使用合并连接的情况下，查询性能将提高到大约2.5秒。这让我感到困惑，我不确定原因，我确实需要DESC。

ASC解释分析：

Limit  (cost=184.88..10004.46 rows=1001 width=2056) (actual time=0.064..23.341 rows=1001 loops=1)
  ->  Nested Loop  (cost=184.88..11301988.03 rows=1152097 width=2056) (actual time=0.063..23.265 rows=1001 loops=1)
        ->  Nested Loop  (cost=184.46..3332810.27 rows=5940024 width=2064) (actual time=0.054..14.535 rows=5227 loops=1)
              ->  Merge Join  (cost=184.03..710946.78 rows=1189471 width=2019) (actual time=0.046..7.915 rows=1001 loops=1)
                    Merge Cond: (datawarehouse_gameactivity.update_id = datawarehouse_gameactivitylatest.update_id)
                    ->  Index Scan using "PK_gameActivity_update_id" on datawarehouse_gameactivity  (cost=0.43..630047.01 rows=3522681 width=2019) (actual time=0.010..5.513 rows=3007 loops=1)
                          Filter: (community_id = 1)
                    ->  Index Only Scan using "IDX_datawarehouse_gameActivitylatest_update_id" on datawarehouse_gameactivitylatest  (cost=0.43..60662.53 rows=1200637 width=8) (actual time=0.007..0.204 rows=1001 loops=1)
                          Heap Fetches: 0
              ->  Index Scan using "IDX_datawarehouse_gameRound_round_id" on datawarehouse_gameround  (cost=0.43..2.15 rows=5 width=53) (actual time=0.002..0.005 rows=5 loops=1001)
                    Index Cond: (round_id = datawarehouse_gameactivity.round_id)
        ->  Index Only Scan using "IDX_datawarehouse_gameRoundlatest_update_id" on datawarehouse_gameroundlatest  (cost=0.42..1.33 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=5227)
              Index Cond: (update_id = datawarehouse_gameround.update_id)
              Heap Fetches: 88
Planning time: 1.124 ms
Execution time: 23.475 ms

(解释分析显示，23 ms，但实际运行查询需要2.5秒，根据pgadmin)

我们在基于SSD云的VM上使用PostgreSQL 9.6。

postgresql

performance

query-performance

回答 1

Database Administration用户

发布于 2018-10-15 19:59:02

在这里，综合指数可能是最优的：

create index on activity (community_id, update_id);

按事项排序的原因(可能)是，community_id=1的行优先出现在update_id值的开头，因此，如果您沿着该索引走到积累了1001行时( community_id=1 )为止，它的速度要快得多，直到积累了1001 (有community_id=1 )为止。使用复合索引可以解决这个问题，因为您可以跳到您想要的地方，而不是以后需要过滤掉它们。

对于您对单列索引是否有用的疑问，它将选择它认为最有选择性的单列索引。因此，它总比没有好，但不如完美的多列索引。它还可以使用位图扫描将多个单列索引组合在一起。同样，这并不像完美的多列索引那样好，但它很容易就足够好了。如果您总是按update_id排序，那么可以有效地将每个单列索引转换为以update_id结尾的两列复合索引，这可能不会增加太多的开销，因为索引的数量保持不变。不幸的是，您不能同时获得位图扫描和索引支持的顺序，因此它必须在两者之间进行选择。

票数 1

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/220153

复制

相似问题

问PostgreSQL:多个表之间的缓慢连接。
EN

上下文：

表：

查询

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PostgreSQL:多个表之间的缓慢连接。EN

上下文：

表：

查询

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PostgreSQL:多个表之间的缓慢连接。
EN