Postgresql’de Fonksiyon ile Oluşturulan Dinamik Sütunlar Üzerinde Index Nasıl Oluşturulur?
Bu yazıda bir süredir geliştirdiğimiz bir SAAS uygulamasında karşılaştığımız problemi ve çözümünü anlatacağım.
Problem
Basit olarak açıklamak gerekirse, uygulamanın ana sayfasında veritabanına request atılarak kayıtlar belli bir sıralamaya göre gösteriliyor. Bu sıralama ise iki integer değerinin farkına göre işliyor. İlk sayfada direk olarak bir veritabanı bağlantısı olduğu için performans sorunları yaşamamak adına bu sorguyu optimize etmek istedik. Sütunları çekip hesaplamamızı yapabiliriz fakat bu performans açısından çok kötü bir çözüm. Cachelemeyi de düşündük fakat bu aşamada buna gerek olmadığını düşünerek daha ileriki bir zamana erteledik.Peki bu işlemin performanslı bir şekilde çalışması için ne yapmalıyız?
Çözüm
Bizim başvurduğumuz çözüm, sorguyu iyileştirmek ve performansı arttırmak icin index oluşturmak fakat bu alan dynamic olarak hesaplandığı için tabloda bir sütun yok ve dolayısıyla index oluşturamıyoruz. Burada da yardımımıza postgresqlin functionları koşuyor. Functionlar basitce dinamik olarak hesaplanan bir sütun oluşturabiliyor ve biz bu sütun üzerinde index tanımlayabiliyoruz. O yüzden bu çözüm problemimiz için biçilmiş kaftan. Şimdi bu adımlara tek tek bakalım.
1) Fonksiyon Oluşturma
Veritabanında posts diye bir tablomuz olsun ve bu tablo upvotes ve downvotes diye iki sütun tutsun.
Fonksiyonumuz da basitçe (upvotes-downvotes) işlemini yapıp geriye virtual column olarak dönsün.
Bu kod ile calculate isminde bir fonksiyon oluşturuyoruz ve fonksiyonumuz posts tablomuzu parametre olarak alıyor ve geriye (upvotes-downvotes) dönüyor. Burada $1 posts tablomuzu temsil ediyor. Oluşturduğumuz bu fonksiyonu sql sorgumuzda virtual column olarak çağırabileceğiz.
2) Index Oluşturma
Fonksiyonumuzu oluşturduk, şimdi sıra index oluşturmada.
Bu kod ile fonksiyonumuza index oluşturmuş oluyoruz. Index’imize calculate_idx adını verdik. Bunu posts tablomuzun altında indexes’in içinde görebiliriz.
Sonuç
Şimdi hepsini birleştirerek sql sorgumuzu çalıştıralım ve sonucunu görelim.
Görüldüğü gibi calculate sütunu calculate fonksiyonumuzdan dönen sonucu tutuyor ve dönen değerler calculate sütununa göre sıralanmış bir şekilde döndü.
Buraya kadar her şey başarılı görünüyor, peki ama sql sorgumuz, fonksiyon üzerinde oluştrduğumuz indexi kullanarak mı dönüyor yoksa sorgu sequential olarak mı çalıştırıldı? Bunu test edebilmek için sorguyu EXPLAIN komutuyla çalıştırmalıyız.
Görüldüğü gibi index oluşturmuş olmamıza rağmen sorgu sequential çalıştı. Burada tablomuzda az kayıt olduğu için, sequential scan index’e göre daha iyi çalışıyor. Bu yüzden de database seuqential çalıştırıyor. Sequential scan’i disable ederek, index kullanması gerektiği zaman kullanacak mı, emin olabiliriz.
Bu şekilde çalıştırarak index kullanımını görebiliriz.
Böylelikle postgresql’de bir virtual column üzerinde fonksiyon oluşturup, o fonksiyon için de index oluşturarak performans açısından güzel bir çözüm elde etmiş olduk.
Bir sonraki yazıda görüşmek üzere.
