Yavaş sorgudan doğru indekse

Eskiden anında açılan liste sayfası dört saniye sürmeye başlar. Gözle görülür bir değişiklik olmamıştır, tablo sadece büyümeye devam etmiştir ve elli bin satırla iki milyon satır arasında bir yerde planner sorguyu nasıl cevaplayacağı konusunda fikir değiştirmiştir. O noktadaki ilk dürtü, WHERE'deki kolona bir indeks açıp devam etmektir. Bu tahmin yeterince sık tutuyor ki tehlikeli olsun: tutmadığında elinizde hem yavaş bir sayfa hem de bakımını üstlendiğiniz fazladan bir indeks kalır.

Bir şeyi düzeltmeden önce sorguyu bulun

Kullanıcının şikâyet ettiği sorgu, zamanı yiyen sorgu olmayabilir. Veritabanının zaten tuttuğu veriden başlayın. Çoğu motor toplu statement istatistikleri sunar; gerçek sorunu ortaya çıkaran şey de ortalamaya değil toplam süreye göre sıralamaktır:

SELECT calls,
       round(total_exec_time)      AS total_ms,
       round(mean_exec_time, 1)    AS mean_ms,
       left(query, 70)             AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

 calls | total_ms | mean_ms | query
  8421 |   312840 |    37.2 | SELECT id, total FROM orders WHERE cust
    12 |    48120 |  4010.0 | SELECT count(*) FROM events WHERE creat

Düzeltilecek satır ilki. Tek başına bakıldığında 37 milisaniye gayet iyi görünür ve o sorgu sekiz bin kez çalışmaktadır; sayfanın süresi oraya gitmiştir. İkincisi çağrı başına daha yavaştır ve neredeyse hiç fark etmez.

Tablonun diğer yarısı uygulamada. Her sorgunun süresini ve dönen satır sayısını request id ile birlikte log'layın; o zaman yavaş bir endpoint size tek bir yavaş sorgu mu yoksa iki yüz hızlı sorgu mu çalıştırdığını anında söyler. İkinci durum bir indeks sorunu değildir ve hiçbir indeks onu düzeltmez.

Planı okuyun, tahmin etmeyin

