🥇PostgreSQL Sorgu Profileri: Bir Plan ve Sorguyu Nasıl Eşleştirirsiniz

Zaten kullanan birçok kişi açıkla.tensor.ru - PostgreSQL planı görselleştirme hizmetimiz, süper güçlerinden birinin farkında olmayabilir - sunucu günlüğünün okunması zor bir parçasını dönüştürmek...

... karşılık gelen plan düğümleri için bağlamsal ipuçları içeren güzel tasarlanmış bir sorguya:

Onun ikinci bölümünün bu transkripsiyonunda PGConf.Rusya 2020'deki rapor Size bunu nasıl başardığımızı anlatacağım.

Tipik sorgu performansı sorunlarına ve çözümlerine ayrılan ilk bölümün transkriptini makalede bulabilirsiniz. "Rahatsız edici SQL sorguları için tarifler".

Öncelikle renklendirmeye başlayalım - ve artık planı renklendirmeyeceğiz, onu zaten renklendirdik, zaten güzel ve anlaşılır bir şekilde hazırladık, ancak bir ricamız var.

Bize öyle geldi ki, bu kadar biçimlendirilmemiş bir "sayfa" ile günlükten çekilen istek çok çirkin ve bu nedenle sakıncalı görünüyor.

Özellikle geliştiriciler isteğin gövdesini koda "yapıştırdığında" (bu elbette bir anti-modeldir, ancak olur) tek satırda. Berbat!

Bunu bir şekilde daha güzel çizelim.

Ve bunu güzel bir şekilde çizebilirsek, yani isteğin gövdesini parçalara ayırıp tekrar bir araya getirebilirsek, o zaman bu isteğin her nesnesine bir ipucu "ekleyebiliriz" - planın ilgili noktasında ne olduğu.

Sorgu sözdizimi ağacı

Bunu yapmak için önce isteğin ayrıştırılması gerekir.

Çünkü bizde var sistemin çekirdeği NodeJS üzerinde çalışır, sonra bunun için bir modül hazırladık, GitHub'da bul. Aslında bunlar PostgreSQL ayrıştırıcısının dahili bileşenlerine yönelik genişletilmiş "bağlamalardır". Yani dilbilgisi basitçe ikili olarak derlenir ve ona NodeJS'den bağlamalar yapılır. Başkalarının modüllerini temel aldık - burada büyük bir sır yok.

İsteğin gövdesini fonksiyonumuza girdi olarak besliyoruz - çıktıda JSON nesnesi biçiminde ayrıştırılmış bir sözdizimi ağacı elde ediyoruz.

Artık bu ağacın üzerinden ters yönde ilerleyebilir ve istediğimiz girintilere, renklendirmeye ve biçimlendirmeye sahip bir istek oluşturabiliriz. Hayır, bu kişiselleştirilemez, ancak bize bunun uygun olacağı görüldü.

Sorgu ve plan düğümlerini eşleme

Şimdi ilk adımda analiz ettiğimiz plan ile ikinci adımda analiz ettiğimiz sorguyu nasıl birleştirebileceğimizi görelim.

Basit bir örnek verelim; bir CTE üreten ve ondan iki kez okuyan bir sorgumuz var. Böyle bir plan yapıyor.

CTE

Dikkatli bakarsanız sürüm 12'ye kadar (veya ondan başlayarak anahtar kelimeyle MATERIALIZED) oluşumu CTE planlamacı için mutlak bir engeldir.

Bu, isteğin bir yerinde bir CTE nesli ve planın bir yerinde bir düğüm görürsek anlamına gelir. CTE, o zaman bu düğümler kesinlikle birbirleriyle “kavga eder”, onları hemen birleştirebiliriz.

Yıldız işaretiyle ilgili sorun: CTE'ler iç içe yerleştirilebilir.

Çok kötü yuvalanmış olanlar ve hatta aynı isimde olanlar var. Örneğin, içeride yapabilirsiniz CTE A yapmak CTE Xve içeride aynı seviyede CTE B Tekrar yap CTE X:

WITH A AS (
  WITH X AS (...)
  SELECT ...
)
, B AS (
  WITH X AS (...)
  SELECT ...
)
...

