PostgreSQL Sorgu Optimizasyonu: EXPLAIN'den Index'e

EXPLAIN ANALYZE
SELECT m.baslik, k.ad, COUNT(y.id) AS yorum_sayisi
FROM makaleler m
JOIN kullanicilar k ON k.id = m.yazar_id
LEFT JOIN yorumlar y ON y.makale_id = m.id
WHERE m.yayinlandi = true
  AND m.olusturuldu > NOW() - INTERVAL '30 days'
GROUP BY m.id, m.baslik, k.id, k.ad
ORDER BY yorum_sayisi DESC
LIMIT 20;

Limit  (cost=2341.52..2341.57 rows=20) (actual time=89.432..89.445 rows=20)
  -> Sort  (cost=2341.52..2348.27 rows=2700) (actual time=89.428..89.431)
       -> HashAggregate  (cost=2167.32..2194.82 rows=2700)
            -> Hash Left Join  (cost=892.14..2099.82)
                 Hash Cond: (y.makale_id = m.id)
                 -> Hash Join  (cost=45.20..1152.60)     ← SEQ SCAN var mı?
                      -> Seq Scan on makaleler m          ← KÖTÜ işaret
                           Filter: (yayinlandi AND olusturuldu > ...)
                           Rows Removed by Filter: 12840
 
Planning Time:  2.1 ms
Execution Time: 89.4 ms   ← Hedef: < 10ms

-- 1. Tek sütun index — WHERE koşullarına
CREATE INDEX CONCURRENTLY idx_makaleler_yayinlandi
ON makaleler(yayinlandi);
 
CREATE INDEX CONCURRENTLY idx_makaleler_olusturuldu
ON makaleler(olusturuldu DESC);
 
-- 2. Composite index — birden fazla WHERE koşulu birlikte kullanılıyorsa
-- Sıra önemli: eşitlik koşulları önce, range koşulları sona
CREATE INDEX CONCURRENTLY idx_makaleler_yayinlandi_tarih
ON makaleler(yayinlandi, olusturuldu DESC);
 
-- 3. Partial index — WHERE koşulunun daraltılmış versiyonu
-- Sadece yayınlananları indeksle, diğerleri büyüklüğü artırır
CREATE INDEX CONCURRENTLY idx_makaleler_aktif_tarih
ON makaleler(olusturuldu DESC)
WHERE yayinlandi = true;
 
-- 4. Covering index — JOIN'ı ortadan kaldırır
-- SELECT'te kullanılan tüm alanlar index içinde
CREATE INDEX CONCURRENTLY idx_makaleler_listele
ON makaleler(olusturuldu DESC, yayinlandi)
INCLUDE (baslik, slug, yazar_id);

