Postgresql
向查詢添加 LEFT JOIN 已將執行時間增加了 10 倍
在 CentOS 7.6 伺服器（Intel Core i7-6700、64 GB DDR4 RAM、RAID1 SSD）上，我執行一個用 PL/pgSQL 和 Java 編寫的後端，用於具有以下 Linux 軟體包的移動和桌面文字遊戲：
- postgresql10-server-10.6-1PGDG.rhel7.x86_64
- pgbouncer-1.9.0-1.rhel7.x86_64
- postgresql-jdbc-42.2.5-1.rhel7.noarch
以下是 postgresql.conf 中唯一修改的設置：
- 最大連接數 = 120
- work_mem = 8MB
- 維護工作記憶體 = 128MB
90% 的後端原始碼是生成 JSON 的儲存函式，其中一個函式是遊戲的主要核心，是對 7 個表的 SELECT 查詢。
每個連接 Websocket 的客戶端都會呼叫它，並為玩家提供一個活躍遊戲的 JSON 列表。
直到最近，查詢需要 1-2 秒才能完成，但在我使用下表添加 LEFT JOIN 後，查詢需要 7-10 秒才能完成，這讓遊戲玩起來很不愉快：
# \d words_geoip; Table "public.words_geoip" Column | Type | Collation | Nullable | Default --------+------------------+-----------+----------+--------- block | inet | | not null | lat | double precision | | | lng | double precision | | | Indexes: "words_geoip_pkey" PRIMARY KEY, btree (block) # select * from words_geoip order by random() limit 5; block | lat | lng -------------------+----------+----------- 217.72.221.128/25 | 48.26 | 11.434 71.183.37.0/24 | 40.9357 | -72.9809 190.174.132.0/22 | -34.6033 | -58.3817 24.72.74.128/25 | 50.5061 | -104.6752 73.155.238.0/23 | 29.5075 | -95.0895 (5 rows) # select count(*) from words_geoip; count --------- 3073410 (1 row)這是 SELECT 查詢（為了更好的可讀性，我刪除了儲存的函式和 ROW_TO_JSON 並註釋掉了 3 個新行）：
SELECT g.gid, EXTRACT(EPOCH FROM g.created)::int AS created, EXTRACT(EPOCH FROM g.finished)::int AS finished, g.letters AS letters, g.values AS values, g.bid AS bid, CARDINALITY(g.pile) AS pilelen, m.tiles AS tiles, m.score AS score, CASE WHEN g.player1 = 5 THEN g.player1 ELSE g.player2 END AS player1, CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END AS player2, CASE WHEN g.player1 = 5 THEN g.score1 ELSE g.score2 END AS score1, CASE WHEN g.player1 = 5 THEN g.score2 ELSE g.score1 END AS score2, CASE WHEN g.player1 = 5 THEN g.state1 ELSE g.state2 END AS state1, CASE WHEN g.player1 = 5 THEN g.hint1 ELSE g.hint2 END AS hint1, CASE WHEN g.player1 = 5 THEN g.chat1 ELSE g.chat2 END AS chat1, u1.elo AS elo1, u2.elo AS elo2, -- i2.lat AS lat2, -- i2.lng AS lng2, s1.given AS given1, s2.given AS given2, s1.photo AS photo1, s2.photo AS photo2, EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN g.played1 ELSE g.played2 END)::int AS played1, EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN g.played2 ELSE g.played1 END)::int AS played2, ARRAY_TO_STRING(CASE WHEN g.player1 = 5 THEN g.hand1 ELSE g.hand2 END, '') AS hand1, CASE WHEN g.finished IS NOT NULL THEN NULL WHEN g.player2 IS NULL THEN NULL WHEN g.player1 = 5 AND g.played1 < g.played2 THEN EXTRACT(EPOCH FROM g.played2 + interval '24 hour' - CURRENT_TIMESTAMP)::int WHEN g.player2 = 5 AND (g.played2 IS NULL OR g.played2 < g.played1) THEN EXTRACT(EPOCH FROM g.played1 + interval '24 hour' - CURRENT_TIMESTAMP)::int ELSE NULL END AS left1, CASE WHEN g.finished IS NOT NULL THEN NULL WHEN g.player2 IS NULL THEN NULL WHEN g.player1 = 5 AND (g.played2 IS NULL OR g.played2 < g.played1) THEN EXTRACT(EPOCH FROM g.played1 + interval '24 hour' - CURRENT_TIMESTAMP)::int WHEN g.player2 = 5 AND g.played1 < g.played2 THEN EXTRACT(EPOCH FROM g.played2 + interval '24 hour' - CURRENT_TIMESTAMP)::int ELSE NULL END AS left2 FROM words_games g LEFT JOIN words_moves m ON m.gid = g.gid AND NOT EXISTS (SELECT 1 FROM words_moves m2 WHERE m2.gid = m.gid AND m2.played > m.played) LEFT JOIN words_users u1 ON u1.uid = 5 LEFT JOIN words_users u2 ON u2.uid = (CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END) -- LEFT JOIN words_geoip i2 ON (CASE WHEN g.player1 = 5 THEN u2.ip ELSE u1.ip END) << i2.block LEFT JOIN words_social s1 ON s1.uid = 5 AND NOT EXISTS (SELECT 1 FROM words_social s WHERE s1.uid = s.uid AND s.stamp > s1.stamp) LEFT JOIN words_social s2 ON s2.uid = (CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END) AND NOT EXISTS (SELECT 1 FROM words_social s WHERE s2.uid = s.uid AND s.stamp > s2.stamp) WHERE 5 IN (g.player1, g.player2) AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1 day');這裡前面帶有 EXPLAIN ANALYZE 的查詢：
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Left Join (cost=117019.66..323100.50 rows=240 width=1414) (actual time=390.626..1518.867 rows=9 loops=1) Hash Cond: (CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END = s2.uid) -> Nested Loop Left Join (cost=115853.58..321900.39 rows=240 width=1760) (actual time=384.751..1512.913 rows=9 loops=1) -> Nested Loop Left Join (cost=115853.58..321042.49 rows=60 width=1672) (actual time=382.754..1510.864 rows=9 loops=1) -> Nested Loop Left Join (cost=115853.29..321033.43 rows=60 width=1668) (actual time=382.749..1510.850 rows=9 loops=1) -> Hash Right Join (cost=115853.00..320535.13 rows=60 width=1664) (actual time=382.736..1510.753 rows=9 loops=1) Hash Cond: (m.gid = g.gid) -> Hash Anti Join (cost=103356.07..305406.36 rows=1002521 width=212) (actual time=358.638..1488.564 rows=49721 loops=1) Hash Cond: (m.gid = m2.gid) Join Filter: (m2.played > m.played) Rows Removed by Join Filter: 3841937 -> Seq Scan on words_moves m (cost=0.00..77215.81 rows=1503781 width=220) (actual time=0.003..233.670 rows=1499874 loops=1) -> Hash (cost=77215.81..77215.81 rows=1503781 width=12) (actual time=358.205..358.205 rows=1499874 loops=1) Buckets: 262144 Batches: 16 Memory Usage: 6706kB -> Seq Scan on words_moves m2 (cost=0.00..77215.81 rows=1503781 width=12) (actual time=0.003..204.397 rows=1499874 loops=1) -> Hash (cost=12496.89..12496.89 rows=3 width=1456) (actual time=19.316..19.316 rows=9 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 22kB -> Seq Scan on words_games g (cost=0.00..12496.89 rows=3 width=1456) (actual time=15.134..19.304 rows=9 loops=1) Filter: (((5 = player1) OR (5 = player2)) AND ((finished IS NULL) OR (finished > (CURRENT_TIMESTAMP - '1 day'::interval)))) Rows Removed by Filter: 49730 -> Index Scan using words_users_pkey on words_users u2 (cost=0.29..8.30 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=9) Index Cond: (uid = CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END) -> Materialize (cost=0.29..8.31 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=9) -> Index Scan using words_users_pkey on words_users u1 (cost=0.29..8.30 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1) Index Cond: (uid = 5) -> Materialize (cost=0.00..854.91 rows=4 width=88) (actual time=0.222..0.227 rows=1 loops=9) -> Nested Loop Anti Join (cost=0.00..854.89 rows=4 width=88) (actual time=1.995..2.039 rows=1 loops=1) Join Filter: ((s.stamp > s1.stamp) AND (s1.uid = s.uid)) Rows Removed by Join Filter: 11 -> Seq Scan on words_social s1 (cost=0.00..427.20 rows=6 width=96) (actual time=0.112..0.990 rows=6 loops=1) Filter: (uid = 5) Rows Removed by Filter: 10652 -> Materialize (cost=0.00..427.23 rows=6 width=8) (actual time=0.015..0.174 rows=3 loops=6) -> Seq Scan on words_social s (cost=0.00..427.20 rows=6 width=8) (actual time=0.092..1.043 rows=6 loops=1) Filter: (uid = 5) Rows Removed by Filter: 10652 -> Hash (cost=1077.29..1077.29 rows=7104 width=92) (actual time=5.855..5.855 rows=10552 loops=1) Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1419kB -> Hash Anti Join (cost=533.76..1077.29 rows=7104 width=92) (actual time=1.918..4.456 rows=10552 loops=1) Hash Cond: (s2.uid = s_1.uid) Join Filter: (s_1.stamp > s2.stamp) Rows Removed by Join Filter: 10709 -> Seq Scan on words_social s2 (cost=0.00..400.56 rows=10656 width=96) (actual time=0.002..0.576 rows=10658 loops=1) -> Hash (cost=400.56..400.56 rows=10656 width=8) (actual time=1.906..1.906 rows=10658 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 545kB -> Seq Scan on words_social s_1 (cost=0.00..400.56 rows=10656 width=8) (actual time=0.001..1.098 rows=10658 loops=1) Planning time: 0.409 ms Execution time: 1518.996 ms (48 rows)和這里相同的查詢，但我已經刪除了
--字元，因此添加了針對words_geoip錶的 LEFT JOIN：QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Left Join (cost=117019.66..5257508.21 rows=3688092 width=1430) (actual time=1383.205..5279.226 rows=9 loops=1) Hash Cond: (CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END = s2.uid) -> Nested Loop Left Join (cost=115853.58..4733610.67 rows=3688092 width=1776) (actual time=1376.856..5272.785 rows=9 loops=1) -> Nested Loop Left Join (cost=115853.58..4686654.61 rows=922023 width=1688) (actual time=1374.768..5270.644 rows=9 loops=1) Join Filter: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END << i2.block) Rows Removed by Join Filter: 27660682 -> Nested Loop Left Join (cost=115853.58..321042.49 rows=60 width=1686) (actual time=618.110..1531.740 rows=9 loops=1) -> Nested Loop Left Join (cost=115853.29..320544.19 rows=60 width=1675) (actual time=618.105..1531.642 rows=9 loops=1) -> Hash Right Join (cost=115853.00..320535.13 rows=60 width=1664) (actual time=618.093..1531.613 rows=9 loops=1) Hash Cond: (m.gid = g.gid) -> Hash Anti Join (cost=103356.07..305406.36 rows=1002521 width=212) (actual time=363.819..1510.167 rows=49721 loops=1) Hash Cond: (m.gid = m2.gid) Join Filter: (m2.played > m.played) Rows Removed by Join Filter: 4074631 -> Seq Scan on words_moves m (cost=0.00..77215.81 rows=1503781 width=220) (actual time=0.007..235.666 rows=1499877 loops=1) -> Hash (cost=77215.81..77215.81 rows=1503781 width=12) (actual time=363.537..363.537 rows=1499877 loops=1) Buckets: 262144 Batches: 16 Memory Usage: 6706kB -> Seq Scan on words_moves m2 (cost=0.00..77215.81 rows=1503781 width=12) (actual time=0.002..204.827 rows=1499877 loops=1) -> Hash (cost=12496.89..12496.89 rows=3 width=1456) (actual time=18.521..18.521 rows=9 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 22kB -> Seq Scan on words_games g (cost=0.00..12496.89 rows=3 width=1456) (actual time=12.417..18.511 rows=9 loops=1) Filter: (((5 = player1) OR (5 = player2)) AND ((finished IS NULL) OR (finished > (CURRENT_TIMESTAMP - '1 day'::interval)))) Rows Removed by Filter: 49730 -> Materialize (cost=0.29..8.31 rows=1 width=11) (actual time=0.001..0.002 rows=1 loops=9) -> Index Scan using words_users_pkey on words_users u1 (cost=0.29..8.30 rows=1 width=11) (actual time=0.008..0.010 rows=1 loops=1) Index Cond: (uid = 5) -> Index Scan using words_users_pkey on words_users u2 (cost=0.29..8.30 rows=1 width=15) (actual time=0.008..0.008 rows=1 loops=9) Index Cond: (uid = CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END) -> Materialize (cost=0.00..83684.15 rows=3073410 width=23) (actual time=0.004..228.492 rows=3073410 loops=9) -> Seq Scan on words_geoip i2 (cost=0.00..50308.10 rows=3073410 width=23) (actual time=0.008..248.647 rows=3073410 loops=1) -> Materialize (cost=0.00..854.91 rows=4 width=88) (actual time=0.232..0.237 rows=1 loops=9) -> Nested Loop Anti Join (cost=0.00..854.89 rows=4 width=88) (actual time=2.085..2.129 rows=1 loops=1) Join Filter: ((s.stamp > s1.stamp) AND (s1.uid = s.uid)) Rows Removed by Join Filter: 11 -> Seq Scan on words_social s1 (cost=0.00..427.20 rows=6 width=96) (actual time=0.114..1.029 rows=6 loops=1) Filter: (uid = 5) Rows Removed by Filter: 10652 -> Materialize (cost=0.00..427.23 rows=6 width=8) (actual time=0.015..0.183 rows=3 loops=6) -> Seq Scan on words_social s (cost=0.00..427.20 rows=6 width=8) (actual time=0.087..1.094 rows=6 loops=1) Filter: (uid = 5) Rows Removed by Filter: 10652 -> Hash (cost=1077.29..1077.29 rows=7104 width=92) (actual time=6.326..6.326 rows=10552 loops=1) Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1419kB -> Hash Anti Join (cost=533.76..1077.29 rows=7104 width=92) (actual time=1.966..4.600 rows=10552 loops=1) Hash Cond: (s2.uid = s_1.uid) Join Filter: (s_1.stamp > s2.stamp) Rows Removed by Join Filter: 10709 -> Seq Scan on words_social s2 (cost=0.00..400.56 rows=10656 width=96) (actual time=0.002..0.622 rows=10658 loops=1) -> Hash (cost=400.56..400.56 rows=10656 width=8) (actual time=1.952..1.952 rows=10658 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 545kB -> Seq Scan on words_social s_1 (cost=0.00..400.56 rows=10656 width=8) (actual time=0.001..1.130 rows=10658 loops=1) Planning time: 0.511 ms Execution time: 5292.006 ms (53 rows)我可能遺漏了一些小東西，但我沒有調整 SQL 查詢的經驗，而且整個查詢非常廣泛。請幫忙
更新：
以下是查詢中使用的其他 4 個表 -
# \d words_games Table "public.words_games" Column | Type | Collation | Nullable | Default ----------+--------------------------+-----------+----------+------------------------------------------ gid | integer | | not null | nextval('words_games_gid_seq'::regclass) created | timestamp with time zone | | not null | finished | timestamp with time zone | | | player1 | integer | | not null | player2 | integer | | | played1 | timestamp with time zone | | | played2 | timestamp with time zone | | | state1 | text | | | state2 | text | | | reason | text | | | hint1 | text | | | hint2 | text | | | score1 | integer | | not null | score2 | integer | | not null | chat1 | integer | | not null | chat2 | integer | | not null | hand1 | character(1)[] | | not null | hand2 | character(1)[] | | not null | pile | character(1)[] | | not null | letters | character(1)[] | | not null | values | integer[] | | not null | bid | integer | | not null | friendly | boolean | | | Indexes: "words_games_pkey" PRIMARY KEY, btree (gid) "words_games_reason_index" btree (reason) "words_games_state1_index" btree (state1) "words_games_state2_index" btree (state2) Check constraints: "words_games_chat1_check" CHECK (chat1 >= 0) "words_games_chat2_check" CHECK (chat2 >= 0) "words_games_check" CHECK (player1 <> player2) "words_games_score1_check" CHECK (score1 >= 0) "words_games_score2_check" CHECK (score2 >= 0) Foreign-key constraints: "words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid) ON DELETE CASCADE "words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES words_users(uid) ON DELETE CASCADE "words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES words_users(uid) ON DELETE CASCADE Referenced by: TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE # \d words_users Table "public.words_users" Column | Type | Collation | Nullable | Default ---------------+--------------------------+-----------+----------+------------------------------------------ uid | integer | | not null | nextval('words_users_uid_seq'::regclass) created | timestamp with time zone | | not null | visited | timestamp with time zone | | not null | ip | inet | | not null | fcm | text | | | apns | text | | | adm | text | | | motto | text | | | vip_until | timestamp with time zone | | | grand_until | timestamp with time zone | | | banned_until | timestamp with time zone | | | banned_reason | text | | | elo | integer | | not null | medals | integer | | not null | coins | integer | | not null | Indexes: "words_users_pkey" PRIMARY KEY, btree (uid) Check constraints: "words_users_banned_reason_check" CHECK (length(banned_reason) > 0) "words_users_elo_check" CHECK (elo >= 0) "words_users_medals_check" CHECK (medals >= 0) Referenced by: TABLE "words_chat" CONSTRAINT "words_chat_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_games" CONSTRAINT "words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_games" CONSTRAINT "words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_moves" CONSTRAINT "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_reviews" CONSTRAINT "words_reviews_author_fkey" FOREIGN KEY (author) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_reviews" CONSTRAINT "words_reviews_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_scores" CONSTRAINT "words_scores_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_social" CONSTRAINT "words_social_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_stats" CONSTRAINT "words_stats_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE # \d words_social Table "public.words_social" Column | Type | Collation | Nullable | Default --------+------------------+-----------+----------+--------- sid | text | | not null | social | integer | | not null | given | text | | not null | family | text | | | photo | text | | | lat | double precision | | | lng | double precision | | | stamp | integer | | not null | uid | integer | | not null | Indexes: "words_social_pkey" PRIMARY KEY, btree (sid, social) Check constraints: "words_social_given_check" CHECK (given ~ '\S'::text) "words_social_photo_check" CHECK (photo ~* '^https?://...'::text) "words_social_social_check" CHECK (0 < social AND social <= 64) Foreign-key constraints: "words_social_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE Referenced by: TABLE "words_payments" CONSTRAINT "words_payments_sid_fkey" FOREIGN KEY (sid, social) REFERENCES words_social(sid, social) ON DELETE CASCADE # \d words_moves Table "public.words_moves" Column | Type | Collation | Nullable | Default ---------+--------------------------+-----------+----------+------------------------------------------ mid | bigint | | not null | nextval('words_moves_mid_seq'::regclass) action | text | | not null | gid | integer | | not null | uid | integer | | not null | played | timestamp with time zone | | not null | tiles | jsonb | | | score | integer | | | letters | text | | | hand | text | | | puzzle | boolean | | not null | false Indexes: "words_moves_pkey" PRIMARY KEY, btree (mid) Check constraints: "words_moves_score_check" CHECK (score >= 0) Foreign-key constraints: "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE Referenced by: TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE更新 2：
報告了 6 次“Seq Scan”，我必須通過添加索引來擺脫它，對嗎？
所以我已經添加了 2 並且時間有所改善：
CREATE INDEX words_moves_gid_index ON words_moves(gid); CREATE INDEX words_moves_uid_index ON words_social(uid);但不幸的是我還是不明白，為 words_geoip 添加哪個 INDEX，因為已經有一個（PK）？
在pgsql-general郵件列表中的 Andrew Gierth 先生和IRC #postgresql的使用者xocolatl、azeem和aditsu的友好幫助下，查詢已通過添加以下索引進行了優化（7-10 秒 -> 0.0003 秒）：
CREATE INDEX ON words_games (player1, COALESCE(finished, 'INFINITY')); CREATE INDEX ON words_games (player2, COALESCE(finished, 'INFINITY')); CREATE INDEX ON words_moves (gid, played DESC); CREATE INDEX ON words_social (uid, stamp DESC); CREATE INDEX ON words_geoip USING SPGIST (block);並切換到 LEFT JOIN LATERAL 以查找 words_moves 和 words_social 表中的最新記錄：
SELECT ... -- find move record with the most recent timestamp LEFT JOIN LATERAL (SELECT * FROM words_moves m WHERE m.gid = g.gid ORDER BY m.played DESC LIMIT 1) AS m ON TRUE LEFT JOIN words_geoip i2 ON (CASE WHEN g.player1 = in_uid THEN u2.ip ELSE u1.ip END) <<= i2.block LEFT JOIN LATERAL (SELECT * FROM words_social AS s1 WHERE s1.uid = u1.uid ORDER BY s1.stamp DESC LIMIT 1) AS s1 ON TRUE LEFT JOIN LATERAL (SELECT * FROM words_social AS s2 WHERE s2.uid = u2.uid ORDER BY s2.stamp DESC LIMIT 1) AS s2 ON TRUE ... WHERE in_uid IN (g.player1, g.player2) AND COALESCE(g.finished, 'INFINITY') > (CURRENT_TIMESTAMP - INTERVAL '1 day')我還在 postgresql.conf 中增加了以下參數 -
from_collapse_limit = 24 join_collapse_limit = 24最後，這裡是完整查詢和ANALYZE EXPLAIN輸出的連結。