PostgreSQL LIKE Performansı: CAST, Concat ve OR Karşılaştırması

Geçenlerde gördüğüm bir sorgu üzerine düşünmeye başladım. LongColumn (bigint) ve Description (text) diye iki sütun vardı. Arama şöyle çalışıyordu:

// Eski kod: CAST + concat
context.Documents
    .Where(d => EF.Functions.Like(
        d.LongColumn.ToString() + " " + d.Description, pattern))

EF Core bunu LongColumn::text || ' ' || Description olarak PostgreSQL'e gönderiyordu. bigint bir sütunda LIKE aramak için önce ::text cast edip Description ile birleştirmek gerekiyordu. Daha sonra LongColumn'daki veriler TextColumn adlı text tipinde yeni bir sütuna taşındı. CAST ortadan kalktı.

Peki şimdi sorguyu nasıl yazmalıyız? Altı farklı yol var:

// A: CAST + concat (eski yol)
.Where(d => EF.Functions.Like(
    d.LongColumn.ToString() + " " + d.Description, pattern))

// B: CAST, tek sütun
.Where(d => EF.Functions.Like(d.LongColumn.ToString(), pattern))

// C: TextColumn concat
.Where(d => EF.Functions.Like(
    d.TextColumn + " " + d.Description, pattern))

// D: TextColumn tek sütun
.Where(d => EF.Functions.Like(d.TextColumn, pattern))

// E: TextColumn OR Description (ayrı ayrı LIKE)
.Where(d => EF.Functions.Like(d.TextColumn, pattern)
         || EF.Functions.Like(d.Description, pattern))

// F: LongColumn::text OR Description (CAST'li OR)
.Where(d => EF.Functions.Like(d.LongColumn.ToString(), pattern)
         || EF.Functions.Like(d.Description, pattern))

Hangisi daha hızlı? CAST maliyeti ne kadar? Concat index'i bozuyor mu? OR yaklaşımı index kullanabiliyor mu? Bu soruları cevaplamak için 2 milyon satırlık bir benchmark kurdum.

Benchmark Kurulumu

• PostgreSQL 16 (Docker), port 5433
• documents tablosu: Id, LongColumn (bigint), TextColumn (text), Description (text) - 2 milyon satır
• TextColumn: LongColumn'ın string karşılığı + Türkçe metin. Ortalama 439 byte
• LongColumn: rastgele 10 haneli bigint. String karşılığı ortalama 14 byte
• Description: Türkçe metin, ortalama 423 byte
• Index'ler TextColumn ve Description üzerinde (bigint sütuna text index'i oluşturulmaz)
• 3 index stratejisi: Yok, B-tree, GIN trigram (pg_trgm)
• 3 pattern: %ankara% (%42 satır), %de% (%99 satır), %xyzqwerty% (hiç yok)
• Her senaryo 5 kez çalıştırıldı, en iyi/en kötü atıldı, 3'ünün ortalaması
• EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ile ölçüldü

EF Core'un Ürettiği SQL

A - CAST + concat

-- @__pattern_1='%ankara%'
SELECT d."Id", d."Description", d."LongColumn", d."TextColumn"
FROM documents AS d
WHERE d."LongColumn"::text || ' ' || d."Description" LIKE @__pattern_1 ESCAPE ''

B - CAST, tek sütun

WHERE d."LongColumn"::text LIKE @__pattern_1 ESCAPE ''

C - TextColumn concat

WHERE d."TextColumn" || ' ' || d."Description" LIKE @__pattern_1 ESCAPE ''

D - TextColumn tek sütun

WHERE d."TextColumn" LIKE @__pattern_1 ESCAPE ''

E - TextColumn OR Description

WHERE d."TextColumn" LIKE @__pattern_1 ESCAPE '' OR d."Description" LIKE @__pattern_1 ESCAPE ''

F - LongColumn::text OR Description

WHERE d."LongColumn"::text LIKE @__pattern_1 ESCAPE '' OR d."Description" LIKE @__pattern_1 ESCAPE ''

d.LongColumn.ToString() → LongColumn::text. string + string → ||. ESCAPE '' EF Core varsayılanı, performansa etkisiz.

