Postgresql 13 RDS 託管索引表上的慢速簡單選擇查詢

我正在使用一個簡單的表，它託管在執行 Postgresql 13 的 RDS 實例上。它使用具有通用 SSD 儲存的 t3.small 實例。

該表及其索引如下所示

measurements=> \d+ measurement_export_2 
                                        Table "public.measurement_export_2"
     Column       |           Type           | Collation | Nullable | Default | Storage | Stats target | Description 
-------------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
metering_point_id | integer                  |           |          |         | plain   |              | 
datetime_to       | timestamp with time zone |           |          |         | plain   |              | 
energy            | real                     |           |          |         | plain   |              | 
is_consumption    | boolean                  |           |          |         | plain   |              | 
Indexes:
   "index_on_measurement_export_2_metering_point_id_consumption" btree (metering_point_id, is_consumption)
Access method: heap

並具有以下大小（表中約 50 M 行）：

measurements=> \d+
                               List of relations
Schema |          Name          |   Type   |  Owner   |    Size    | Description 
--------+------------------------+----------+----------+------------+-------------
public | measurement_export_2   | table    | postgres | 2528 MB    | 

            table             |                              index                              | index_size | index_scans 
-------------------------------+-----------------------------------------------------------------+------------+-------------
public.measurement_export_2   | index_on_measurement_export_2_metering_point_id_consumption     | 336 MB     |           7

一個簡單查詢的解釋如下所示：

EXPLAIN (ANALYZE, BUFFERS) SELECT datetime_to, energy FROM measurement_export_2 WHERE metering_point_id=1267 AND is_consumption;
                                                                                            QUERY PLAN                                                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_on_measurement_export_2_metering_point_id_consumption on measurement_export_2  (cost=0.56..325673.37 rows=92433 width=12) (actual time=2.328..29296.200 rows=121344 loops=1)
  Index Cond: ((metering_point_id = 1267) AND (is_consumption = true))
  Buffers: shared read=74057
  I/O Timings: read=28946.758
Planning Time: 0.066 ms
Execution Time: 29317.316 ms
(6 rows)

我嘗試過並檢查過的事情：

1. 在創建索引後執行VACUUM (ANALYZE) measurement_export_2並在上述查詢之前測試幾個查詢。
2. 更改索引 - 因為“metering_point_id,datetime_to,is_consumption”是唯一的，所以我嘗試使用唯一索引，它在某些情況下提供了廣泛的改進，但通常只有百分之幾。
3. 我檢查了 RDS 指標：CPU 使用率低，餘額正常（突發，IO），有足夠的可用記憶體 - 增加實例大小沒有意義。除非引入額外的記憶體可以將大部分/所有數據放入 RAM（以某種方式）。但我不認為這是這裡的主要問題。
4. 我確實將“work_mem”設置增加到“16MB”，這有助於解決另一個問題，但不是在這裡。

我還能做些什麼來提高查詢性能？

我不是 DBA，只是一名開發人員，努力讓事情盡我所能。謝謝你的幫助。

如果該表是 VACUUMed 並且您在所有 4 列上都有一個索引，其中 metering_point_id 和 is_consumption 領先，那麼您可以獲得一個應該很快的僅索引掃描，因為它根本不需要在表中跳轉.

或者，如果您在索引上對錶進行了分群，以便表行與索引條目的順序相同，那麼您將能夠讀取表的一小部分並且大部分是按順序讀取的，這應該比全部跳轉快得多在桌子周圍。但是 CLUSTER 很慢，需要一個強鎖，一旦完成，它就不會隨著未來的數據翻轉而保持完成。

或者，如果該表適合 RAM 並保持在那裡（這取決於有多少其他大表以及這些表被使用的頻率），那麼目前計劃將很快（一旦表找到進入 RAM 的方式，pg_prewarm可以主動提供幫助），因為它不需要從磁碟讀取數據。

您目前正在閱讀大約每毫秒 2.5 個塊。這絕不是慢，但如果你願意為此付出代價可能會更快。

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