Karşılaştırma yaparken bunu anlamalısınız. Bunu "gözlerinizle" anlamak - hatta planı görmek, hatta talebin metnini görmek - çok zordur. CTE nesliniz karmaşıksa, iç içe geçmişse ve istekler büyükse, o zaman tamamen bilinçsizdir.

BİRLİĞİ

Sorguda bir anahtar kelimemiz varsa UNION [ALL] (iki örneği birleştirme operatörü), o zaman planda bir düğüme karşılık gelir Append, veya bazıları Recursive Union.

Yukarıda "yukarıda" olan UNION - bu, "aşağıda" olan düğümümüzün ilk soyundan geliyor - ikincisi. Eğer bittiyse UNION aynı anda "yapıştırılmış" birkaç bloğumuz var, o zaman Append-hala tek bir düğüm olacak, ancak iki değil, birçok çocuğu olacak - sırasıyla:

  (...) -- #1
UNION ALL
  (...) -- #2
UNION ALL
  (...) -- #3

Append
  -> ... #1
  -> ... #2
  -> ... #3

Yıldız işaretiyle ilgili sorun: özyinelemeli örnekleme üretiminin içinde (WITH RECURSIVE) birden fazla da olabilir UNION. Ancak yalnızca son bloktan sonraki son blok her zaman özyinelemelidir UNION. Yukarıdaki her şey birdir ama farklıdır UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
  (...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...

Ayrıca bu tür örnekleri "çıkarabilmeniz" gerekir. Bu örnekte şunu görüyoruz UNION-Talebimizde 3 bölüm vardı. Buna göre bir UNION maçlar Append-düğüme ve diğerine - Recursive Union.

Okuma-yazma verileri

Her şey planlandı, artık talebin hangi parçasının planın hangi parçasına karşılık geldiğini biliyoruz. Ve bu parçalarda “okunabilir” olan nesneleri kolaylıkla ve doğal bir şekilde bulabiliriz.

Sorgu açısından bakıldığında bunun bir tablo mu yoksa CTE mi olduğunu bilmiyoruz ancak bunlar aynı düğüm tarafından belirlenir RangeVar. Ve "okunabilirlik" açısından bu aynı zamanda oldukça sınırlı bir düğüm kümesidir:

Seq Scan on [tbl]
Bitmap Heap Scan on [tbl]
Index [Only] Scan [Backward] using [idx] on [tbl]
CTE Scan on [cte]
Insert/Update/Delete on [tbl]

Planın ve sorgunun yapısını biliyoruz, blokların yazışmalarını biliyoruz, nesnelerin isimlerini biliyoruz - birebir karşılaştırma yapıyoruz.

tekrar "yıldız işaretli" görev. İsteği alıyoruz, yürütüyoruz, herhangi bir takma adımız yok - sadece aynı CTE'den iki kez okuduk.

Plana bakıyoruz - sorun ne? Neden bir takma adımız vardı? Biz sipariş etmedik. Böyle bir “sayı numarasını” nereden buluyor?

PostgreSQL bunu kendisi ekler. Sadece bunu anlamalısın tam da böyle bir takma ad Bizim açımızdan planla karşılaştırma yapmak açısından hiçbir anlam ifade etmiyor, sadece buraya ekleniyor. Ona dikkat etmeyelim.

İkinci "yıldız işaretli" görev: bölümlenmiş bir tablodan okuyorsak bir düğüm elde ederiz Append veya Merge Appendçok sayıda “çocuktan” oluşacak ve her biri bir şekilde Scanbölüm tablosundan: Seq Scan, Bitmap Heap Scan veya Index Scan. Ancak her durumda, bu "çocuklar" karmaşık sorgular olmayacak - bu düğümler bu şekilde ayırt edilebilir. Append at UNION.

Biz de bu tür düğümleri anlıyoruz, onları “tek yığın halinde” topluyoruz ve şöyle diyoruz: “megatable'dan okuduğunuz her şey burada ve ağacın aşağısında".

"Basit" veri alan düğümler

Values Scan planda karşılık gelir VALUES talepte.

Result olmadan bir istek FROM tür SELECT 1. Veya kasıtlı olarak yanlış bir ifade kullandığınızda WHERE-block (daha sonra özellik görünür One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1

Result  (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
  One-Time Filter: false

Function Scan Aynı adı taşıyan SRF'lere “harita”.

Ancak iç içe sorgularda her şey daha karmaşıktır; ne yazık ki her zaman InitPlan/SubPlan. Bazen dönüşürler ... Join veya ... Anti Joinözellikle şöyle bir şey yazdığınızda WHERE NOT EXISTS .... Ve burada bunları birleştirmek her zaman mümkün değildir - plan metninde planın düğümlerine karşılık gelen operatörler yoktur.

tekrar "yıldız işaretli" görev: bazı VALUES talepte. Bu durumda ve planda birkaç düğüm alacaksınız Values Scan.

“Numaralandırılmış” son ekler, bunları birbirinden ayırmaya yardımcı olacaktır; bunlar, tam olarak karşılık gelenlerin bulunduğu sıraya göre eklenir. VALUES-İstek boyunca yukarıdan aşağıya doğru bloklar.

Veri işleme

İsteğimizdeki her şey çözülmüş gibi görünüyor; geriye kalan tek şey Limit.

Ancak burada her şey basit - düğümler gibi Limit, Sort, Aggregate, WindowAgg, Unique Eğer oradalarsa, istekteki ilgili operatörlerle bire bir "eşleşin". Burada “yıldız” ya da zorluk yok.

JOIN

Birleşmek istediğimizde zorluklar ortaya çıkıyor JOIN onların arasında. Bu her zaman mümkün değildir ancak mümkündür.

Sorgu ayrıştırıcının bakış açısından bir düğümümüz var JoinExprtam olarak iki çocuğu var - sol ve sağ. Buna göre bu, JOIN'inizin "üstünde" olan ve istekte "altında" yazılan şeydir.

Ve plan açısından bakıldığında bunlar bazılarının iki torunu. * Loop/* Join-düğüm. Nested Loop, Hash Anti Join,... - bunun gibi bir şey.

Basit bir mantık kullanalım: Planda birbirine "birleşen" A ve B tablolarımız varsa, bunlar istekte de bulunabilir. A-JOIN-BVeya B-JOIN-A. Bu şekilde birleştirmeye çalışalım, diğer şekilde birleştirmeye çalışalım ve bu çiftler bitene kadar böyle devam edelim.

Sözdizimi ağacımızı alalım, planımızı alalım, onlara bakalım... benzemiyor!

Hadi onu grafikler şeklinde yeniden çizelim - ah, zaten bir şeye benziyor!

Aynı anda B ve C çocuklarına sahip olan düğümlerimiz olduğunu unutmayın; hangi sırayla olduğu umurumuzda değil. Bunları birleştirip düğümün resmini ters çevirelim.

Tekrar bakalım. Artık A çocukları ve (B + C) çiftlerinden oluşan düğümlerimiz var - onlarla da uyumlu.

Harika! Meğerse biz bu ikimizmişiz JOIN istekten plan düğümleri başarıyla birleştirildi.

Ne yazık ki, bu sorun her zaman çözülmüyor.

Örneğin, eğer bir istekteyseniz A JOIN B JOIN Cve planda öncelikle A ve C "dış" düğümleri bağlandı. Ancak istekte böyle bir operatör yok, vurgulayacak hiçbir şeyimiz yok, ipucu ekleyecek hiçbir şeyimiz yok. Yazarken "virgül" ile aynı şey A, B.

Ancak çoğu durumda, neredeyse tüm düğümler "çözülebilir" ve bu tür profil oluşturmayı zamanla solda elde edebilirsiniz - kelimenin tam anlamıyla, Google Chrome'da JavaScript kodunu analiz ettiğinizde olduğu gibi. Her satırın ve her ifadenin "yürütülmesinin" ne kadar sürdüğünü görebilirsiniz.

Tüm bunları kullanmanızı daha kolay hale getirmek için depolama alanı oluşturduk arşivPlanlarınızı kaydedip daha sonra ilişkili isteklerle birlikte bulabileceğiniz veya bağlantıyı birisiyle paylaşabileceğiniz yer.