主页 / dba / 问题 / 93241
Accepted
Alexis
Asked: 2015-02-19 10:18:26 +0800 CST

从查询中排除现有朋友的最佳查询

  • 772

我有一个用户表

/ id_user / username / ..

朋友表（AB 和 BA 对）：

/ friend_of / friend_to

和一个 friend_request 表（用于待处理的好友请求）

/ id_req/ sent_from / sent_to

我想稍微搜索一下新朋友功能（自动建议样式），并且我不想在列表中显示已经在我的朋友列表中或处于待处理的朋友请求中的朋友。

查询背后的想法是显示用户的用户名不在 friend_to 列中，也不在 sent_to 列中。

我想出了这两种口味：

select id_user,username 
from public.user u 
where 
username like '%tom%' 
and 
u.id_user NOT IN (SELECT sent_to FROM friend_request where sent_from = 288) 
AND 
u.id_user NOT IN (SELECT friend_to FROM friends where friend_of=288);

或者

select id_user,username
from public.user u 
left join friend_request fr 
on 
u.id_user=sent_to AND fr.sent_from=288
left join friends f 
on 
u.id_user = f.friend_to AND f.friend_of=288
where username like '%tom%' AND sent_to IS NULL and friend_to IS NULL;

这两个产生相同的结果。但我想要一些建议，哪一个是更好的选择性能和明智的投注实践？

postgresql optimization

1 个回答

  1. Best Answer

    NOT IN通常是最慢的选项。LEFT JOIN/IS NULL更有前途。或者NOT EXISTS：

    询问

    只是您的查询，格式为：

    SELECT u.id_user, u.username
FROM   "user" u 
LEFT   JOIN friend_request fr ON fr.sent_to = u.id_user
                             AND fr.sent_from = 288
LEFT   JOIN friends f ON f.friend_to = u.id_user
                     AND f.friend_of = 288
WHERE  u.username LIKE '%tom%'
AND    fr.sent_to IS NULL
AND    f.friend_to IS NULL;

    或者，具有NOT EXISTS反半连接的变体：

    SELECT u.id_user, u.username
FROM   "user" u
WHERE  NOT EXISTS (
   SELECT 1 FROM friend_request
   WHERE  sent_to = u.id_user
   AND    sent_from = 288
   ) 
AND    NOT EXISTS (
   SELECT 1 FROM friends
   WHERE  friend_to = u.id_user
   AND    friend_of = 288
   )
WHERE  u.username LIKE '%tom%';

    索引

    性能的最重要因素。一个理想的设置应该有：

    1. 上的trigram GIN 或 GiST 索引。"user".username您需要附加模块pg_trgm。说明 - 以及 Gin 或 GiST？

      如果可以进行仅索引扫描，请附加其他无用的id_user. 您需要附加模块btree_gin（或btree_gist分别）：

      然后：

      CREATE INDEX usr_trgm_idx ON "user" USING gin (username gin_trgm_ops, id_user);

    2. 多列索引（或 UNIQUE/PK 约束）(sent_from, sent_to)和(friend_of, friend_to)- 列按该顺序排列。相反的顺序也可以，但效果不佳。每个查询检查单个 friend_of与许多不同 friend_to的组合，这使用建议的列顺序会更快，因为这样元组更容易定位在相同或几个索引页上。

    也就是说，布丁的证明仍在吃中。EXPLAIN (BUFFERS, ANALYZE)从您的生产数据库中获取最终结论。

    在旁边

    不要调用你的表，user那是一个保留字。

    • 2

相关问题