PostgreSQL შეკითხვის პროფილი: როგორ დავამთხვიოთ გეგმა და მოთხოვნა

ბევრი, ვინც უკვე იყენებს განმარტეთ.tensor.ru - ჩვენმა PostgreSQL გეგმის ვიზუალიზაციის სერვისმა შეიძლება არ იცოდეს მისი ერთ-ერთი ზესახელმწიფო - სერვერის ჟურნალის ძნელად წასაკითხი ნაწილის გადაქცევა...

... ლამაზად შემუშავებულ შეკითხვაში კონტექსტური მინიშნებებით შესაბამისი გეგმის კვანძებისთვის:

მისი მეორე ნაწილის ამ ჩანაწერში ანგარიში PGConf.Russia 2020-ზე მე გეტყვით, როგორ მოვახერხეთ ეს.

პირველი ნაწილის ტრანსკრიპტი, რომელიც ეძღვნება შეკითხვის შესრულების ტიპურ პრობლემებს და მათ გადაწყვეტილებებს, შეგიძლიათ იხილოთ სტატიაში "რეცეპტები დაავადებული SQL მოთხოვნებისთვის".

ჯერ მოფერება დავიწყოთ - და გეგმას აღარ გავაფერადებთ, უკვე გავაფერადეთ, უკვე გვაქვს ლამაზი და გასაგები, მაგრამ თხოვნა.

ჩვენ გვეჩვენებოდა, რომ ასეთი არაფორმატირებული „ფურცლით“ ჟურნალიდან ამოღებული მოთხოვნა ძალიან მახინჯი და, შესაბამისად, მოუხერხებელი ჩანს.


განსაკუთრებით მაშინ, როდესაც დეველოპერები „წებებენ“ მოთხოვნის ნაწილს კოდში (ეს, რა თქმა უნდა, ანტიპატერნაა, მაგრამ ეს ხდება) ერთ ხაზზე. საშინელებაა!

ეს როგორმე უფრო ლამაზად დავხატოთ.


და თუ ჩვენ შეგვიძლია ეს ლამაზად დავხატოთ, ანუ დავშალოთ და დავაბრუნოთ მოთხოვნის სხეული, მაშინ შეგვიძლია ამ მოთხოვნის თითოეულ ობიექტს „მივუმაგროთ“ მინიშნება - რა მოხდა გეგმის შესაბამის წერტილში.

შეკითხვის სინტაქსის ხე

ამისათვის ჯერ მოთხოვნა უნდა გაანალიზდეს.


რადგან გვაქვს სისტემის ბირთვი მუშაობს NodeJS-ზე, შემდეგ ჩვენ გავაკეთეთ მოდული, შეგიძლიათ იპოვეთ იგი GitHub-ზე. სინამდვილეში, ეს არის გაფართოებული „დაკავშირება“ თავად PostgreSQL პარსერის შიდა ნაწილებთან. ანუ, გრამატიკა უბრალოდ ორობითია შედგენილი და მასზე აკინძვა ხდება NodeJS-დან. ჩვენ საფუძვლად ავიღეთ სხვისი მოდულები - აქ დიდი საიდუმლო არ არის.

ჩვენ ვკვებავთ მოთხოვნის სხეულს, როგორც შეყვანას ჩვენს ფუნქციაში - გამოსავალზე ვიღებთ გაანალიზებულ სინტაქსურ ხეს JSON ობიექტის სახით.


ახლა ჩვენ შეგვიძლია გავუშვათ ამ ხეს საპირისპირო მიმართულებით და შევკრიბოთ მოთხოვნა ჩვენთვის სასურველი ჩაღრმავებით, შეღებვით და ფორმატირებით. არა, ეს არ არის კონფიგურირებადი, მაგრამ გვეჩვენებოდა, რომ ეს მოსახერხებელი იქნებოდა.

შეკითხვისა და გეგმის კვანძების რუკების შედგენა

ახლა ვნახოთ, როგორ შეგვიძლია გავაერთიანოთ გეგმა, რომელიც გავაანალიზეთ პირველ ეტაპზე და შეკითხვა, რომელიც გავაანალიზეთ მეორეში.

