使用 GROUP BY 和 ORDER BY 對大表進行慢速查詢

我有一個包含 720 萬個元組的表，如下所示：

                              table public.methods
column |          type         |                      attributes
--------+-----------------------+----------------------------------------------------
id     | integer               | not null DEFAULT nextval('methodkey'::regclass)
hash   | character varying(32) | not null
string | character varying     | not null
method | character varying     | not null
file   | character varying     | not null
type   | character varying     | not null
Indexes:
   "methods_pkey" PRIMARY KEY, btree (id)
   "methodhash" btree (hash)

現在我想選擇一些值，但查詢速度非常慢：

db=# explain 
   select hash, string, count(method) 
   from methods 
   where hash not in 
         (select hash from nostring) 
   group by hash, string 
   order by count(method) desc;
                                           QUERY PLAN
----------------------------------------------------------------------------------------
Sort  (cost=160245190041.10..160245190962.07 rows=368391 width=182)
  Sort Key: (count(methods.method))
  ->  GroupAggregate  (cost=160245017241.77..160245057764.73 rows=368391 width=182)
      ->  Sort  (cost=160245017241.77..160245026451.53 rows=3683905 width=182)
            Sort Key: methods.hash, methods.string
            ->  Seq Scan on methods  (cost=0.00..160243305942.27 rows=3683905 width=182)
                  Filter: (NOT (SubPlan 1))
                  SubPlan 1
                  ->  Materialize  (cost=0.00..41071.54 rows=970636 width=33)
                    ->  Seq Scan on nostring  (cost=0.00..28634.36 rows=970636 width=33)

該hash列是 md5 散列string並具有索引。所以我認為我的問題是整個表是按id排序而不是按hash排序的，所以先排序再分組需要一段時間？

該表nostring僅包含我不想擁有的雜湊列表。但我需要兩個表都有所有值。因此，刪除這些不是一個選項。

附加資訊：所有列都不能為空（在表定義中已修復）並且我使用的是 postgresql 9.2。

@dezsoLEFT JOIN的答案應該很好。然而，索引幾乎沒有用處（本身），因為無論如何查詢都必須讀取整個表 - 例外是 Postgres 9.2+ 中的僅索引掃描和有利條件，見下文。

SELECT m.hash, m.string, count(m.method) AS method_ct
FROM   methods m
LEFT   JOIN nostring n USING (hash)
WHERE  n.hash IS NULL
GROUP  BY m.hash, m.string 
ORDER  BY count(m.method) DESC;

執行EXPLAIN ANALYZE查詢。多次排除兌現效應和噪音。比較最好的結果。

創建與您的查詢匹配的多列索引：

CREATE INDEX methods_cluster_idx ON methods (hash, string, method);

等待？在我說索引沒有幫助之後？好吧，我們需要它到CLUSTER桌子上：

CLUSTER methods USING methods_cluster_idx;
ANALYZE methods;

重新執行EXPLAIN ANALYZE。有更快的嗎？它應該是。

CLUSTER是按使用索引的順序重寫整個表的一次性操作。它實際上也是一個VACUUM FULL. 如果你想確定，你會單獨執行一個預測試，VACUUM FULL看看可以歸因於什麼。

如果您的表看到大量寫入操作，則效果會隨著時間的推移而降低。安排CLUSTER在下班時間恢復效果。微調取決於您的確切案例。關於CLUSTER.

CLUSTER是一個比較粗糙的工具，需要對錶進行排他鎖。如果您負擔不起，請考慮pg_repack哪個可以在沒有排他鎖的情況下做同樣的事情。更多在稍後的答案中：

• 為讀取性能配置 PostgreSQL

---

如果NULL列中值的百分比method很高（超過 ~ 20%，取決於實際的行大小），部分索引應該有幫助：

CREATE INDEX methods_foo_idx ON methods (hash, string)
WHERE method IS NOT NULL;

（您以後的更新顯示您的列是NOT NULL，因此不適用。）

如果您正在執行 PostgreSQL 9.2或更高版本（正如@deszo 評論CLUSTER的那樣），如果規劃器可以使用僅索引掃描，則提供的索引可能很有用。僅在有利條件下適用：沒有會影響可見性映射的寫入操作，因為VACUUM查詢中的最後一列和所有列都必須被索引覆蓋。基本上只讀表可以隨時使用它，而大量寫入的表是有限的。Postgres Wiki 中的更多詳細資訊。

在這種情況下，上面提到的部分索引可能會更有用。

另一方面，如果在 column 中沒有值*，* 您應該 1.) 定義它並 2.) 使用而不是，這會稍微快一些，並且在沒有值的情況下執行相同的操作。NULL``method
NOT NULL
count(*)``count(method)``NULL

如果您必須經常呼叫此查詢並且該表是只讀的，請創建一個MATERIALIZED VIEW.

---

異國情調的要點：您的表名為nostring，但似乎包含雜湊。通過排除雜湊而不是字元串，您可能會排除比預期更多的字元串。極不可能，但有可能。

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