რაზე EXPLAIN დუმს და როგორ უნდა ახსნას

კლასიკური კითხვა, რომელსაც დეველოპერი უსვამს თავის DBA-ს ან ბიზნესის მფლობელს, უსვამს PostgreSQL კონსულტანტს, თითქმის ყოველთვის ერთნაირად ჟღერს: "რატომ სჭირდება მოთხოვნების დასრულებას მონაცემთა ბაზაში ამდენი დრო?"

მიზეზების ტრადიციული ნაკრები:

• არაეფექტური ალგორითმი
  როდესაც გადაწყვეტთ შეუერთდეთ რამდენიმე CTE-ს რამდენიმე ათიათასობით ჩანაწერზე
• მოძველებული სტატისტიკა
  თუ ცხრილში მონაცემების რეალური განაწილება უკვე ძალიან განსხვავდება ANALYZE-ის მიერ წინა ჯერზე შეგროვებულიდან
• რესურსების "ჩართვა".
  და აღარ არის CPU-ს საკმარისი გამოთვლითი სიმძლავრე, მეხსიერების გიგაბაიტები მუდმივად იტუმბება, ან დისკი ვერ ახერხებს მონაცემთა ბაზის ყველა „სასურველს“
• დაბლოკვა კონკურენტული პროცესებიდან

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

მაგრამ, როგორც იმავე დოკუმენტაციაშია ნათქვამი,

„გეგმის გაგება ხელოვნებაა და მის დაუფლებას გარკვეული გამოცდილება სჭირდება...“

მაგრამ ამის გარეშე შეგიძლიათ, თუ იყენებთ სწორ ხელსაწყოს!

როგორ გამოიყურება, როგორც წესი, შეკითხვის გეგმა? Რაღაც მაგდაგვარი:

Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
  Index Cond: (relname = $1)
  Filter: (oid = $0)
  Buffers: shared hit=4
  InitPlan 1 (returns $0,$1)
    ->  Limit (actual time=0.019..0.020 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
                Filter: (relkind = 'r'::"char")
                Rows Removed by Filter: 5
                Buffers: shared hit=1

ან ასე:

"Append  (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
"  Buffers: shared hit=3"
"  CTE cl"
"    ->  Seq Scan on pg_class  (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
"          Buffers: shared hit=3"
"  ->  Limit  (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
"        Buffers: shared hit=1"
"        ->  CTE Scan on cl  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
"              Buffers: shared hit=1"
"  ->  Limit  (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
"        Buffers: shared hit=2"
"        ->  CTE Scan on cl cl_1  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
"              Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"

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

• ნაჩვენებია კვანძში ჯამი ქვეხის რესურსებით
  ანუ იმის გასაგებად, თუ რამდენი დრო დასჭირდა კონკრეტული კვანძის შესრულებას, ან ზუსტად რამდენმა წაიკითხა ცხრილიდან მიღებული მონაცემები დისკიდან, საჭიროა როგორმე გამოაკლოთ ერთი მეორეს.
• საჭიროა კვანძის დრო გამრავლება მარყუჟებით
  დიახ, გამოკლება არ არის ყველაზე რთული ოპერაცია, რომელიც უნდა გაკეთდეს "თავში" - ბოლოს და ბოლოს, შესრულების დრო მითითებულია საშუალოდ კვანძის ერთი შესრულებისთვის და შეიძლება იყოს ასობით მათგანი
• კარგად და ეს ყველაფერი ერთად გვიშლის პასუხის გაცემას მთავარ კითხვაზე – მაშ ვინ "ყველაზე სუსტი რგოლი"?

როდესაც ჩვენ ვცადეთ ამ ყველაფრის ახსნა რამდენიმე ასეული ჩვენი დეველოპერისთვის, მივხვდით, რომ გარედან ასე გამოიყურებოდა:

და ეს ნიშნავს, რომ ჩვენ გვჭირდება...

Tool

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

გეგმების ხილვადობა

ადვილია თუ არა გეგმის გაგება, როცა ის ასე გამოიყურება?

Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
  Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms

ნამდვილად არ არის.

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

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

ისე, ყველაზე რთული ვარიანტებისთვის ის ჩქარობს დახმარებას პროგრესის სქემა:

მაგალითად, არის საკმაოდ არა ტრივიალური სიტუაციები, როდესაც გეგმას შეიძლება ჰქონდეს ერთზე მეტი რეალური ფესვი:

სტრუქტურული მინიშნებები

კარგად, თუ გეგმის მთელი სტრუქტურა და მისი სუსტი მხარეები უკვე ასახულია და ჩანს, რატომ არ უნდა გამოვყოთ ისინი დეველოპერს და აუხსნათ ისინი „რუსულ ენაზე“?

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

სტრიქონი-სტრიქონი შეკითხვის პროფილირებელი

ახლა, თუ თავდაპირველ შეკითხვას გადაანაწილებთ გაანალიზებულ გეგმას, შეგიძლიათ ნახოთ რამდენი დრო დაიხარჯა თითოეულ ცალკეულ განცხადებაზე - დაახლოებით ასეთი:

... ან თუნდაც ასე:

პარამეტრების ჩანაცვლება მოთხოვნაში

თუ თქვენ "დაურთეთ" არა მხოლოდ მოთხოვნა გეგმას, არამედ მისი პარამეტრები ჟურნალის DETAIL ხაზიდან, შეგიძლიათ დამატებით დააკოპიროთ იგი ერთ-ერთ ვარიანტში:

• მოთხოვნაში ღირებულების ჩანაცვლებით
  თქვენს ბაზაზე პირდაპირი შესრულებისთვის და შემდგომი პროფილირებისთვის

  SELECT 'const', 'param'::text;

• მნიშვნელობის ჩანაცვლებით PREPARE/EXECUTE-ის საშუალებით
  გრაფიკის მუშაობის ემულაცია, როდესაც პარამეტრული ნაწილის იგნორირება შესაძლებელია - მაგალითად, დანაწევრებულ ცხრილებზე მუშაობისას

  DEALLOCATE ALL;
  PREPARE q(text) AS SELECT 'const', $1::text;
  EXECUTE q('param'::text);
  

გეგმების არქივი

ჩასვით, გაანალიზეთ, გაუზიარეთ კოლეგებს! გეგმები დარჩება არქივში და შეგიძლიათ მოგვიანებით დაუბრუნდეთ მათ: განმარტება.tensor.ru/archive

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

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

წყარო: www.habr.com