ავიღოთ მარტივი მაგალითი - ჩვენ გვაქვს შეკითხვა, რომელიც ქმნის CTE-ს და მისგან ორჯერ კითხულობს. ის აყალიბებს ასეთ გეგმას.

CTE

თუ ყურადღებით დააკვირდებით, მე-12 ვერსიამდე (ან მისგან დაწყებული საკვანძო სიტყვით MATERIALIZED) ფორმირება CTE არის აბსოლუტური ბარიერი დამგეგმავისთვის.


ეს ნიშნავს, რომ თუ ჩვენ ვხედავთ CTE თაობას სადმე მოთხოვნაში და კვანძს სადმე გეგმაში CTE, მაშინ ეს კვანძები აუცილებლად „ებრძვიან“ ერთმანეთს, შეგვიძლია დაუყოვნებლივ გავაერთიანოთ ისინი.

ვარსკვლავის პრობლემა: CTE-ები შეიძლება იყოს წყობილი.

არის ძალიან ცუდად მობუდული და თუნდაც ერთი და იგივე სახელი. მაგალითად, შეგიძლიათ შიგნით CTE A გააკეთეთ CTE X, და იმავე დონეზე შიგნით CTE B გააკეთე ისევ CTE X:

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

შედარებისას ეს უნდა გესმოდეთ. ამის „თვალებით“ გაგება - გეგმის დანახვაც კი, მოთხოვნის სხეულის დანახვაც კი - ძალიან რთულია. თუ თქვენი CTE თაობა რთულია, წყობილი და მოთხოვნები დიდია, მაშინ ის სრულიად უგონოა.

UNION

თუ საკვანძო სიტყვა გვაქვს შეკითხვაში UNION [ALL] (ორი ნიმუშის შეერთების ოპერატორი), შემდეგ გეგმაში იგი შეესაბამება რომელიმე კვანძს Append, ან ზოგიერთი Recursive Union.


ის, რაც ზემოთ არის "ზემოთ". UNION - ეს არის ჩვენი კვანძის პირველი შთამომავალი, რომელიც არის "ქვემოთ" - მეორე. თუ მეშვეობით UNION ჩვენ გვაქვს ერთდროულად რამდენიმე ბლოკი "დაწებებული". Append- ჯერ კიდევ იქნება მხოლოდ ერთი კვანძი, მაგრამ მას ეყოლება არა ორი, არამედ ბევრი შვილი - შესაბამისად, თანმიმდევრობით:

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

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

ვარსკვლავის პრობლემა: რეკურსიული შერჩევის თაობის შიგნით (WITH RECURSIVE) ასევე შეიძლება იყოს ერთზე მეტი UNION. მაგრამ მხოლოდ ბოლო ბლოკი უკანასკნელის შემდეგ ყოველთვის რეკურსიულია UNION. ყველაფერი ზემოთ არის ერთი, მაგრამ განსხვავებული UNION:

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

თქვენ ასევე უნდა შეძლოთ ასეთი მაგალითების „გამორჩევა“. ამ მაგალითში ჩვენ ვხედავთ, რომ UNION-ჩვენს მოთხოვნაში იყო 3 სეგმენტი. შესაბამისად, ერთი UNION შეესაბამება Append- კვანძი და მეორე - Recursive Union.

მონაცემების წაკითხვა-ჩაწერა

ყველაფერი ჩამოყალიბებულია, ახლა ჩვენ ვიცით, მოთხოვნის რომელი ნაწილი შეესაბამება გეგმის რომელ ნაწილს. და ამ ნაწილებში ჩვენ შეგვიძლია მარტივად და ბუნებრივად ვიპოვოთ ის ობიექტები, რომლებიც "იკითხებადი".

შეკითხვის თვალსაზრისით, ჩვენ არ ვიცით, არის ეს ცხრილი თუ CTE, მაგრამ ისინი მითითებულია იმავე კვანძით RangeVar. და "წაკითხვის" თვალსაზრისით, ეს ასევე არის კვანძების საკმაოდ შეზღუდული ნაკრები:

• 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]

