如何使用反連接加速查詢

我有一個帶有 2 個反連接（UserEmails = 1M+ 行和Subscriptions = <100k 行）、2 個條件和一個排序的查詢。我為 2 個條件 + 排序創建了一個索引，它將查詢速度提高了 50%。兩個反連接都有索引。但是，查詢太慢了（生產時需要 4 秒）。

這是查詢：

SELECT
   "Users"."firstName",
   "Users"."lastName",
   "Users"."email",
   "Users"."id"
FROM
   "Users"
WHERE
   NOT EXISTS (
       SELECT
           1
       FROM
           "UserEmails"
       WHERE
           "UserEmails"."userId" = "Users". ID
   )
AND NOT EXISTS (
   SELECT
       1
   FROM
       "Subscriptions"
   WHERE
       "Subscriptions"."userId" = "Users". ID
)
AND "isEmailVerified" = TRUE
AND "emailUnsubscribeDate" IS NULL
ORDER BY
   "Users"."createdAt" DESC
LIMIT 100

這是解釋：

Limit  (cost=1.28..177.77 rows=100 width=49) (actual time=6171.121..6171.850 rows=100 loops=1)
 ->  Nested Loop Anti Join  (cost=1.28..4665810.76 rows=2643614 width=49) (actual time=6171.119..6171.807 rows=100 loops=1)
       ->  Nested Loop Anti Join  (cost=0.86..3470216.17 rows=2707688 width=49) (actual time=0.809..6062.152 rows=28607 loops=1)
             ->  Index Scan using users_email_subscribers_idx on "Users"  (cost=0.43..1844686.50 rows=3312999 width=49) (actual time=0.055..2342.793 rows=1186607 loops=1)
             ->  Index Only Scan using "UserEmails_userId_emailId_key" on "UserEmails"  (cost=0.43..0.49 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1186607)
                   Index Cond: ("userId" = "Users".id)
                   Heap Fetches: 1153034
       ->  Index Only Scan using "Subscriptions_userId_type_key" on "Subscriptions"  (cost=0.42..0.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=28607)
             Index Cond: ("userId" = "Users".id)
             Heap Fetches: 28507
Planning time: 2.346 ms
Execution time: 6171.963 ms

這是將速度提高 50% 的指標：

CREATE INDEX  "users_email_subscribers_idx" ON "public"."Users" USING btree("createdAt" DESC) WHERE "isEmailVerified" = TRUE AND "emailUnsubscribeDate" IS NULL;

編輯：我還應該提到users_email_subscribers_idx正在顯示索引掃描而不是僅索引掃描，因為索引會定期更新。

您最好的選擇可能是在應用程序級別解決這個問題。這看起來像是您在數據清理練習中執行的查詢。如果是這樣，你為什麼關心它是否需要 6 秒來執行，為什麼你將它限制為 100 行而不是一次讀取所有行？也許您可以使用物化視圖或其他一些記憶體機制。如果您拒絕該選項，請繼續閱讀一些“次佳”選項。

我還應該提到 users_email_subscribers_idx 可能顯示的是索引掃描，而不是僅索引掃描，因為索引會定期更新。

這不是為什麼。您需要使用者表中未包含在索引中的列，例如 firstName 和 id。如果您在列列表末尾創建了所有這些列的索引，您將獲得僅索引掃描。這可能會使查詢速度提高 20%，但不會使其速度提高 99%。

               Heap Fetches: 1153034

您需要更積極地清理使用者電子郵件。同樣，這不會是 99% 的改進，但它應該會有所幫助。Autovacuum 在保持表足夠真空以優化僅索引掃描方面做得不好。你可以做手動吸塵器。或者您可以嘗試通過將“autovacuum_vacuum_scale_factor”的每個表設置降低為零來強制 autovacuum 做得更好，然後將每個表“autovacuum_vacuum_threshold”設置為控制清理。如果表在整個表中隨機更新，我會將“autovacuum_vacuum_threshold”設置為表中塊數的大約 1/20。

如果您進行實驗，查詢將如何執行set enable_nestedloop to off？這可能會給你散列反連接，如果你的版本足夠新，你可能會得到它們的並行版本。

計劃者估計的行數與實際行數之間存在巨大差異。這意味著計劃者選擇了基於虛假資訊的計劃。

例如，Nested Loop Anti Join (cost=0.86..3470216.17 rows=2707688 width=49) (actual time=0.809..6062.152 rows=28607 loops=1)表示他估計他會得到 2 707 688，而他實際上得到了 28 607。

要麼你的統計數據不准確（如果你從未調整autovacuum過那些巨大表的設置，我敢打賭），要麼你有一列依賴於另一列，而另一列不是鍵的一部分（違反第三範式）。

要更頻繁地刷新靜態數據，您可以調整autovacuum這些大表的設置。我強烈建議您閱讀該部落格文章以了解 autovacuum 調整。

如果您的模型違反了第三範式，您可以糾正您的模型（成本高，但從長遠來看更好），或者讓刨床收集您相關列的統計資訊（請參閱此處create statistics的文件）。

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