PostgreSQL pg_trgm modülü ile metin benzerliğini sorgulayalım ve LIKE sorgularımızı hızlandıralım

01/12/2012

pg_trgm modülü nedir?

pg_trgm modülü, geliştiricilerinin deyimiyle, metinlerin benzerliğini sorgulamak için fonksiyonlar ve operatörler sunmasının yanı sıra benzer stringlerin hızlıca aranabilmesi için index operatör sınıfları da sunar.

pg_trgm modülü contrib paketinde yer alır. Modülü etkin hale getirmek için aşağıdaki SQL sorgusunu çalıştırmanız yeterlidir.

CREATE EXTENSION pg_trgm;

Nasıl çalışır?

pg_trgm modülünün çalışma ilkesi trigramlara dayanır. Trigram, bir string içerisindeki ardışık 3 karaktere verilen addır. Modül, iki stringin benzerliğini içerdikleri ortak trigramların sayısına göre hesaplar. Bu yöntem doğal diller için oldukça başarılıdır ki buna Türkçe de dahildir. Trigramlar hesaplanmadan önce stringin önünde iki boşluk, sonunda da bir boşluk olduğu kabul edilir. Böylece fehmi stringinin trigramları '  f', ' fe', 'feh', 'ehm', 'hmi', 'mi ' olur.

Fonksiyonlar ve Operatörler

Yukarıda pg_trgm modülünün bazı fonksiyonlar ve operatörler sunduğundan bahsetmiştim. Şimdi bu fonksiyonlara ve operatörlere göz atalım.

Fonksiyonlar

similarity(text, text)

Verilen iki stringin benzerliğini belirten 0 ile 1 arasında bir sayı döndürür. Benzerlik arttıkça bu sayı 1'e yaklaşır.

show_trgm(text)

Verilen string içindeki tüm trigramları döndürür. select show_trgm('fehmi'); => {" f"," fe",ehm,feh,hmi,"mi "}

show_limit()

Aşağıda açıklayacağım % operatörü tarafından kullanılmakta olan benzerlik limitini gösterir. Bu limitin üzerinde benzerlik puanına sahip stringler, % operatörü tarafından benzer kabul edilir.

set_limit(real)

Benzerlik limitini setler. Bu değer 0 ile 1 arasında olmalıdır. Varsayılan değer 0.3'tür.

Operatörler

text % text

Verilen iki string arasındaki benzerlik puanı benzerlik limitinin üzerinde ise true aksi halde false döner.

text <-> text

Verilen iki string arasındaki uzaklık puanını döner. Bu puan (1 - benzerlik puanı) olarak hesaplanır.

Index desteği

pg_trgm modülü yüksek hızlı benzerlik araması için text alanlar üzerinde GIN ve GIST türünden index operatör sınıfları sağlıyor. Modülün sağladığı bu index desteği ile PostgreSQL 9.1 sürümünden itibaren LIKE sorgularımızı da hızlandırabiliyoruz. Aşağıda hem GIN hem de GIST türünden pg_trgm index oluşturma örneği yer alıyor.

CREATE TABLE test_trgm (t text);
CREATE INDEX trgm_idx ON test_trgm USING gist (t gist_trgm_ops);
CREATE INDEX trgm_idx ON test_trgm USING gin (t gin_trgm_ops);

Bir yanlış anlaşılmaya mahal vermeden belirtmek isterim ki bu indexler varchar türünden alanlar üzerinde de oluşturulabilirler. Oluşturduğumuz indexi kullanarak benzerlik araması yapmak için aşağıdaki gibi bir sorgu yazabiliriz.

SELECT t, similarity(t, 'fehmi') AS sml
  FROM test_trgm
  WHERE t % 'fehmi'
  ORDER BY sml DESC, t;

Yukarıdaki sorgu fehmi stringine benzer olan stringleri ve benzerlik puanlarını, benzerlik puanına göre azalan olarak getirir. Böylece 'fehmi' stringine en benzer olan stringler en üstte yer alırlar. Aynı sorguyu uzaklık puanı operatürünü(<->) kullanarak aşağıdaki gibi de yazabilirdik.

SELECT t, t <-> 'fehmi' AS uzaklik
  FROM test_trgm
  ORDER BY uzaklik LIMIT 10;

LIKE sorgularında oluşturduğumuz indexin kullanılması için ayrıca bir işlem yapmamız gerekmiyor. Aşağıdaki gibi bir sorgu yazdığımızda pg_trgm indexi devreye giriyor ve sorgunun daha hızlı çalışmasını sağlıyor.

SELECT * FROM test_trgm WHERE t LIKE '%foo%bar';

Bir dip not da GIN ve GIST index türleri için vereyim. GIN indexler GIST indexlere göre daha hızlı aranabilirler ancak oluşturulma ve güncelleme maliyetleri daha yüksektir. Bu sebeple çok sık güncellenen tablolarda GIST indexler daha avantajlı olabilir. Ancak statik ya da çok sık değişmeyen tablolar için GIN indexlerin arama performansından faydanılmalıdır.

Follow me on Twitter