Menu

PostgreSQL CREATE INDEX: İndeks Türleri ve Örnekler

CREATE INDEX varsayılan olarak bir B-tree indeksi oluşturur; böylece PostgreSQL tüm tabloyu okumadan satırları bulabilir. Diğer türler (GIN, GiST, BRIN, hash) JSONB, diziler, tam metin arama, aralıklar ve çok büyük tablolar içindir.

Bu sayfada çalıştırılabilir editörler var. Düzenle, çalıştır ve sonucu anında gör.

CREATE INDEX name ON table (column);, PostgreSQL'in tüm tabloyu okumadan eşleşen satırları bulmasını sağlayan bir indeks oluşturur. Varsayılan tür B-tree'dir; =, <, >, BETWEEN, IN, IS NULL ve ORDER BY için çalışır. USING ile başka bir tür seç: gin, gist, spgist, brin ya da hash.

Planın ilk satırında Index Scan using users_email_idx on users yazıyor: PostgreSQL değeri indekste arar ve yalnızca eşleşen satırı okur. CREATE INDEX satırını kaldırıp tekrar çalıştırırsan 10.000 satırın hepsini okuyan Seq Scan on users görürsün.

Birkaç düzine satırlık bir tabloda planlayıcı indeks olsa bile çoğu zaman sıralı taramayı seçer: bir avuç sayfayı okumak daha ucuzdur. Bu planların nasıl okunacağı EXPLAIN ANALYZE sayfasında anlatılıyor.

İndeks türü seçmek

TürNe için kullanılır
B-tree (varsayılan)eşitlik, aralıklar, sıralama, LIKE 'prefix%' (C collation ya da text_pattern_ops ile)
GINçok parçalı değerler: jsonb (@>, ?), diziler (@>, &&), tam metin tsvector
GiSTgeometri, aralıklar (&&, çakışma), tam metin, en yakın komşu ORDER BY ... <->
SP-GiSTçakışmayan bölgelere ayrılan veriler: noktalar, IP adresleri, metin önekleri
BRINdeğerleri fiziksel sırayı izleyen çok büyük tablolar, örneğin yalnızca eklenen bir created_at
Hashyalnızca eşitlik; B-tree aynısını ve fazlasını yapar, bu yüzden nadiren seçilir

Bir jsonb sütunundaki GIN indeksi, içerme sorgularının indeksi kullanmasını sağlar:

Plan, GIN indeksi üzerinde bir Bitmap Index Scan gösteriyor; bu da eşleşen satırları okuyan bir Bitmap Heap Scan'e veri aktarıyor. Operatörleri JSONB sayfasında anlatılıyor.

Birkaç sütunu indekslemek

Çok sütunlu bir B-tree indeksi önce ilk sütuna, sonra ilk sütunun her değeri içinde ikinci sütuna göre sıralar. Baştaki sütunu ya da baştaki sütunla sonrakileri filtreleyen sorgulara hizmet eder. Yalnızca ikinci sütun üzerindeki bir filtre ondan pek fayda görmez, bu yüzden her zaman = ile filtrelediğin sütunu başa koy.

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);

-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;

INCLUDE (PostgreSQL 11+), ek sütunları onlara göre sıralamadan indekste saklar: CREATE INDEX ON orders (customer_id) INCLUDE (total);. Yalnızca bu sütunları okuyan bir sorgu böylece sadece indeksten yanıtlanabilir; buna Index Only Scan denir.

Unique ve kısmi indeksler: bir kuralı bazı satırlarda zorunlu kılmak

CREATE UNIQUE INDEX yinelenen değerleri reddeder. Kısmi (partial) indeks bir WHERE ekler ve yalnızca ona uyan satırları indeksler. Daha küçüktür ve bir benzersizlik kuralının yalnızca bir satır alt kümesine uygulanmasını sağlayabilir:

İki satır da kabul edilir, çünkü yalnızca biri silinmemiş durumda. Aynı e-postaya sahip üçüncü bir canlı satır duplicate key value violates unique constraint "accounts_email_live" hatasıyla başarısız olur. Bir sorgu kısmi indeksi yalnızca WHERE koşulu indeksin koşulunu kapsıyorsa kullanır; burada WHERE NOT deleted.

Bir ifadeyi indekslemek

Bir indeks bir ifadenin sonucunu saklayabilir. Onu kullanmak için sorgunun aynı ifadeyi tekrar etmesi gerekir; email üzerindeki bir indeks WHERE lower(email) = ... sorgusuna yardım etmez:

Plan users_email_lower_idx indeksini kullanıyor, çünkü WHERE tam olarak lower(email) ifadesini tekrar ediyor. İfade immutable olmalıdır, bu yüzden now() ya da bir ayara bağlı bir fonksiyon reddedilir.

Canlı bir veritabanında indeksleri listelemek, oluşturmak ve silmek

pg_indexes her indeksi onu yeniden oluşturacak komutla birlikte listeler:

Birincil anahtar ve UNIQUE kısıtı kendi indekslerini oluşturdu: users_pkey ve users_email_key. psql'de \d users aynı listeyi tablonun sütunlarının altında gösterir.

Yazma alan bir tabloda, yazmalar bloklanmasın diye indeksleri CONCURRENTLY ile oluştur ve sil. Bu biçimler bir transaction bloğu içinde çalışamaz. Başarısız olan eşzamanlı bir oluşturma INVALID bir indeks bırakır; onu silip yeniden oluşturman gerekir:

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx;  -- rebuild a bloated index

Sıkça Sorulan Sorular

PostgreSQL indeksleri otomatik oluşturur mu?

Yalnızca PRIMARY KEY, UNIQUE ve exclusion kısıtları için. Bir yabancı anahtar sütunu indeks almaz, bu yüzden onun üzerinden join yapıyorsan ya da başvurulan tablodan satır siliyorsan indeksi kendin ekle.

PostgreSQL neden indeksimi kullanmıyor?

Sık görülen nedenler: tablo küçüktür ve tamamını okumak daha ucuzdur; sorgu satırların büyük bir kısmını döndürür; WHERE sütunu indekste olmayan bir fonksiyon ya da cast ile sarar; ya da istatistikler eskidir (ANALYZE çalıştır). Hangi planın seçildiğini EXPLAIN gösterir.

Kaç indeks fazla sayılır?

Sabit bir sayı yok. Her indeks bazı okumaları hızlandırır, tablodaki her INSERT'ü ve çoğu UPDATE'i yavaşlatır ve disk alanı kaplar. pg_stat_user_indexes.idx_scan her indeksin ne sıklıkla kullanıldığını gösterir, böylece sıfırda kalanları silebilirsin.

CREATE INDEX tabloyu kilitler mi?

Düz CREATE INDEX bitene kadar tablodaki INSERT, UPDATE ve DELETE işlemlerini bloklar, okumalar ise devam eder. CREATE INDEX CONCURRENTLY oluşturma sırasında yazmalara izin verir, daha uzun sürer ve bir transaction bloğu içinde çalışamaz.

Coddy programlama dilleri çizimi

Coddy ile kodlamayı öğren

Başla