postgresql - 优化具有多个连接的大表上的查询索引
问题描述
我们有一个images
包含大约 2500 万条记录的表,当我根据来自多个连接的值查询表时,规划器的估计与行数的实际结果有很大不同。我们有其他查询大致相同,但没有所有连接,而且速度要快得多。我想知道我可以采取哪些步骤来调试和优化查询。此外,最好有一个索引覆盖连接和where
子句中包含的所有列,还是每个连接列有多个索引,然后另一个索引包含子句中的所有字段where
?
查询:
EXPLAIN ANALYZE
SELECT "images".* FROM "images"
INNER JOIN "locations" ON "locations"."id" = "images"."location_id"
INNER JOIN "users" ON "images"."creator_id" = "users"."id"
INNER JOIN "user_groups" ON "users"."id" = "user_groups"."user_id"
WHERE "images"."deleted_at" IS NULL
AND "user_groups"."group_id" = 7
AND "images"."creator_type" = 'User'
AND "images"."status" = 2
AND "locations"."active" = TRUE
ORDER BY date_uploaded DESC
LIMIT 50
OFFSET 0;
解释:
Limit (cost=25670.61..25670.74 rows=50 width=585) (actual time=1556.250..1556.278 rows=50 loops=1)
-> Sort (cost=25670.61..25674.90 rows=1714 width=585) (actual time=1556.250..1556.264 rows=50 loops=1)
Sort Key: images.date_uploaded
Sort Method: top-N heapsort Memory: 75kB
-> Nested Loop (cost=1.28..25613.68 rows=1714 width=585) (actual time=0.097..1445.777 rows=160886 loops=1)
-> Nested Loop (cost=0.85..13724.04 rows=1753 width=585) (actual time=0.069..976.326 rows=161036 loops=1)
-> Nested Loop (cost=0.29..214.87 rows=22 width=8) (actual time=0.023..0.786 rows=22 loops=1)
-> Seq Scan on user_groups (cost=0.00..95.83 rows=22 width=4) (actual time=0.008..0.570 rows=22 loops=1)
Filter: (group_id = 7)
Rows Removed by Filter: 5319
-> Index Only Scan using users_pkey on users (cost=0.29..5.40 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=22)
Index Cond: (id = user_groups.user_id)
Heap Fetches: 18
-> Index Scan using creator_date_uploaded_Where_pub_not_del on images (cost=0.56..612.08 rows=197 width=585) (actual time=0.062..40.992 rows=7320 loops=22)
Index Cond: ((creator_id = users.id) AND ((creator_type)::text = 'User'::text) AND (status = 2))
-> Index Scan using locations_pkey on locations (cost=0.43..6.77 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=161036)
Index Cond: (id = images.location_id)
Filter: active
Rows Removed by Filter: 0
Planning time: 1.694 ms
Execution time: 1556.352 ms
我们在 RDS db.m4.large 实例上运行 Postgres 9.4。
解决方案
至于查询本身,您唯一能做的就是跳过users
表格。从EXPLAIN
你可以看到它只是做了一个Index Only Scan
没有实际接触桌子的事情。因此,从技术上讲,您的查询可能如下所示:
SELECT images.* FROM images
INNER JOIN locations ON locations.id = images.location_id
INNER JOIN user_groups ON images.creator_id = user_groups.user_id
WHERE images.deleted_at IS NULL
AND user_groups.group_id = 7
AND images.creator_type = 'User'
AND images.status = 2
AND locations.active = TRUE
ORDER BY date_uploaded DESC
OFFSET 0 LIMIT 50
其余的都是关于索引的。locations
似乎数据很少,所以这里的优化不会给你带来任何好处。user_groups
另一方面,可以从索引ON (user_id) WHERE group_id = 7
或ON (group_id, user_id)
. 这应该消除对表格内容的一些额外过滤。
-- Option 1
CREATE INDEX ix_usergroups_userid_groupid7
ON user_groups (user_id)
WHERE group_id = 7;
-- Option 2
CREATE INDEX ix_usergroups_groupid_userid
ON user_groups (group_id, user_id);
当然,这里最重要的是images
。目前,刨床会进行索引扫描creator_date_uploaded_Where_pub_not_del
,我怀疑它不完全符合要求。在这里,根据您的使用模式,您会想到多种选择 - 从搜索参数相当常见的一种选择:
-- Option 1
CREATE INDEX ix_images_creatorid_typeuser_status2_notdel
ON images (creator_id)
WHERE creator_type = 'User' AND status = 2 AND deleted_at IS NULL;
到一个具有完全动态参数的:
-- Option 2
CREATE INDEX ix_images_status_creatortype_creatorid_notdel
ON images (status, creator_type, creator_id)
WHERE deleted_at IS NULL;
第一个索引更可取,因为它更小(值被过滤掉而不是索引)。
总而言之,除非您受到内存(或其他因素)的限制,否则我会在user_groups
和上添加索引images
。指数的正确选择必须通过经验来确认,因为通常有多种选择,而且情况取决于数据的统计分布。
推荐阅读
- c# - 在 React-Typescript Web 应用程序上使用 Msal.js 的 .net 核心 api 调用的 OAuth2 身份验证失败
- python - 如果不使用它,是否需要关闭 tempfile.mkstemp 返回给我的操作系统级句柄?
- python - 如何在 REST API POST 请求中发送 HTML 文件内容?
- c++ - Visual Studio:使用 concurrent_vector 时出现 C2027 使用“未定义类型”错误
> 在异步函数中 - android - 无法将 androidx.appcompat.widget.Toolbar 设置为 androidx.fragment.app.FragmentActivity
- javascript - 传入off函数的匿名函数是什么意思?
- python-3.x - 如何使用python根据列值根据优先级过滤数据框?
- reactjs - Typescript React 事件处理程序类型
- android - 模拟器有问题,因为启动活动需要很长时间
- javascript - '在发送到客户端后无法设置标题' - 我正在设置标题,但我找不到在哪里