PostgreSQL CTE nedir, hangi sorunu çözer?
CTE, WITH içinde tanımlanan ve tek bir SQL deyimi boyunca kullanılabilen adlandırılmış sorgudur. Uzun bir raporu ücretli siparişler, müşteri toplamları ve sonuç filtresi gibi anlamlı aşamalara ayırabilirsiniz. PostgreSQL WITH belgesi, bu yapının temel kurallarını açıklar.
Örneğin ekipten gelen ihtiyaç şu olsun: ödenmiş siparişlerinin toplamı en az 1000 kuruş olan müşterileri listelemek. İç içe sorgu yazmak mümkündür. Ancak ara adımların adı olduğunda, bekleyen siparişlerin rapora girip girmediğini kodu inceleyen kişinin görmesi kolaylaşır. Buradaki kazanç önce anlam ve bakım kolaylığıdır; hız ayrıca ölçülür.
Çalışan rapor örneği nasıl hazırlanır?
Aşağıdaki SQL bloklarını aynı bağlantıda sırayla çalıştırın. Geçici tablo yalnızca bu bağlantıya aittir. Tutarlar örneği küçük tutmak için tam sayı kuruş olarak saklanır:
CREATE TEMP TABLE report_orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL,
total_cents integer NOT NULL,
status text NOT NULL
);
INSERT INTO report_orders VALUES
(1, 1, 1200, 'paid'),
(2, 1, 800, 'paid'),
(3, 2, 500, 'paid'),
(4, 2, 900, 'pending'),
(5, 3, 1500, 'paid');
Birinci CTE yalnızca ödenmiş satırları seçer. İkinci CTE bunları müşteri bazında toplar. Dış sorgu raporun eşiğini ve gösterim sırasını belirler:
WITH paid_orders AS (
SELECT customer_id, total_cents
FROM report_orders
WHERE status = 'paid'
), customer_totals AS (
SELECT customer_id,
SUM(total_cents) AS total_cents,
COUNT(*) AS order_count
FROM paid_orders
GROUP BY customer_id
)
SELECT customer_id, total_cents, order_count
FROM customer_totals
WHERE total_cents >= 1000
ORDER BY total_cents DESC, customer_id;
Sonuçta müşteri 1 için 2000 ve 2 sipariş; müşteri 3 için 1500 ve 1 sipariş bulunur. Müşteri 2'nin bekleyen siparişi hesaba katılmaz ve ödenmiş toplamı eşikten düşüktür. Böyle bir örnekte yalnızca satır sayısını kontrol etmek yetmez; toplamın hangi kayıtlardan oluştuğunu da doğrulayın.
Her CTE kendi başına anlaşılabilir bir iş tanımı taşımalıdır. paid_orders gibi bir ad filtreyi anlatır; step1 gibi bir ad okuyucuya çok az şey söyler. Bununla birlikte, tek satırlık her ifadeyi ayrı CTE'ye taşımak raporu gereksiz yere uzatabilir.
CTE bir tablo veya kalıcı önbellek midir?
CTE adı onu içeren deyimin kapsamındadır. Yukarıdaki noktalı virgülden sonra yeni bir sorguda customer_totals adını kullanamazsınız. Aynı bağlantıda kalıcı ara sonuç gerekiyorsa geçici tablo gibi başka bir araç değerlendirilir; uygulama boyunca saklanan bir sonuç gerekiyorsa yaşam döngüsü ayrıca tasarlanır.
WITH yazmak, sunucunun her aşamayı mutlaka ayrı ayrı ve metindeki sırayla çalıştıracağı anlamına da gelmez. SQL'in mantıksal anlatımı ile yürütme planı farklı şeylerdir. Sonucun sırasını garanti etmek için dış sorgudaki ORDER BY gerekir; bir ara aşamada görülen sıra yeterli değildir.
MATERIALIZED ve NOT MATERIALIZED neyi değiştirir?
PostgreSQL, özyinelemeli olmayan ve yan etkisiz SELECT CTE'lerini bazı koşullarda ana sorguyla birleştirebilir. Tek kullanım ile birden çok kullanım için varsayılan karar farklılaşabilir. SELECT başvurusundaki WITH açıklaması, MATERIALIZED ve NOT MATERIALIZED seçeneklerinin sınırlarını belirtir.
Aynı küçük veri üzerinde iki planı karşılaştırabilirsiniz:
EXPLAIN (COSTS OFF)
WITH paid AS MATERIALIZED (
SELECT customer_id, total_cents
FROM report_orders
WHERE status = 'paid'
)
SELECT * FROM paid WHERE customer_id = 1;
EXPLAIN (COSTS OFF)
WITH paid AS NOT MATERIALIZED (
SELECT customer_id, total_cents
FROM report_orders
WHERE status = 'paid'
)
SELECT * FROM paid WHERE customer_id = 1;
İlk planda CTE Scan görebilirsiniz. İkincisinde müşteri koşulu tablonun taramasına taşınabilir. Bu gözlem, ikinci seçeneğin her raporda daha hızlı olduğunu kanıtlamaz. Pahalı bir hesaplama tekrar kullanılıyorsa birleştirme tekrarlı işe yol açabilir; ayrı hesaplama ise dış filtrenin erken uygulanmasını engelleyebilir.
CTE performansı nasıl değerlendirilir?
Önce sonucun doğruluğunu, sonra planı inceleyin. Gerçekçi veri hacmi, filtre seçiciliği ve mevcut indeksler olmadan yapılan süre karşılaştırması yanıltabilir. EXPLAIN rehberi, planı nasıl okuyacağınızı daha ayrıntılı ele alır.
Örnekteki EXPLAIN sorguyu çalıştırmadan tahmini planı gösterir. EXPLAIN ANALYZE ise sorguyu gerçekten yürütür; özellikle veri değiştiren deyimlerde bu fark önemlidir. Üretimde denemeden önce işlemin etkisini anlayın. Adlandırılmış bir ara aşama, doğrulanmamış bir optimizasyon önerisine dönüşmemelidir.
Bir grubun en son kaydını seçmek gibi sıralama ihtiyaçlarında CTE ile window functions birlikte kullanılabilir. Bu ikinci konu için ROW_NUMBER ve RANK rehberine bakabilirsiniz. CTE sorgunun parçalarını adlandırır; hangi satırın kazanacağını seçen kuralı sizin kurmanız gerekir.
Sık sorulan sorular
WITH sonucu sonraki istekte yeniden kullanılır mı? Hayır. CTE, kalıcı veya uygulama genelinde paylaşılan bir önbellek değildir.
NOT MATERIALIZED her zaman daha hızlı mı? Hayır. Birleştirme, filtreleri erkene taşıyabilir ama aynı hesabın tekrarlanmasına da neden olabilir.
Bu örnek hiyerarşik veri sorgular mı? Hayır. Burada özyinelemesiz raporlama anlatılıyor; WITH RECURSIVE ayrı bir kullanım biçimidir.