Sonuçlar - GIN Trigram İndex ile

En kritik senaryo GIN index varken. B-tree None ile aynı sonucu verdiği için atlıyorum - LIKE '%x%' B-tree'nin çalışma prensibine ters.

Yaklaşım	Pattern	Süre (ms)	Read Blocks	Satır
A: CAST+concat	%ankara%	702.35	465,400	1,036,239
A: CAST+concat	%de%	645.73	232,304	1,991,384
A: CAST+concat	%xyzqwerty%	468.41	464,416	0
B: CAST tek	%ankara%	131.93	459,568	0
B: CAST tek	%de%	328.45	229,556	0
B: CAST tek	%xyzqwerty%	137.11	457,680	0
C: TextConcat	%ankara%	996.65	453,224	1,729,401
C: TextConcat	%de%	640.01	226,244	1,999,945
C: TextConcat	%xyzqwerty%	780.89	451,480	0
D: TextSingle	%ankara%	1,644.34	224,260	850,373
D: TextSingle	%de%	552.69	224,148	1,987,804
D: TextSingle	%xyzqwerty%	0.01	0	0
E: TextOR	%ankara%	2,480.59	223,484	1,297,051
E: TextOR	%de%	543.10	223,204	1,999,945
E: TextOR	%xyzqwerty%	0.01	0	0
F: CAST OR	%ankara%	1,776.36	222,064	777,179
F: CAST OR	%de%	627.46	221,848	1,991,384
F: CAST OR	%xyzqwerty%	459.11	443,016	0

Kritik Bulgu 1: GIN İndex - Kim Kullanıyor, Kim Kullanamıyor?

%xyzqwerty% pattern'ine odaklanalım. Bu pattern hiçbir satırda yok - index'in gerçek etkisini en net burada görüyoruz:

#	Yaklaşım	SQL Özeti	Süre	Tarama Tipi	İndex?
A	CAST+concat	LongCol::text || Desc	468ms	Seq Scan	❌
B	CAST tek	LongCol::text	137ms	Seq Scan	❌
C	TextConcat	TextCol || Desc	781ms	Seq Scan	❌
D	TextSingle	TextCol	0.01ms	Bitmap Index Scan	✅
E	Text OR	TextCol OR Desc	0.01ms	BitmapOr	✅✅
F	CAST OR	LongCol::text OR Desc	459ms	Seq Scan	❌

Sadece D ve E GIN index kullanabiliyor. Neden?

A, B, C: || concat ve ::text CAST birer expression. GIN index'i TextColumn ve Description sütunları üzerinde. Expression'lar bu index'lerle eşleşmez - planner Seq Scan'e düşer.

D: TextColumn direkt sütun referansı. ix_text_gin tam bu sütunda. Planner anında Bitmap Index Scan kullanır.

E: İki direkt sütun referansı, OR ile bağlı. PostgreSQL ix_text_gin ve ix_desc_gin index'lerini ayrı ayrı tarar, sonuçları BitmapOr ile birleştirir. Çift index, tek seferde.

F: LongColumn::text CAST'li, Description direkt. CAST'li kısım index kullanamaz. PostgreSQL OR ifadesinin tamamını Seq Scan'e düşürür - Description'ın GIN index'i olsa bile.

Kritik Bulgu 2: E'nin EXPLAIN Planı - BitmapOr

Yaklaşım E (TextColumn OR Description) GIN index ile şu planı üretiyor:

Bitmap Heap Scan (time=0.003ms, rows=0)
  -> BitmapOr
    -> Bitmap Index Scan on ix_text_gin (time=0.003ms)
       Index Cond: ("TextColumn" ~~ '%xyzqwerty%')
    -> Bitmap Index Scan on ix_desc_gin (time=0.003ms)
       Index Cond: ("Description" ~~ '%xyzqwerty%')

İki index paralel taranıyor, BitmapOr ile birleştiriliyor, heap'e hiç gidilmiyor. Sonuç: 0.01ms.

