Postgres'te yavaş bir sorguyu 40 kat hızlandırdığım gün

Üretimdeki Darboğaz

Mutabakat servisinin yönetim panelinde son 50 başarısız ödeme denemesini listeleyen uç nokta, tablo 18 milyon satırı geçtiğinde p99 gecikmesinde 480 ms sınırına dayandı. İlk bakışta tabloda account_id üzerinde bir B-Tree indeksi vardı ve geliştirme ortamındaki küçük veri setinde sorgu milisaniyeler içinde dönüyordu.

Üretim kopyasında çalışan asıl sorgu şuydu:

SELECT id, amount_cents, error_code, created_at
FROM ledger_attempts
WHERE account_id = $1
  AND status = 'FAILED'
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN ANALYZE Çıktısını Okumak

Sorunun ağ gecikmesinden mi yoksa disk okumasından mı kaynaklandığını görmek için sorguyu BUFFERS seçeneğiyle koşturdum:

Limit  (cost=41820.44..41820.57 rows=50 width=44) (actual time=479.812..479.826 rows=50 loops=1)
  Buffers: shared hit=3412 read=14890
  ->  Sort  (cost=41820.44..41908.19 rows=35100 width=44) (actual time=479.809..479.818 rows=50 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 31kB
        Buffers: shared hit=3412 read=14890
        ->  Index Scan using idx_attempts_account on ledger_attempts
              (cost=0.43..40654.12 rows=35100 width=44) (actual time=0.084..456.210 rows=34812 loops=1)
              Index Cond: (account_id = 'acc_91f02'::text)
              Filter: ((status)::text = 'FAILED'::text)
              Rows Removed by Filter: 192440
              Buffers: shared hit=3412 read=14890
Planning Time: 0.290 ms
Execution Time: 479.895 ms

Plan iki temel hatayı açıkça gösteriyordu. Birincisi, yüksek hacimli hesaplarda idx_attempts_account indeksi ilgili hesaba ait yaklaşık 227 bin satırın tamamını tarıyor, her biri için tablo yığınına (heap) gidip status = 'FAILED' filtresini uyguluyor ve 192 bin satırı çöpe atıyordu. İkincisi, indeks created_at sırasını bilmediği için PostgreSQL eşleşen 34 bin satırın tamamını okuyup bellekte top-N heapsort yapmak zorunda kalıyordu. Toplamda tek bir API çağrısı 18 binden fazla 8 KB’lık sayfa (~145 MB) okuyordu.

İndeks Tasarımı ve Ödünleşim

İlk refleks (account_id, status, created_at DESC) şeklinde tam bir bileşik indeks açmaktı. Ancak tablodaki kayıtların %91’i SUCCEEDED durumundaydı ve uygulama hiçbir zaman başarılı kayıtları bu sorgu kalıbıyla filtrelemiyordu. Tüm durumları indekslemek hem yazma maliyetini (write amplification) artıracak hem de paylaşımlı önbellekte (shared_buffers) gereksiz yer kaplayacaktı.

Bunun yerine yalnızca başarısız kayıtları kapsayan, seçilen kolonları da INCLUDE ile yaprak düğümlere ekleyen bir kısmi örtücü indeks (partial covering index) tanımladım:

CREATE INDEX CONCURRENTLY idx_attempts_failed_recent
ON ledger_attempts (account_id, created_at DESC)
INCLUDE (id, amount_cents, error_code)
WHERE status = 'FAILED';

Eşitlik koşulu (account_id) önde, sıralama kolonu (created_at DESC) hemen arkasında yer aldığı için sıralama adımı tamamen ortadan kalktı. İndeks tanımındaki WHERE status = 'FAILED' koşulu indeks boyutunu 2.4 GB yerine 190 MB seviyesinde tuttu.

Sonuç ve Operasyonel Maliyet

Yeni plan çıktısı:

Limit  (cost=0.42..18.95 rows=50 width=44) (actual time=0.062..11.140 rows=50 loops=1)
  Buffers: shared hit=48 read=6
  ->  Index Only Scan using idx_attempts_failed_recent on ledger_attempts
        (cost=0.42..12904.10 rows=34812 width=44) (actual time=0.060..11.124 rows=50 loops=1)
        Index Cond: (account_id = 'acc_91f02'::text)
        Heap Fetches: 4
        Buffers: shared hit=48 read=6
Planning Time: 0.215 ms
Execution Time: 11.178 ms

Okunan blok sayısı 18.302’den 54’e, çalışma süresi ise 480 ms’den 11.1 ms’ye indi. AUTOVACUUM görünürlük haritasını (visibility map) güncel tuttukça sorgu yığına neredeyse hiç dokunmadan Index Only Scan ile tamamlanıyor. Karşılığında INSERT ve status güncelleme yolunda ek bir indeks bakım maliyeti kabul ettik; ancak kısmi filtre sayesinde bu ek yük ölçülebilir gürültü sınırının altında kaldı.