Postgres 查询优化

发布于 2024-08-31 01:24:55 字数 774 浏览 5 评论 0原文

大家好，尝试优化此查询以解决重复的用户问题：

SELECT userid, 'ismaster' AS name, 'false' AS propvalue FROM user 
WHERE userid NOT IN (SELECT userid FROM userprop WHERE name = 'ismaster');

问题是 NOT IN 之后的选择是 120.000 条记录，而且需要很长时间。

按照评论中的建议使用解释前缀会返回：

                                    QUERY PLAN

--------------------------------------------------------------------------------
--
 Seq Scan on user  (cost=5559.38..122738966.99 rows=61597 width=8)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=5559.38..7248.33 rows=121395 width=8)
           ->  Seq Scan on userprop  (cost=0.00..4962.99 rows=121395 width=8
)
                 Filter: ((name)::text = 'ismaster'::text)
(6 rows)

有什么建议吗？

原文

Hey guys, trying to optimize this query to solve a duplicate user issue:

SELECT userid, 'ismaster' AS name, 'false' AS propvalue FROM user 
WHERE userid NOT IN (SELECT userid FROM userprop WHERE name = 'ismaster');

The problem is that the select after the NOT IN is 120.000 records and it's taking forever.

Using the explain prefix as suggested in the comments returns:

                                    QUERY PLAN

--------------------------------------------------------------------------------
--
 Seq Scan on user  (cost=5559.38..122738966.99 rows=61597 width=8)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=5559.38..7248.33 rows=121395 width=8)
           ->  Seq Scan on userprop  (cost=0.00..4962.99 rows=121395 width=8
)
                 Filter: ((name)::text = 'ismaster'::text)
(6 rows)

Any suggestion?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我还不会笑 2024-09-07 01:24:55

您是否在 userid 上添加了索引？

或者尝试另一种变体：

SELECT userid, 'ismaster' AS name, 'false' AS propvalue FROM user 
WHERE NOT EXISTS 
 (SELECT * FROM userprop 
 WHERE userpop.userid = user.userid 
   AND name = 'ismaster');

Did you put index on userid?

Or try another variation:

SELECT userid, 'ismaster' AS name, 'false' AS propvalue FROM user 
WHERE NOT EXISTS 
 (SELECT * FROM userprop 
 WHERE userpop.userid = user.userid 
   AND name = 'ismaster');

回复收藏 0 原文

东风软 2024-09-07 01:24:55

名称列是否已建立索引？名称值的选择性如何？
此外，每当您希望有人建议对查询进行更改时，请提供查询计划，即使是看似简单的查询。这样我们就真正知道规划者在做什么。

回复收藏 0 原文

鸠书 2024-09-07 01:24:55

根据这个答案，使用 LEFT JOIN ... IS NULL 可能比 NOT EXISTS 更快或更慢，具体取决于 RDBMS，尽管它们在 PostGres 上是等效的。

SELECT u.userid, 'ismaster' AS name, 'false' AS propvalue FROM user u
LEFT JOIN userprop up ON u.userid = up.userid AND up.name <> 'ismaster'
WHERE up.userid IS NULL

According to this answer, using a LEFT JOIN ... IS NULL might be either faster or slower than NOT EXISTS, depending on the RDBMS, though they're equivalent on PostGres.

SELECT u.userid, 'ismaster' AS name, 'false' AS propvalue FROM user u
LEFT JOIN userprop up ON u.userid = up.userid AND up.name <> 'ismaster'
WHERE up.userid IS NULL

回复收藏 0 原文

~没有更多了~