-- KÖTÜ — her makale için ayrı yazar sorgusu (ORM'lerin tuzağı)
-- Kod: makaleler.forEach(m => m.yazar)
-- DB'ye giden: SELECT * FROM kullanicilar WHERE id = '1'
--              SELECT * FROM kullanicilar WHERE id = '2'
--              ... (100 makale = 101 sorgu!)
 
-- İYİ — tek JOIN ile hepsini çek
SELECT 
    m.id, m.baslik, m.slug,
    k.id   AS yazar_id,
    k.ad   AS yazar_adi,
    k.avatar AS yazar_avatar
FROM makaleler m
INNER JOIN kullanicilar k ON k.id = m.yazar_id
WHERE m.yayinlandi = true
ORDER BY m.olusturuldu DESC
LIMIT 20;
 
-- Prisma'da: include ile N+1'i önle
const makaleler = await prisma.makale.findMany({
  include: {
    yazar: { select: { ad: true, avatar: true } }, // JOIN kullanır
    etiketler: true,
  },
  take: 20,
  orderBy: { olusturuldu: 'desc' },
  where: { yayinlandi: true },
});

-- KÖTÜ — büyük offset'te performans katastrofik düşer
-- OFFSET 10000 = 10000 satırı tarayıp at
SELECT * FROM makaleler
WHERE yayinlandi = true
ORDER BY olusturuldu DESC
LIMIT 20 OFFSET 10000;  -- Yavaş!
 
-- İYİ — Cursor-based pagination (keyset pagination)
-- İlk sayfa
SELECT id, baslik, slug, olusturuldu
FROM makaleler
WHERE yayinlandi = true
ORDER BY olusturuldu DESC, id DESC
LIMIT 20;
 
-- Sonraki sayfa — son elemanın cursor'ını kullan
SELECT id, baslik, slug, olusturuldu
FROM makaleler
WHERE yayinlandi = true
  AND (olusturuldu, id) < ('2025-06-01 12:00:00', 'abc-last-id')
ORDER BY olusturuldu DESC, id DESC
LIMIT 20;

-- GIN index ile Türkçe full-text search
ALTER TABLE makaleler ADD COLUMN arama_vektoru tsvector;
 
-- Otomatik güncelleme için trigger
CREATE FUNCTION makaleler_arama_guncelle() RETURNS trigger AS $$
BEGIN
  NEW.arama_vektoru :=
    setweight(to_tsvector('simple', coalesce(NEW.baslik, '')), 'A') ||
    setweight(to_tsvector('simple', coalesce(NEW.excerpt, '')), 'B') ||
    setweight(to_tsvector('simple', coalesce(NEW.icerik, '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER makaleler_arama_trigger
BEFORE INSERT OR UPDATE ON makaleler
FOR EACH ROW EXECUTE FUNCTION makaleler_arama_guncelle();
 
-- GIN index
CREATE INDEX idx_makaleler_fts ON makaleler USING GIN(arama_vektoru);
 
-- Arama sorgusu
SELECT 
    baslik, 
    excerpt,
    ts_rank(arama_vektoru, sorgu) AS skor
FROM makaleler,
     to_tsquery('simple', 'javascript & react') sorgu
WHERE arama_vektoru @@ sorgu
  AND yayinlandi = true
ORDER BY skor DESC
LIMIT 10;

-- Yavaş: her sorguda COUNT hesapla
SELECT COUNT(*) FROM yorumlar WHERE makale_id = $1;
 
-- Hızlı: denormalize yorum sayısını tablo üzerinde tut
ALTER TABLE makaleler ADD COLUMN yorum_sayisi INTEGER NOT NULL DEFAULT 0;
 
-- Trigger ile senkronize et
CREATE FUNCTION yorum_sayisi_guncelle() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE makaleler SET yorum_sayisi = yorum_sayisi + 1 WHERE id = NEW.makale_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE makaleler SET yorum_sayisi = yorum_sayisi - 1 WHERE id = OLD.makale_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER yorum_sayaci
AFTER INSERT OR DELETE ON yorumlar
FOR EACH ROW EXECUTE FUNCTION yorum_sayisi_guncelle();
 
-- Artık JOIN'sız sorgu
SELECT baslik, yorum_sayisi FROM makaleler 
WHERE yayinlandi = true
ORDER BY yorum_sayisi DESC
LIMIT 10;
-- Execution time: 2ms (önceki: 450ms)

// Prisma ile bağlantı havuzu — veritabanını aşırı yükleme
// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  // PgBouncer veya Prisma Accelerate için
}
 
// .env
DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=20&pool_timeout=10"
 
// PgBouncer bağlantı URL'si
DATABASE_URL="postgresql://user:pass@pgbouncer:6432/db?pgbouncer=true"

-- postgresql.conf — yavaş sorguları logla
log_min_duration_statement = 100  -- 100ms üstü sorguları logla
log_line_prefix = '%t [%p]: [%l-1] '
log_checkpoints = on
log_connections = on
 
-- pg_stat_statements ile analiz
SELECT 
    LEFT(query, 80) AS sorgu,
    calls AS cagri_sayisi,
    ROUND(total_exec_time::numeric, 2) AS toplam_ms,
    ROUND(mean_exec_time::numeric, 2)  AS ortalama_ms,
    rows AS etkilenen_satir
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;