Postgresql 9.3 GROUP BY 在 30m 行表中的索引列上花費很長時間

我有一個簡單的表格，其中包含 3000 萬行的各種車輛的 GPS 數據。

        Column         |            Type             | Modifiers | Storage  | Stats target | Description
------------------------+-----------------------------+-----------+----------+--------------+-------------
id                     | bigint                      | not null  | plain    |              |
equipmentid            | bigint                      |           | plain    |              |
Indexes:
   "some_pkey" PRIMARY KEY, btree (id)
   "some_idx" btree (equipmentid)

該表有更多列，但我認為它們與我的問題無關（但如果你認為它們是讓我知道，我會添加它們）。

我正在嘗試獲取每個設備的最後一個 GPS 位置，如下所示：

user=> explain (analyze, verbose, buffers) select max(gps2.id) from GpsData gps2 group by gps2.equipmentId;
                                                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate  (cost=0.56..131266837.64 rows=298 width=16) (actual time=64679.945..637667.147 rows=307 loops=1)
  Output: max(id), equipmentid
  Buffers: shared hit=5662655 read=11225117
  ->  Index Scan using some_idx on public.gpsdata gps2  (cost=0.56..131099834.76 rows=33399980 width=16) (actual time=35.452..634355.329 rows=29176300 loops=1)
        Output: equipmentid, id
        Buffers: shared hit=5662655 read=11225117
Total runtime: 637677.321 ms
(7 rows)

如您所見，此查詢執行時間超過 5 分鐘，這非常多。在我看來，這也是 a 最簡單的案例之一GROUP BY（即我希望它更快）。

我正在嘗試查看分析，但是… 我不知道從哪裡開始。我的意思是它確實說rows=29176300，這看起來像很多行，但仍然 5 分鐘似乎過多。

我還應該注意，我已經通過 SET enable_seqscan = off希望它更快來強制進行索引掃描，但我錯了（我之前Seq Scan的輸出中沒有索引掃描；它過去需要大約 4 分鐘，略短）

關於從哪裡開始的任何提示？我的 postgres 版本是PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit.

**編輯：**根據要求提供更多資訊。

user=> select pg_total_relation_size('some_idx');
pg_total_relation_size
------------------------
            1620033536
(1 row)

查看讀取索引所需的緩衝區數量，您可能正遭受表和索引膨脹的困擾。

如果您有能力鎖定表，請執行vacuum full analyze gpsdata以清理舊數據。

auto-vacuum沒有正確清理和重新使用已刪除空間的原因有幾個：

1. 您有許多會話訪問處於“事務中空閒”狀態的該表。無法清理事務使用的行。確保您正確送出所有事務 - 包括SELECT也啟動事務的語句。
   最簡單的方法是啟用自動送出（預設在psql）
2. 您對該表的更新頻率很高，因此該表不斷更改並且自動真空無法跟上（或沒有機會清理 - 請參閱 1。）
3. 您已禁用自動真空

---

上的索引gpsdata (equipmentid,id)可能會加快計算速度。

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