在只有結果 > 0 重要的情況下提高計數的性能

我有以下查詢：

select count(*) -- rp.tag
from submission 
inner join post rp on submission.id = rp.submission_id 
where user_name = ?1
and lower(rp.tag) like ('%verified%')
and not lower(rp.tag) like ('%unverified%');

如果結果大於 0，我會在我的應用程序中為這個使用者做一些事情。

我針對大量使用者執行此查詢，完成整個過程需要將近一個小時。而我只需要知道結果是否大於0。

rp.tag如果有任何符合我的條件，有沒有辦法提前完成查詢？

您可以添加LIMIT 1，但在LIMIT聚合之後應用（因此在計數之後）。將它添加到相同的查詢級別沒有任何用處，因為無論如何只剩下 1 行要返回：行數。會誤導人的廢話。

如果您想走那條路線，請添加LIMIT一個子查詢並在 external 中計數SELECT，例如：

SELECT count(*) FROM **(**
  SELECT             -- empty SELECT list
  FROM   submission
  JOIN   ...
  WHERE  ...
  **LIMIT 1
)** sub

對於這個特殊目的，將SELECT列表留空是最快的。count(*)只計算行的存在而不考慮任何列。

你可以用any LIMIT做到這一點，真的。檢查是否至少有N行。看：

• 在 PostgreSQL 中發現表的行數的快速方法

檢查N = 1只是您的特例。雖然我們只關心是否存在任何行而不是多少行，EXISTS但更好、更快的工具是：

SELECT EXISTS (
  SELECT FROM submission s
  JOIN   post p ON s.id = p.submission_id 
  WHERE  s.user_name = ?
  AND    p.tag ~* 'verified'
  AND    NOT p.tag ~* 'unverified'
  );

沒有索引，lower(col) LIKE '%something%'比col ~* 'something'. 但是，如果您的表很大並且性能很重要（如您的問題所示），那麼您應該首先具有匹配的索引，準確地說是三元索引**。**tag假設標籤來自表格post（您在那裡留下了解釋的空間）：

CREATE INDEX post_tag_gin_trgm_idx ON post USING gin (tag gin_trgm_ops);

那麼更簡單的表達式也一樣快。看：

• LOWER LIKE vs iLIKE
• LIKE 是如何實現的？

根據submission_id基數和數據分佈，將其與多列索引結合起來可能是有意義的：

CREATE INDEX post_foo_trgm_idx ON post USING gin (submission_id, tag gin_trgm_ops);

為此，您需要額外的模組 btree_gin。看：

• 相似度函式的最佳索引

submission(id, user_name)並且在- 或 on 上設置另一個多列索引可能是有意義的submission(user_name, id)，這再次取決於提到的細節。如果 Postgres 期望謂詞 onsubmission.user_name更具選擇性，第一個變體可能更有用。

（您對標籤的處理可能會進一步優化。）

在旁邊：

如果結果大於 0，我會在我的應用程序中為這個使用者做一些事情。

如果那是另一個 SQL DML 語句，請將其設為單個語句以獲得額外收益。例子：

UPDATE foo
SET    bar = 'baz'
WHERE  EXISTS ( ...) -- like above
AND    bar IS DISTINCT FROM 'baz' -- avoid empty updates

關於添加的最後一行：

• 我如何（或我可以）在多列上選擇 DISTINCT？

或者您將表達式嵌入到 plpgsql 程式碼中以滿足程序需求，如下所示：

• PL/pgSQL 檢查一行是否存在

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