Önce ölç, sonra düzelt: yavaş sorguda çalışma akışı

Senaryo: "Rapor yavaş, indeks ekleyelim"

Ilgaz Bank'ta operasyon ekibi aylık hareket raporunun yavaşladığını söylüyor. Biri hemen "indeks ekleyelim" diyor. Belki doğrudur, ama henüz kimse hangi sorgunun neden yavaş olduğunu görmedi. Optimizasyon işi tahminle başlarsa yanlış yere indeks eklersin, yazmayı yavaşlatırsın, rapor yine yavaş kalır.

Altı adımlık akış

1. Sorunu tanımla. Hangi sorgu? Ne sıklıkla çalışıyor? Kabul edilebilir süre ne? Günde bir çalışan rapor ile saniyede yüz kez çalışan ekran aynı öncelikte değildir.
2. Temel ölçümü al (baseline). Süreyi birkaç kez ölç. İş ölçüsünü de yaz: okunan sayfa ya da satır. Sonucun sağlamasını sakla: satır sayısı ve toplam.
3. Planı oku. Tabloyu baştan sona mı okuyor, indeks mi kullanıyor, nerede satır çok?
4. Tek değişiklik yap. Aynı anda üç şey değiştirirsen hangisinin işe yaradığını bilemezsin.
5. Yeniden ölç ve sonucu karşılaştır. Hızlı ama yanlış sorgu, yavaş ve doğru sorgudan kötüdür.
6. Bedeli yaz. İndeks yazmayı yavaşlatır ve yer kaplar. Kararı ve ölçümü bir yere not et.

Bu raporla deneyelim

SELECT txn_type, COUNT(*) AS adet, ROUND(SUM(amount)) AS toplam
FROM bank_txns
WHERE strftime('%Y-%m', txn_time) = '2024-11'
GROUP BY txn_type;

Planı görelim. Bu görevlerde tarayıcıdaki motor SQLite olduğu için EXPLAIN QUERY PLAN kullanıyoruz:

EXPLAIN QUERY PLAN
SELECT txn_type, COUNT(*) AS adet, ROUND(SUM(amount)) AS toplam
FROM bank_txns
WHERE strftime('%Y-%m', txn_time) = '2024-11'
GROUP BY txn_type;

Satırları oku: SCAN bank_txns tablonun tamamını okuduğunu, USE TEMP B-TREE FOR GROUP BY gruplama için geçici yapı kurduğunu söylüyor. Üstelik bir ay isteniyor. Peki okunan satırların ne kadarı gerçekten kullanılıyor? Bunu da sorguyla ölçebilirsin:

SELECT COUNT(*) AS okunan,
       SUM(CASE WHEN txn_time >= '2024-11-01' AND txn_time < '2024-12-01' THEN 1 ELSE 0 END) AS kullanilan
FROM bank_txns;

Tek değişiklik: sütunu serbest bırak ve indeks ekle

CREATE INDEX ix_txn_time ON bank_txns (txn_time);
EXPLAIN QUERY PLAN
SELECT txn_type, COUNT(*) AS adet, ROUND(SUM(amount)) AS toplam
FROM bank_txns
WHERE txn_time >= '2024-11-01' AND txn_time < '2024-12-01'
GROUP BY txn_type;

Plan SEARCH bank_txns USING INDEX ix_txn_time (txn_time>? AND txn_time<?) oldu: motor yalnız o ayın satırlarına gidiyor.

Sonuç aynı mı?

Sağlama yapmadan bitmedi. İki sorgunun farkı boş kalmalı. Her iki yönü de dene:

SELECT txn_type, COUNT(*) AS adet FROM bank_txns WHERE strftime('%Y-%m', txn_time) = '2024-11' GROUP BY txn_type
EXCEPT
SELECT txn_type, COUNT(*) AS adet FROM bank_txns WHERE txn_time >= '2024-11-01' AND txn_time < '2024-12-01' GROUP BY txn_type;

Gerçek bir PostgreSQL'de aynı değişiklik

PostgreSQL planını bir sonraki derste ayrıntılı okuyacağız. Şimdilik üç satır yeter. Seq Scan tablonun tamamını okur, Index Only Scan yalnız indeksi okur. Rows Removed by Filter okunup atılan satırdır. Buffers: shared hit okunan sayfa sayısıdır.

İndeks yokken:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT COUNT(*) FROM bank_txns WHERE txn_time >= '2024-11-01' AND txn_time < '2024-12-01';

Gerçek çıktı

Aggregate  (cost=1034.37..1034.38 rows=1 width=8) (actual rows=1 loops=1)
  Buffers: shared hit=391
  ->  Seq Scan on bank_txns  (cost=0.00..1025.26 rows=3644 width=0) (actual rows=3590 loops=1)
        Filter: ((txn_time >= '2024-11-01 00:00:00'::timestamp without time zone) AND (txn_time < '2024-12-01 00:00:00'::timestamp without time zone))
        Rows Removed by Filter: 38694
        Buffers: shared hit=391

İndeks varken:

CREATE INDEX ix_txn_time ON bank_txns (txn_time);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT COUNT(*) FROM bank_txns WHERE txn_time >= '2024-11-01' AND txn_time < '2024-12-01';

Gerçek çıktı

Aggregate  (cost=121.16..121.17 rows=1 width=8) (actual rows=1 loops=1)
  Buffers: shared hit=13
  ->  Index Only Scan using ix_txn_time on bank_txns  (cost=0.29..112.17 rows=3594 width=0) (actual rows=3590 loops=1)
        Index Cond: ((txn_time >= '2024-11-01 00:00:00'::timestamp without time zone) AND (txn_time < '2024-12-01 00:00:00'::timestamp without time zone))
        Heap Fetches: 0
        Buffers: shared hit=13

Süre yerine iş ölçüsüne bak: okunan sayfa sayısı 391'den 13'e indi. Filtrenin elediği satırlar da (Rows Removed by Filter) kayboldu. İki çıktının gerçek satır sayısı aynı: 3.590.

Her SQL türünde ne ölçülür?