PostgreSQL 從 9.3 升級到 9.4 後速度極慢

我們最近將一個 Amazon RDS 實例從 PostgreSQL 9.3 升級到了 9.4。升級過程中沒有任何問題，之後我們執行VACUUM ANALYZE. 性能現在非常緩慢。下面是一個在 9.3 中花費幾分之一秒的查詢範例：

EXPLAIN ANALYZE SELECT room_name, id FROM common_activityinstance WHERE started_by_id = 1370408 AND room_name IN ('robcv28', 'foobartest', 'noroster', 'jscode', 'cv28', 'johansencouple', 'lv426', 'johansenfamily', 'johansen') AND end_time IS NULL;
                                                                             QUERY PLAN                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on common_activityinstance  (cost=55.63..59.65 rows=1 width=13) (actual time=1.082..1.082 rows=0 loops=1)
  Recheck Cond: ((started_by_id = 1370408) AND (room_name = ANY ('{robcv28,foobartest,noroster,jscode,cv28,johansencouple,lv426,johansenfamily,johansen}'::text[])))
  Filter: (end_time IS NULL)
  Rows Removed by Filter: 925
  ->  BitmapAnd  (cost=55.63..55.63 rows=1 width=0) (actual time=0.349..0.349 rows=0 loops=1)
        ->  Bitmap Index Scan on common_activityinstance_started_by_id  (cost=0.00..5.53 rows=146 width=0) (actual time=0.122..0.122 rows=927 loops=1)
              Index Cond: (started_by_id = 1370408)
        ->  Bitmap Index Scan on common_activityinstance_room_name_c1f9997a_like  (cost=0.00..49.85 rows=1171 width=0) (actual time=0.171..0.171 rows=926 loops=1)
              Index Cond: (room_name = ANY ('{robcv28,foobartest,noroster,jscode,cv28,johansencouple,lv426,johansenfamily,johansen}'::text[]))
Total runtime: 1.116 ms
(10 rows)

在 9.4 中，同樣的查詢現在要慢得多：

EXPLAIN ANALYZE SELECT room_name, id FROM common_activityinstance WHERE started_by_id = 1370408 AND room_name IN ('robcv28', 'foobartest', 'noroster', 'jscode', 'cv28', 'johansencouple', 'lv426', 'johansenfamily', 'johansen') AND end_time IS NULL;
                                                                         QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on common_activityinstance  (cost=10157.60..14038.97 rows=45 width=36) (actual time=3923.011..3923.011 rows=0 loops=1)
  Recheck Cond: ((started_by_id = 1370408) AND (end_time IS NULL))
  Rows Removed by Index Recheck: 698
  Filter: (room_name = ANY ('{robcv28,foobartest,noroster,jscode,cv28,johansencouple,lv426,johansenfamily,johansen}'::text[]))
  Heap Blocks: exact=634
  ->  BitmapAnd  (cost=10157.60..10157.60 rows=991 width=0) (actual time=3917.278..3917.278 rows=0 loops=1)
        ->  Bitmap Index Scan on common_activityinstance_started_by_id  (cost=0.00..3282.60 rows=198155 width=0) (actual time=0.245..0.245 rows=927 loops=1)
              Index Cond: (started_by_id = 1370408)
        ->  Bitmap Index Scan on end_time_id  (cost=0.00..6874.73 rows=198155 width=0) (actual time=3915.565..3915.565 rows=1674860 loops=1)
              Index Cond: (end_time IS NULL)
Planning time: 2.457 ms
Execution time: 3923.065 ms
(12 rows)

我不明白為什麼查詢計劃不同。這只是一個例子——在我放棄並停止它之前，另一個查詢執行了 15 分鐘。

我認為索引可能有問題，所以我也這樣做了，REINDEX DATABASE但沒有幫助。

有任何想法嗎？

由於整個數據庫似乎都受到了影響，我決定嘗試一個VACUUM (FULL, ANALYZE). 完成需要 50 小時 4 分鐘，但這是完全值得的。我們的數據庫磁碟使用量從 1259 GB 下降到 747 GB（回收了高達 512 GB 的空間），整體性能恢復到升級前的水平。

你有兩個完全不同的點陣圖掃描在那裡發生..快速..

  ->  BitmapAnd  (cost=55.63..55.63 rows=1 width=0) (actual time=0.349..0.349 rows=0 loops=1)
        ->  Bitmap Index Scan on common_activityinstance_started_by_id  (cost=0.00..5.53 rows=146 width=0) (actual time=0.122..0.122 rows=927 loops=1)
              Index Cond: (started_by_id = 1370408)
        ->  Bitmap Index Scan on common_activityinstance_room_name_c1f9997a_like  (cost=0.00..49.85 rows=1171 width=0) (actual time=0.171..0.171 rows=926 loops=1)
              Index Cond: (room_name = ANY ('{robcv28,foobartest,noroster,jscode,cv28,johansencouple,lv426,johansenfamily,johansen}'::text[]))
Total runtime: 1.116 ms

慢的..

  ->  BitmapAnd  (cost=10157.60..10157.60 rows=991 width=0) (actual time=3917.278..3917.278 rows=0 loops=1)
        ->  Bitmap Index Scan on common_activityinstance_started_by_id  (cost=0.00..3282.60 rows=198155 width=0) (actual time=0.245..0.245 rows=927 loops=1)
              Index Cond: (started_by_id = 1370408)
        ->  Bitmap Index Scan on end_time_id  (cost=0.00..6874.73 rows=198155 width=0) (actual time=3915.565..3915.565 rows=1674860 loops=1)
              Index Cond: (end_time IS NULL)

所以發生了什麼情況，IS NULL預計有 200k 行，它得到了 150 萬行，所以它使用掃描end_time_id而不是common_activityinstance_room_name_c1f9997a_like

讓我們看一下查詢，

EXPLAIN ANALYZE SELECT room_name, id
FROM common_activityinstance
WHERE started_by_id = 1370408
 AND room_name IN ('robcv28', 'foobartest', 'noroster', 'jscode', 'cv28', 'johansencouple', 'lv426', 'johansenfamily', 'johansen')
 AND end_time IS NULL;

我們不知道這裡的案例是什麼，但您多久搜尋一次 where end_time IS NOT NULLon room_name. 我會對 index進行核對end_time_in，common_activityinstance_started_by_id而是將其設為部分索引（假設您實際上並不關心已end_time設置的行並且它們是歸檔的。

CREATE INDEX ON common_activityinstance (started_by_id, room_name)
 WHERE end_time IS NULL;
ANALYZE common_activityinstance;

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