Bu, ayrı ayrı LIKE + OR yaklaşımının concat'tan neden bu kadar üstün olduğunu gösteriyor. Concat (A, C) ifadeyi tek bir expression'a dönüştürüp index'i devre dışı bırakırken, OR her sütunu ayrı ayrı index'letebiliyor.

Kritik Bulgu 3: CAST OR'u Zehirliyor

F (LongColumn::text OR Description) GIN index varken bile Seq Scan:

Gather (workers=2) -> Parallel Seq Scan
  Filter: ((LongColumn::text ~~ '%xyzqwerty%') OR (Description ~~ '%xyzqwerty%'))
  Rows Removed by Filter: 666,667 (x3 workers = 2M)
  Execution Time: 459ms

Description'ın GIN index'i (ix_desc_gin) var. Ama LongColumn::text CAST'i non-indexable olduğu için, PostgreSQL tüm OR ifadesini Seq Scan olarak planlıyor. Tek bir non-indexable terim, tüm sorguyu index'siz bırakıyor.

Bu, migration'ın neden kritik olduğunu gösteriyor: LongColumn (bigint) durduğu sürece, ister concat ister OR kullan, index faydası alamazsın.

Kritik Bulgu 4: Hız Sıralaması (GIN, seyrek pattern)

Kazananlar:

1. D ve E: 0.01ms - index kullanıyor, sub-milisaniye
2. B: 137ms - Seq Scan ama en kısa veri (14 byte)
3. A: 468ms - Seq Scan, CAST + concat (~438 byte)
4. F: 459ms - Seq Scan, CAST OR (~438 byte)
5. C: 781ms - Seq Scan, TextConcat (~863 byte)

En yavaş C çünkü en büyük veriyi tarıyor (TextColumn 439B + Description 423B = 862B). En hızlı Seq Scan B çünkü sadece 14 byte'lık LongColumn::text taranıyor.

Ama index devredeyken veri boyutunun önemi kalmıyor - D de 439 byte tarıyor olmasına rağmen index sayesinde 0.01ms.

Diğer Bulgular

Sık pattern'lerde index kullanılmıyor. %de% ve %ankara% için tüm yaklaşımlar Seq Scan. Planner, çok satır match olunca heap lookup maliyetinin direkt taramayı geçtiğini biliyor.

CAST maliyeti düşük. bigint::text dönüşümü mikrosaniye seviyesinde. Asıl maliyet concat ile iki büyük sütunu birleştirmekte.

A, C'den hızlı. CAST+concat (468ms), TextConcat'tan (781ms) daha hızlı. Sebep: LongColumn::text 14 byte, TextColumn 439 byte. CAST maliyeti değil, veri boyutu belirleyici.

Pratik Tavsiyeler

1. LIKE sorgusunda asla concat kullanma. col1 || ' ' || col2 ifadesi sütun bazlı index'leri tamamen devre dışı bırakır. Her zaman ayrı ayrı LIKE + OR yap.

2. OR kullanırken CAST'li sütunları OR'a dahil etme. Tek bir CAST'li terim bile olsa, tüm OR Seq Scan'e düşer. CAST'li sütunu ayrı bir WHERE koşulu olarak yazıp UNION ile birleştirmek daha iyi olabilir.

3. Non-text sütunları text'e çevir. bigint gibi tiplerde LIKE araması yapıyorsan migration ile text sütuna taşı. CAST ortadan kalkar, GIN index kullanılabilir hale gelirsin.

4. GIN trigram index'i seyrek pattern'ler için ezici. Ama sık pattern'lerde planner kullanmayacak. Index'in INSERT/UPDATE maliyetini değerlendir.

5. Ayrı ayrı LIKE + OR, tek sütun LIKE kadar hızlı. E (TextColumn OR Description) de D (TextColumn) de 0.01ms. İkinci sütunu dahil etmenin index maliyeti yok denecek kadar az (BitmapOr).

6. Kendi verinde ölç. Bu benchmark 2M satır, Docker macOS. EXPLAIN ANALYZE ile kendi ortamında test et.

Kaynaklar

• GitHub - pg-perf benchmark projesi
• PostgreSQL pg_trgm dokümantasyonu
• Npgsql EF Core - string çevirileri