ჩვენ ვიცით გეგმისა და მოთხოვნის სტრუქტურა, ვიცით ბლოკების შესაბამისობა, ვიცით ობიექტების სახელები - ვაკეთებთ ერთ-ერთ შედარებას.


ისევ დავალება "ვარსკვლავით". ჩვენ ვიღებთ მოთხოვნას, ვასრულებთ მას, ჩვენ არ გვაქვს რაიმე მეტსახელი - უბრალოდ ორჯერ ვკითხულობთ იმავე CTE-დან.


ჩვენ ვუყურებთ გეგმას - რა პრობლემაა? რატომ გვქონდა მეტსახელი? ჩვენ არ შეუკვეთეთ. საიდან იღებს მას ასეთი "ნომრის ნომერი"?

PostgreSQL ამას თავად ამატებს. თქვენ მხოლოდ ამის გაგება გჭირდებათ უბრალოდ ასეთი მეტსახელი ჩვენთვის, გეგმასთან შედარების მიზნით, აზრი არ აქვს, უბრალოდ აქ არის დამატებული. ნუ მივაქცევთ მას ყურადღებას.

მეორე დავალება "ვარსკვლავით": თუ ვკითხულობთ დანაწევრებული ცხრილიდან, მაშინ მივიღებთ კვანძს Append ან Merge Append, რომელიც შედგება დიდი რაოდენობით „ბავშვებისგან“ და რომელთაგან თითოეული როგორღაც იქნება Scanom მაგიდის განყოფილებიდან: Seq Scan, Bitmap Heap Scan ან Index Scan. მაგრამ, ნებისმიერ შემთხვევაში, ეს „ბავშვები“ არ იქნება რთული მოთხოვნები - ასე შეიძლება განვასხვავოთ ეს კვანძები Append ზე UNION.


ჩვენ ასევე გვესმის ასეთი კვანძები, ვაგროვებთ მათ "ერთ წყობაში" და ვამბობთ:ყველაფერი, რასაც მეგატაბლიდან კითხულობთ, არის აქ და ხეზე".

"მარტივი" მონაცემთა მიმღები კვანძები

Values Scan გეგმაში შეესაბამება VALUES მოთხოვნაში.

