PostgreSQL'de indeks seçimi: B-tree, GIN ve BRIN

Yavaş bir sorguyla karşılaşınca ilk refleks genelde ilgili kolona indeks eklemek olur. Çoğu durumda bu doğru hamle, ama indeksin türü en az varlığı kadar önemli. Yanlış tür hem yazma performansını düşürür hem de diskte gereksiz yer kaplar.

B-tree: varsayılan ve çoğu zaman yeterli

CREATE INDEX yazdığınızda tür belirtmezseniz PostgreSQL B-tree oluşturur. Eşitlik ve aralık sorgularında (=, <, >, BETWEEN) ve ORDER BY işlemlerinde iyi çalışır.

CREATE INDEX idx_orders_created_at ON orders (created_at);

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE created_at >= now() - interval '7 days';

Plan çıktısında Index Scan using idx_orders_created_at görüyorsanız indeks kullanılıyor demektir. Seq Scan (sequential scan) görüyorsanız ya seçicilik düşüktür ya da istatistikler güncel değildir. İkinci durumda ANALYZE orders; çalıştırmak genelde yeterli.

B-tree’nin neden bu kadar hızlı olduğunu görmek için aramayı adım adım izleyebilirsiniz. Kök sayfadan başlayan arama, her seviyede tek bir sayfayı okuyarak yaprağa iner. Aralık sorgusunda ise yaprak sayfalar arasındaki kardeş bağlantıları izlenir.

B-tree indeksinde arama. Gerçek bir indeks sayfasında yüzlerce anahtar olur; şekilde okunaklı kalsın diye her sayfada en fazla 2 anahtar var.

GIN: dizi, JSONB ve tam metin arama

B-tree, bir kolonun içindeki elemanları aramak için uygun değildir. tags gibi bir dizi kolonunda “şu etiketi içeren satırlar” sorgusu için GIN kullanılır:

CREATE INDEX idx_posts_tags ON posts USING gin (tags);

SELECT id FROM posts WHERE tags @> ARRAY['postgresql'];

GIN indeksleri okuma tarafında hızlıdır, ancak yazma maliyeti yüksektir. Çok sık güncellenen tablolarda fastupdate ayarının etkisini ölçmek gerekir.¹

Uyarı

Büyük bir tabloda indeksi CONCURRENTLY olmadan oluşturmak, işlem süresince tabloya yazmayı kilitler. Canlı ortamda her zaman CREATE INDEX CONCURRENTLY kullanın.

BRIN: büyük ve doğal sıralı tablolar

Log veya olay tablosu gibi, satırların eklenme sırasıyla zaman damgasının paralel gittiği tablolarda BRIN çok küçük bir indeksle ciddi kazanç sağlar.

Indeks	100M satır için boyut	Aralık sorgusu
B-tree	~2.1 GB	12 ms
BRIN	~120 KB	38 ms

BRIN, tablonun her pages_per_range sayfalık bloğu için yalnızca bir özet (en küçük ve en büyük değer) tutar. Bu yüzden boyutu kabaca şöyle hesaplanabilir:

\text{BRIN boyutu} \approx \frac{N_{\text{sayfa}}}{\texttt{pages\_per\_range}} \times b_{\text{özet}}

Varsayılan pages_per_range değeri $128$ olduğu için indeks, tablonun sayfa sayısıyla neredeyse orantısız derecede küçük kalır.

BRIN biraz daha yavaş, ama boyut farkı üç büyüklük mertebesi. Sıralama bozulduğunda (örneğin geriye dönük veri yüklendiğinde) etkinliği hızla düşer.

Özet

Emin değilseniz B-tree ile başlayın ve EXPLAIN ANALYZE ile doğrulayın.
Dizi ve JSONB içinde arama yapıyorsanız GIN.
Çok büyük, eklenme sırasına göre dizili tablolarda BRIN’i deneyin.

Dipnotlar

fastupdate açıkken yeni kayıtlar önce bekleyen bir listeye yazılır ve toplu olarak indekse eklenir. Yazma hızlanır, ancak liste büyüdükçe okuma sorguları yavaşlayabilir. Liste boyutu gin_pending_list_limit ile sınırlanır. ↩