從 am:n 表中有效地返回兩個聚合數組

我有一個表用於表上的多對多關係，users以表示使用者之間的跟隨關係：

CREATE TABLE users (
   id text PRIMARY KEY,
   username text NOT NULL
);

CREATE TABLE followers (
   userid text,
   followid text,
   PRIMARY KEY (userid, followid),
   CONSTRAINT followers_userid_fk   FOREIGN KEY (userid)   REFERENCES users (id),
   CONSTRAINT followers_followid_fk FOREIGN KEY (followid) REFERENCES users (id)
);

CREATE INDEX followers_followid_idx ON followers (followid);

當我想使用與使用者相關的數據創建 JSON 響應時，我有兩種情況：

• 通過 id 請求單個使用者，
• 通過 id 列表請求使用者對像數組

使用者數據對象應包含兩個使用者 ID 數組，一個是他們關注的使用者，另一個是關注他們的使用者。為了創建這兩個欄位，我使用了以下SELECT語句。

DECLARE follows RECORD;
SELECT  array (select followid FROM followers where userid = Puserid) AS following, 
   array (select userid FROM followers where followid = Puserid) AS followers 
INTO follows;

當請求針對使用者列表時，我想為 JSON 使用者列表中返回的每個使用者對象創建這兩個欄位。

我選擇將關注關係實現為多對多表，這樣我就不必從使用者（或使用者配置文件）表中包含的數組中搜尋和刪除 id，而且將來我可能會添加關於關注的元數據關係（可能是通知設置或阻止使用者等。

但是，我開始懷疑這個決定的效率，特別是在為 200 個使用者發出許多請求的情況下，我認為這將為SELECT列表中的每個 hte id 執行上述查詢。這會非常低效嗎？

我在兩列上都有索引（因為主鍵索引對搜尋沒有用followid），但我正在考慮創建一個包含followid列的 array_agg 的視圖：

SELECT userid, array_agg(followid) as following
FROM followers
GROUP BY userid;

但是要了解關注者和追隨者，我需要以下內容：

SELECT f1.userid, array_agg(f1.followid) as following, 
   f2.followers FROM followers AS f1 INNER JOIN
   (select followid AS userid, array_agg(userid) as followers
from ks.followers
   group by followid) AS f2 ON f1.userid = f2.userid group by f1.userid, f2.followers;

這不是一個好主意，對吧？

我是否採取了錯誤的方法來建模使用者之間的這種關係？

我對此進行了兩次嘗試，對於 18 個 id 的簡短列表，這兩次都需要大約 600 毫秒：

嘗試 1

CREATE OR REPLACE VIEW follow_following AS
   select f1.userid, array_agg(f1.followid) as following, 
   f2.followers FROM followers AS f1 INNER JOIN
   (select followid AS userid, array_agg(userid) as followers
   from followers
   group by followid) AS f2 ON f1.userid = f2.userid group by f1.userid, f2.followers;

CREATE OR REPLACE FUNCTION get_users_by_ids(Puserids TEXT[])
   RETURNS JSON AS $$
   DECLARE rjson JSON;
   BEGIN
       CREATE TEMP TABLE getusers ON COMMIT DROP AS
       SELECT u.id, u.username, p.bio, p.avatar, f.followers, f.following
       FROM users u
       INNER JOIN profiles p
       ON u.id = p.userid
       LEFT OUTER JOIN follow_following f 
       ON u.id = f.userid
       WHERE u.id = ANY(Puserids);

       SELECT INTO rjson json_agg (
           json_build_object (
               'data',json_build_object (
                   'id',getusers.id,
                   'username',getusers.username,
                   'bio',getusers.bio,
                   'avatar',getusers.avatar,
                   'following', getusers.following,
                   'followers', getusers.followers
               )
           )
       ) FROM getusers;
       return rjson;
   END;
$$ LANGUAGE plpgsql SECURITY DEFINER

嘗試 2

當我循環數組而不是使用視圖時，此函式在 17 個 id 的數組上執行大致相同（650 毫秒）：

CREATE OR REPLACE FUNCTION get_users_by_ids(Puserids TEXT[])
RETURNS JSON AS $$
DECLARE 
   rjson JSON;
   uid   TEXT;
BEGIN
   CREATE TEMP TABLE getusers (
       userid text,
       username text,
       following text[],
       followers text[]
   ) ON COMMIT DROP;

   FOREACH uid IN ARRAY Puserids
   LOOP
       INSERT INTO getusers (userid, username, followers, following)
       SELECT u.id, u.username,
           array (select userid FROM followers where followid = uid) AS followers,
           array (select followid FROM followers where userid = uid) AS following
       FROM ks.users u
       WHERE u.id = uid;
   END LOOP;

   SELECT INTO rjson json_agg (
       json_build_object (
           'id',getusers.userid,
           'username',getusers.username,
           'following', getusers.following,
           'followers', getusers.followers
       )
   ) FROM getusers;

   return json_build_object ('data', rjson);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER

詢問

為此目的，創建臨時表和循環是昂貴的過度殺傷力。首先，您甚至不需要 plpgsql - 儘管在同一會話中重複呼叫可能會稍微快一些。從根本上簡化：

CREATE OR REPLACE FUNCTION get_users_by_ids(_uids text[])
 RETURNS JSON
 LANGUAGE sql SECURITY DEFINER AS
$func$
  SELECT json_agg(sub)
  FROM (
     SELECT u.id, u.username
          , ARRAY (SELECT followid FROM followers WHERE userid   = u.id) AS following
          , ARRAY (SELECT userid   FROM followers WHERE followid = u.id) AS followers
     FROM   users u
     WHERE  u.id = ANY (_uids)
     ) sub
$func$;

我json_agg()在子查詢上使用 a 而不是json_build_object(). 應該會快一點，但是。有關的：

• 選擇 json_agg 中的列

如果您需要，它可以方便地允許對數組元素進行廉價排序：添加ORDER BY子查詢。您可能希望保留元素的原始順序。看：

• 帶有元素編號的 PostgreSQL unnest()

如果您需要SECURITY DEFINER（真的嗎？），請確保它不會被濫用。請參閱此 Postgres Wiki 頁面：

CVE-2018-1058 指南：保護您的搜尋路徑

相關子查詢在這裡應該是最快的；如果沒有找到following，你會得到 NULL 。followers或者，可以使用LATERAL連接。有關的：

• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？
• 優化 GROUP BY 查詢以檢索每個使用者的最新記錄

如果您需要將所有內容嵌套在*“數據”*鍵中，則可以輕鬆添加，但這似乎只是噪音。

一個VARIADIC參數_uids可能很方便：

• 如何在 PostgreSQL 中使用數組作為 VARIADIC 函式的參數？

（但列表輸入最多只允許 100 個參數。您仍然可以傳遞任意長度的數組。）

指數

要允許僅索引掃描，請創建二級索引followers_followid_idx而(followid, userid)不是僅(followid). 有關的：

• Postgres 可以對這個帶有連接表的查詢使用僅索引掃描嗎？

數據庫設計

標準化設計是個好主意。它有助於提高寫入速度，並在處理以下內容時防止廣泛的表膨脹和鎖定爭用。它在許多其他方面都更勝一籌。

不過，我強烈建議使用**integer**ID。更小，更快。索引的最佳大小。有關的：

• 複合索引是否也適用於第一個欄位的查詢？

您總是可以額外輸出文本 ID 。

引用自：https://dba.stackexchange.com/questions/204688