Nhiều người đã và đang sử dụng - dịch vụ trực quan hóa kế hoạch PostgreSQL của chúng tôi có thể không biết về một trong những siêu năng lực của nó - biến một đoạn nhật ký máy chủ khó đọc...
... thành một truy vấn được thiết kế đẹp mắt với các gợi ý theo ngữ cảnh cho các nút kế hoạch tương ứng:
Trong bản ghi này của phần thứ hai của ông Tôi sẽ cho bạn biết chúng tôi đã làm được điều này như thế nào.
Bạn có thể tìm thấy bản ghi của phần đầu tiên, dành riêng cho các vấn đề về hiệu năng truy vấn điển hình và giải pháp cho chúng trong bài viết .
Đầu tiên, hãy bắt đầu tô màu - và chúng ta sẽ không tô màu theo kế hoạch nữa, chúng ta đã tô màu rồi, chúng ta đã thấy nó đẹp và dễ hiểu rồi, mà là một yêu cầu.
Đối với chúng tôi, có vẻ như với một “trang tính” chưa được định dạng như vậy, yêu cầu được lấy từ nhật ký trông rất xấu và do đó bất tiện.
Đặc biệt là khi các nhà phát triển “dán” nội dung của yêu cầu vào mã (tất nhiên đây là một phản mẫu, nhưng nó vẫn xảy ra) trong một dòng. Tệ hại!
Hãy vẽ cái này bằng cách nào đó đẹp hơn.
Và nếu chúng ta có thể vẽ đẹp điều này, tức là tháo rời và ghép lại phần thân của yêu cầu, thì chúng ta có thể “đính kèm” một gợi ý cho từng đối tượng của yêu cầu này - điều gì đã xảy ra tại điểm tương ứng trong kế hoạch.
Cây cú pháp truy vấn
Để làm điều này, trước tiên yêu cầu phải được phân tích cú pháp.
Bởi vì chúng tôi có , sau đó chúng tôi đã tạo một mô-đun cho nó, bạn có thể . Trên thực tế, đây là những “ràng buộc” mở rộng cho các phần bên trong của chính trình phân tích cú pháp PostgreSQL. Nghĩa là, ngữ pháp chỉ đơn giản là được biên dịch nhị phân và các ràng buộc được tạo ra từ NodeJS. Chúng tôi lấy mô-đun của người khác làm cơ sở - không có bí mật lớn nào ở đây.
Chúng tôi cung cấp nội dung của yêu cầu làm đầu vào cho hàm của mình - ở đầu ra, chúng tôi nhận được một cây cú pháp được phân tích cú pháp ở dạng đối tượng JSON.
Bây giờ chúng ta có thể chạy qua cây này theo hướng ngược lại và tập hợp một yêu cầu với các mức thụt lề, tô màu và định dạng mà chúng ta muốn. Không, điều này không thể tùy chỉnh được, nhưng đối với chúng tôi, điều này có vẻ thuận tiện.
Ánh xạ các nút truy vấn và kế hoạch
Bây giờ hãy xem cách chúng ta có thể kết hợp kế hoạch mà chúng ta đã phân tích ở bước đầu tiên và truy vấn mà chúng ta đã phân tích ở bước thứ hai.
Hãy lấy một ví dụ đơn giản - chúng ta có một truy vấn tạo CTE và đọc từ đó hai lần. Anh ta tạo ra một kế hoạch như vậy.
CTE
Nếu bạn xem kỹ thì đến phiên bản 12 (hoặc bắt đầu từ đó với từ khóa MATERIALIZED) sự hình thành .
Điều này có nghĩa là nếu chúng ta thấy thế hệ CTE ở đâu đó trong yêu cầu và một nút ở đâu đó trong kế hoạch CTE, thì các nút này chắc chắn sẽ “chiến đấu” với nhau, chúng ta có thể kết hợp chúng ngay lập tức.
Vấn đề với dấu hoa thị: CTE có thể được lồng vào nhau.
Có những cái được lồng rất kém, thậm chí có những cái có cùng tên. Ví dụ, bạn có thể bên trong CTE A làm CTE X, và ở cùng mức bên trong CTE B làm lại lần nữa CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...Khi so sánh, bạn phải hiểu điều này. Hiểu được điều này “bằng mắt” - thậm chí nhìn thấy kế hoạch, thậm chí nhìn thấy nội dung của yêu cầu - là rất khó. Nếu thế hệ CTE của bạn phức tạp, lồng nhau và có nhiều yêu cầu lớn thì nó hoàn toàn vô thức.
UNION
Nếu chúng ta có một từ khóa trong truy vấn UNION [ALL] (toán tử nối hai mẫu), thì trong sơ đồ, nó tương ứng với một nút Append, hoặc một số Recursive Union.
Đó là "ở trên" ở trên UNION - đây là hậu duệ đầu tiên của nút của chúng tôi, nằm ở “bên dưới” - nút thứ hai. Nếu thông qua UNION chúng ta có nhiều khối được "dán" cùng một lúc, sau đó Append-vẫn sẽ chỉ có một nút, nhưng nó sẽ không có hai mà có nhiều nút con - theo thứ tự tương ứng:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
Vấn đề với dấu hoa thị: bên trong thế hệ lấy mẫu đệ quy (WITH RECURSIVE) cũng có thể có nhiều hơn một UNION. Nhưng chỉ khối cuối cùng sau khối cuối cùng là luôn đệ quy UNION. Mọi thứ ở trên là một nhưng khác nhau UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
(...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...
Bạn cũng cần có khả năng “đưa ra” những ví dụ như vậy. Trong ví dụ này chúng ta thấy rằng UNION-có 3 phân đoạn trong yêu cầu của chúng tôi. Theo đó, một UNION phù hợp với Append-node, và sang nút khác - Recursive Union.
Đọc-ghi dữ liệu
Mọi thứ đã được trình bày, bây giờ chúng tôi biết phần nào của yêu cầu tương ứng với phần nào của kế hoạch. Và trong những phần này, chúng ta có thể dễ dàng và tự nhiên tìm thấy những đồ vật “có thể đọc được”.
Từ quan điểm truy vấn, chúng tôi không biết đó là bảng hay CTE, nhưng chúng được chỉ định bởi cùng một nút RangeVar. Và xét về mặt “khả năng đọc”, đây cũng là một tập hợp các nút khá hạn chế:
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]
Chúng tôi biết cấu trúc của kế hoạch và truy vấn, chúng tôi biết sự tương ứng của các khối, chúng tôi biết tên của các đối tượng - chúng tôi thực hiện so sánh 1-1.
Lần nữa nhiệm vụ "có dấu hoa thị". Chúng tôi nhận yêu cầu, thực hiện nó, chúng tôi không có bất kỳ bí danh nào - chúng tôi chỉ đọc nó hai lần từ cùng một CTE.
Chúng tôi xem xét kế hoạch - vấn đề là gì? Tại sao chúng tôi lại có bí danh? Chúng tôi đã không đặt hàng nó. Anh ta lấy đâu ra một “con số” như vậy?
PostgreSQL tự thêm nó vào. Bạn chỉ cần hiểu rằng chỉ là một bí danh như vậy Đối với chúng tôi, với mục đích so sánh với kế hoạch, nó không có ý nghĩa gì, nó chỉ được thêm vào đây. Chúng ta đừng chú ý đến anh ta.
Thứ hai nhiệm vụ "có dấu hoa thị": nếu chúng ta đang đọc từ một bảng được phân vùng thì chúng ta sẽ nhận được một nút Append hoặc Merge Append, sẽ bao gồm một số lượng lớn “trẻ em” và mỗi đứa trong số đó sẽ bằng cách nào đó Scan'om từ phần bảng: Seq Scan, Bitmap Heap Scan hoặc Index Scan. Tuy nhiên, trong mọi trường hợp, những "con" này sẽ không phải là những truy vấn phức tạp - đây là cách có thể phân biệt các nút này với Append khi UNION.
Chúng tôi cũng hiểu những nút thắt như vậy, gom chúng “thành một đống” và nói: “mọi thứ bạn đọc từ megatable đều ở đây và trong cây".
Các nút nhận dữ liệu "đơn giản"
Values Scan tương ứng trong kế hoạch VALUES trong yêu cầu.
Result là một yêu cầu không có FROM giống như SELECT 1. Hoặc khi bạn cố tình diễn đạt sai trong WHERE-block (sau đó thuộc tính xuất hiện One-Time Filter):
EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1Result (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 “bản đồ” tới các SRF cùng tên.
Nhưng với các truy vấn lồng nhau, mọi thứ phức tạp hơn - thật không may, chúng không phải lúc nào cũng trở thành InitPlan/SubPlan. Đôi khi họ biến thành ... Join hoặc ... Anti Join, đặc biệt là khi bạn viết một cái gì đó như WHERE NOT EXISTS .... Và ở đây không phải lúc nào cũng có thể kết hợp chúng - trong văn bản của kế hoạch không có toán tử nào tương ứng với các nút của kế hoạch.
Lần nữa nhiệm vụ "có dấu hoa thị": một số VALUES trong yêu cầu. Trong trường hợp này và trong kế hoạch, bạn sẽ nhận được một số nút Values Scan.
Các hậu tố “được đánh số” sẽ giúp phân biệt chúng với nhau - chúng được thêm chính xác theo thứ tự tìm thấy các hậu tố tương ứng VALUES-chặn theo yêu cầu từ trên xuống dưới.
Xử lí dữ liệu
Có vẻ như mọi thứ trong yêu cầu của chúng tôi đã được sắp xếp - tất cả những gì còn lại là Limit.
Nhưng ở đây mọi thứ đều đơn giản - chẳng hạn như các nút như Limit, Sort, Aggregate, WindowAgg, Unique “ánh xạ” từng người một tới các toán tử tương ứng trong yêu cầu, nếu họ có ở đó. Không có “ngôi sao” hay khó khăn nào ở đây.
THAM GIA
Khó khăn nảy sinh khi chúng ta muốn kết hợp JOIN giữa bọn họ. Điều này không phải lúc nào cũng có thể, nhưng nó có thể.
Từ quan điểm của trình phân tích cú pháp truy vấn, chúng ta có một nút JoinExpr, có đúng hai con - trái và phải. Theo đó, đây là những gì “ở trên” JOIN của bạn và những gì được viết “bên dưới” nó trong yêu cầu.
Và theo quan điểm của kế hoạch, đây là hai hậu duệ của một số * Loop/* Join-nút. Nested Loop, Hash Anti Join,... - đại loại thế.
Hãy sử dụng logic đơn giản: nếu chúng ta có các bảng A và B “kết hợp” với nhau trong kế hoạch, thì chúng cũng có thể được định vị trong yêu cầu A-JOIN-BHoặc B-JOIN-A. Hãy thử kết hợp theo cách này, hãy thử kết hợp theo cách khác, v.v. cho đến khi chúng ta hết những cặp như vậy.
Hãy lấy cây cú pháp của chúng ta, lấy kế hoạch của chúng ta, nhìn vào chúng... không giống nhau!
Hãy vẽ lại nó dưới dạng biểu đồ - ồ, nó trông giống cái gì đó quá!
Hãy lưu ý rằng chúng ta có các nút đồng thời có con B và C - chúng ta không quan tâm theo thứ tự nào. Hãy kết hợp chúng và lật lại hình ảnh của nút.
Hãy nhìn lại lần nữa. Bây giờ chúng ta có các nút có con A và cặp (B + C) - cũng tương thích với chúng.
Tuyệt vời! Hóa ra chúng ta là hai người này JOIN từ yêu cầu với các nút kế hoạch đã được kết hợp thành công.
Than ôi, vấn đề này không phải lúc nào cũng được giải quyết.
Ví dụ: nếu trong một yêu cầu A JOIN B JOIN C, và trong kế hoạch, trước hết, các nút A và C “bên ngoài” đã được kết nối. Nhưng không có toán tử nào như vậy trong yêu cầu, chúng tôi không có gì để làm nổi bật, không có gì để đính kèm gợi ý. Tương tự với dấu phẩy khi bạn viết A, B.
Tuy nhiên, trong hầu hết các trường hợp, hầu hết tất cả các nút đều có thể được "cởi trói" và bạn có thể nhận được loại hồ sơ này ở bên trái kịp thời - theo nghĩa đen, giống như trong Google Chrome khi bạn phân tích mã JavaScript. Bạn có thể thấy mỗi dòng và mỗi câu lệnh mất bao lâu để “thực thi”.
Và để giúp bạn sử dụng tất cả những thứ này thuận tiện hơn, chúng tôi đã thực hiện lưu trữ , nơi bạn có thể lưu và sau đó tìm thấy kế hoạch của mình cùng với các yêu cầu liên quan hoặc chia sẻ liên kết với ai đó.
Nếu bạn chỉ cần đưa một truy vấn không thể đọc được vào một biểu mẫu phù hợp, hãy sử dụng .
Nguồn: www.habr.com