查詢因附加 FROM 項目而變慢（即使沒有額外操作）

我comment在 Postgres 9.5 上有一個本地託管的表，大約有 260 萬行。當我執行以下查詢時，它會在 0.867 毫秒內執行：

SELECT COUNT(body)
FROM comment
WHERE body LIKE '%[deleted]%';

但以下查詢需要 > 50 秒（此時停止測量）：

SELECT COUNT(c1.body)
FROM comment c1, comment c2
WHERE c1.body LIKE '%[deleted]%';

我問是因為我正在嘗試實現如下查詢：

SELECT CAST(COUNT(c1.body)/COUNT(c2.body) as percent_deleted
FROM comment c1, comment c2
WHERE c1.body LIKE '%[deleted]%' 
AND c1.id = c2.id;

據我了解，如果您想知道哪些是“已刪除”行的百分比，請不要與同一個表交叉連接，獲取總行數併計算初始查詢的結果。

with totalc2 as 
(
   select count(*) nrows
   from comment
)
SELECT CAST(COUNT(c1.body) AS float) /totalc2.nrows) as percent_deleted
FROM comment c1
WHERE c1.body LIKE '%[deleted]%' 

您可以將其用作子查詢：

SELECT CAST(COUNT(c1.body) AS float) / (select count(*) from comment) as percent_deleted
FROM comment c1
WHERE c1.body LIKE '%[deleted]%' 

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