Result არის მოთხოვნა გარეშე FROM მომწონს SELECT 1. ან როდესაც თქვენ გაქვთ განზრახ ცრუ გამოხატვა WHERE-ბლოკი (შემდეგ გამოჩნდება ატრიბუტი 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 „რუკა“ ამავე სახელწოდების SRF-ებზე.

მაგრამ ჩასმული კითხვებით ყველაფერი უფრო რთულია - სამწუხაროდ, ისინი ყოველთვის არ იქცევიან InitPlan/SubPlan. ზოგჯერ ისინი გადაიქცევიან ... Join ან ... Anti Join, განსაკუთრებით მაშინ, როცა წერ რაღაცას WHERE NOT EXISTS .... და აქ ყოველთვის არ არის შესაძლებელი მათი გაერთიანება - გეგმის ტექსტში არ არის გეგმის კვანძების შესაბამისი ოპერატორები.

ისევ დავალება "ვარსკვლავით": ზოგიერთი VALUES მოთხოვნაში. ამ შემთხვევაში და გეგმაში მიიღებთ რამდენიმე კვანძს Values Scan.


"დანომრილი" სუფიქსები დაგეხმარებათ ერთმანეთისგან განასხვავოთ - ისინი ემატება ზუსტად იმ თანმიმდევრობით, რომლითაც გვხვდება შესაბამისი. VALUES-ბლოკავს მოთხოვნის გასწვრივ ზემოდან ქვემოდან.

მონაცემთა დამუშავება

როგორც ჩანს, ჩვენს მოთხოვნაში ყველაფერი დალაგებულია - დარჩა მხოლოდ Limit.


მაგრამ აქ ყველაფერი მარტივია - ისეთი კვანძები, როგორიცაა Limit, Sort, Aggregate, WindowAgg, Unique მოთხოვნის შესაბამის ოპერატორებს, თუ ისინი იქ არიან. აქ არ არის "ვარსკვლავები" ან სირთულეები.

შემოგვიერთდით

სირთულეები წარმოიქმნება მაშინ, როდესაც გვინდა გაერთიანება JOIN მათ შორის. ეს ყოველთვის არ არის შესაძლებელი, მაგრამ შესაძლებელია.


შეკითხვის ანალიზატორის თვალსაზრისით, ჩვენ გვაქვს კვანძი JoinExpr, რომელსაც ზუსტად ორი შვილი ჰყავს - მარცხენა და მარჯვენა. ეს, შესაბამისად, არის ის, რაც არის "ზემოთ" თქვენი JOIN და რა წერია "ქვემოთ" მოთხოვნაში.

და გეგმის თვალსაზრისით, ეს ზოგიერთის ორი შთამომავალია * Loop/* Join- კვანძი. Nested Loop, Hash Anti Join,... - რაღაც მაგდაგვარი.

მოდით გამოვიყენოთ მარტივი ლოგიკა: თუ გვაქვს A და B ცხრილები, რომლებიც ერთმანეთს „უერთდებიან“ გეგმაში, მაშინ მოთხოვნაში ისინი შეიძლება განთავსდეს. A-JOIN-Bან B-JOIN-A. ვცადოთ გავაერთიანოთ ასე, ვეცადოთ გავაერთიანოთ პირიქით და ასე გავაგრძელოთ მანამ, სანამ ასეთი წყვილები არ გამოვა.

ავიღოთ ჩვენი სინტაქსის ხე, ავიღოთ ჩვენი გეგმა, შევხედოთ მათ... არა მსგავსი!


მოდით გადავახაზოთ იგი გრაფიკების სახით - ოჰ, ეს უკვე რაღაცას ჰგავს!


აღვნიშნოთ, რომ გვაქვს კვანძები, რომლებსაც ერთდროულად ჰყავთ შვილები B და C - არ გვაინტერესებს რა თანმიმდევრობით. მოდით გავაერთიანოთ ისინი და გადავაბრუნოთ კვანძის სურათი.


მოდით კიდევ ერთხელ გადავხედოთ. ახლა ჩვენ გვაქვს კვანძები ბავშვებთან A და წყვილებით (B + C) - თავსებადი მათთანაც.


დიდი! თურმე ეს ორი ვართ JOIN მოთხოვნიდან გეგმის კვანძები წარმატებით გაერთიანდა.

სამწუხაროდ, ეს პრობლემა ყოველთვის არ წყდება.


მაგალითად, თუ მოთხოვნაში A JOIN B JOIN C, და გეგმაში უპირველეს ყოვლისა, "გარე" კვანძები A და C იყო დაკავშირებული. მაგრამ მოთხოვნაში არ არის ასეთი ოპერატორი, არაფერი გვაქვს ხაზგასმული, მინიშნება არაფერი გვაქვს. იგივეა „მძიმით“ როცა წერ A, B.

მაგრამ, უმეტეს შემთხვევაში, თითქმის ყველა კვანძი შეიძლება „გაიხსნას“ და შეგიძლიათ დროულად მიიღოთ ამ ტიპის პროფილირება მარცხნივ - სიტყვასიტყვით, როგორც Google Chrome-ში, როდესაც აანალიზებთ JavaScript კოდს. თქვენ ხედავთ, რამდენი დრო დასჭირდა თითოეულ სტრიქონს და თითოეულ განცხადებას "შესრულებას".


და იმისათვის, რომ თქვენთვის უფრო მოსახერხებელი იყოს ამ ყველაფრის გამოყენება, ჩვენ გავაკეთეთ საცავი არქივი, სადაც შეგიძლიათ შეინახოთ და მოგვიანებით იპოვოთ თქვენი გეგმები დაკავშირებულ მოთხოვნებთან ერთად ან გაუზიაროთ ბმული ვინმეს.

თუ თქვენ უბრალოდ გჭირდებათ წაუკითხავი შეკითხვა ადეკვატურ ფორმაში მოყვანა, გამოიყენეთ ჩვენი "ნორმალიზატორი".

წყარო: www.habr.com