Sorgu elinizdeyken veritabanına ne yapmayı planladığını ve gerçekte ne yaptığını sorun:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4711 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Limit  (cost=48210.44..48210.49 rows=20 width=28)
        (actual time=612.2..612.3 rows=20 loops=1)
  ->  Sort  (cost=48210.44..48287.19 rows=30700 width=28)
            (actual time=612.1..612.1 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Seq Scan on orders  (cost=0.00..47394.00 rows=30700 width=28)
                  (actual time=0.3..598.4 rows=28934 loops=1)
              Filter: ((customer_id = 4711) AND (status = 'paid'))
              Rows Removed by Filter: 1971066
Planning Time: 0.2 ms
Execution Time: 612.6 ms

Bu çıktıda teşhisin tamamını taşıyan üç şey var. Erişim yolu sequential scan, yani veritabanı tabloyu baştan sona okuyor. Rows Removed by Filter neredeyse iki milyon, yani çöpe atılan iş. Sıralama da taramadan sonra yapılıyor; eşleşen her satır önce üretiliyor, sonra yirmisi tutuluyor.

Oradayken tahmini satır sayısıyla gerçek satır sayısını da karşılaştırın. Tahmin bir büyüklük mertebesi sapıyorsa planner bozuk istatistikle çalışıyordur ve çözüm indeks değil ANALYZE olabilir.

Kolon sırasını seçin

Sorgu iki kolonda eşitlikle filtreliyor ve üçüncü bir kolona göre sıralıyor. Bu doğrudan indekse çevrilir:

CREATE INDEX CONCURRENTLY orders_customer_status_created_idx
  ON orders (customer_id, status, created_at DESC);

Sıranın arkasındaki kural şu: planner bir indeksin yalnızca baştan gelen parçasını kullanabilir. Eşitlik kolonları başa gelir, çünkü her biri taramayı bitişik bir bloğa daraltır. Sıralama veya aralık için kullanılan kolon sona gelir, çünkü o bloğun içine girildiğinde satırlar zaten doğru sıradadır ve sort adımı ortadan kalkar. Sırayı ters çevirip created_at kolonunu başa alırsanız indeks bir tarih aralığı taramasına döner ve bulduğu her satırı yine filtrelemek zorunda kalır.

Seçicilik, eşitlik kolonları arasındaki sırayı belirler; yukarıdaki kuralın yerine geçmez. customer_id çok sayıda farklı değer taşır ve tabloyu bir avuç satıra indirir, status belki beş değer taşır. Seçici olanı başa almak gezinmeyi kısa tutar.

Tek başına status üzerindeki bir indeksin genelde değersiz olmasının sebebi de bu. Tablonun dörtte biri paid ise, önce indeksi okuyup sonra satırların dörtte birini tek tek getirmek tabloyu taramaktan pahalıdır. Düşük kardinaliteli bir kolonun yerini hak ettiği tek biçim var: bayrağın anahtar değil koşul olduğu partial indeks.

CREATE INDEX CONCURRENTLY orders_pending_created_idx
  ON orders (created_at)
  WHERE status = 'pending';

İndeks artık yalnızca worker'ın aradığı satırları içerir. Küçüktür, bellekte kalır ve zaten bitmiş satırlarla birlikte büyümez. Büyük bir içe aktarmayı boşaltan arka plan kuyruğunda işe yarayan indeks tam olarak budur, çünkü orada ilgilenilen satırlar her zaman tablonun çok küçük bir kısmıdır.

Sorgu yalnızca birkaç kolon dönüyorsa bir adım daha atıp tabloya hiç dokunmadan cevap alabilirsiniz:

CREATE INDEX CONCURRENTLY orders_customer_status_created_idx
  ON orders (customer_id, status, created_at DESC)
  INCLUDE (total);

Buna covering index deniyor. Taşınan kolonlar yaprak sayfalarda birlikte durur, plan index only scan'e döner ve heap erişimleri kaybolur. Bedeli boyut: eklenen her kolon indeksi büyütür, büyüyen indeksi cache'te tutmak da zorlaşır.

Bedelin okuma tarafında olmadığını da söylemek gerekiyor. Bir indeks, kolonlarına dokunan her insert ve update işleminde güncellenir; yani okuma tarafında kazandığınız milisaniyeleri yazma tarafında azar azar geri ödersiniz. Çok yazılan bir tabloda üç indeks genelde sorun değildir, on üç indeks fark edilir. Bu yüzden yeni indeks eklemeyi, eskilerinden birini silmek için de bir fırsat sayın.

Çalıştığını nasıl doğrularsınız

Aynı EXPLAIN'i çalıştırın ve aynı üç satırı okuyun:

Limit  (cost=0.43..8.91 rows=20 width=28) (actual time=0.05..0.19 rows=20 loops=1)
  ->  Index Scan using orders_customer_status_created_idx on orders
        (actual time=0.04..0.17 rows=20 loops=1)
        Index Cond: ((customer_id = 4711) AND (status = 'paid'))
Planning Time: 0.3 ms
Execution Time: 0.2 ms

Sort düğümü yok, filtre yok, okunan satır sayısı dönen satır sayısına eşit. Altı yüz milisaniye birin altına indi. Sonra bunu bir hafta çalıştırın ve indekslerin gerçekte ne yaptığına bakın:

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC
LIMIT 10;

Bu listenin başında sıfır tarama ve ciddi bir boyutla duran indeksler, size hiçbir karşılığı olmadan yazma kapasitesine ve diske mal oluyor. Onları silmek işin diğer yarısıdır ve hiç yapılmayan yarısı da budur. Disk sessizce dolar; kimsenin döndürmediği log'larda olduğu gibi.

Nelere dikkat etmeli

• Kolonu sarmalamak indeksi öldürür. WHERE lower(email) = $1 veya WHERE created_at::date = $1 düz kolon üzerindeki indeksi kullanamaz. Ya ifadenin kendisini indeksleyin ya da sorguyu aralık biçiminde yazın.
• LIKE kalıbının başındaki joker de normal indeksi kullanamaz. %kelime% gerektiren metin araması kolon sırası ayarı değil, farklı bir indeks tipi ister.
• Planı gerçekçi bir parametreyle ve bir de uç bir parametreyle test edin. Üç siparişi olan müşteriyle üç yüz bin siparişi olan müşteri aynı sorgudan farklı planlar alabilir; gece birini uyandıran da ikincisidir.
• Motor destekliyorsa indeksi yazmaları kilitlemeden oluşturun, ama bunun daha uzun süreceğini ve bir çakışmada başarısız olabileceğini hesaba katın. Yarım kalan bir concurrent build arkasında elle silmeniz gereken geçersiz bir indeks bırakır.
• Daha çok indeks, daha yavaş yazma ve tutarlı tutulacak daha çok şey demek. Toplu insert alan bir tabloda indeksi silip veriyi yükleyip yeniden oluşturmak daha ucuz olabilir; yeter ki yükleme işi, oluşturma yarıda kalırsa iki kez çalıştırılabilir olsun.

Yol her seferinde aynı: sorguyu sezgiyle değil sayıyla bulun, veritabanının verdiği planı okuyun ve indeksi sorgunun biçimi seçsin, tersi değil. Değerin büyük kısmı ilk iki adımdan gelir, çünkü bir plan size on saniyede söylediği şeyi bir öğlen süren tahmin turu söylemez. Plan veritabanının zaten yapabileceği en az işi yaptığını söylüyorsa sorun başka yerdedir; bunu bilmek de en az diğeri kadar işe yarar.