🥇การทำความเข้าใจแผนการสืบค้น PostgreSQL สะดวกยิ่งขึ้น

ครึ่งปีที่แล้ว เรานำเสนอ exp.tensor.ru - สาธารณะ บริการสำหรับการแยกวิเคราะห์และการแสดงภาพแผนการสืบค้น สู่ PostgreSQL

ตลอดหลายเดือนที่ผ่านมาเราได้พูดถึงเขา รายงานที่ PGConf.Russia 2020ได้เตรียมเรื่องย่อไว้แล้ว บทความเกี่ยวกับการเร่งความเร็วแบบสอบถาม SQL ตามคำแนะนำที่ให้ไว้... แต่ที่สำคัญที่สุด เรารวบรวมคำติชมของคุณและพิจารณากรณีการใช้งานจริง

และตอนนี้เราพร้อมแล้วที่จะพูดถึงโอกาสใหม่ๆ ที่คุณสามารถใช้ได้

รองรับรูปแบบแผนต่างๆ

วางแผนจากบันทึกพร้อมกับคำขอ

โดยตรงจากคอนโซล เลือกบล็อกทั้งหมด โดยเริ่มจากบรรทัดด้วย ข้อความสอบถามโดยมีช่องว่างนำหน้าทั้งหมด:

        Query Text: INSERT INTO  dicquery_20200604  VALUES ($1.*) ON CONFLICT (query)
                           DO NOTHING;
        Insert on dicquery_20200604  (cost=0.00..0.05 rows=1 width=52) (actual time=40.376..40.376 rows=0 loops=1)
          Conflict Resolution: NOTHING
          Conflict Arbiter Indexes: dicquery_20200604_pkey
          Tuples Inserted: 1
          Conflicting Tuples: 0
          Buffers: shared hit=9 read=1 dirtied=1
          ->  Result  (cost=0.00..0.05 rows=1 width=52) (actual time=0.001..0.001 rows=1 loops=1)

... และนำทุกอย่างที่คัดลอกมาลงในช่องแผนโดยตรง โดยไม่แยกสิ่งใดออก:

ในตอนท้ายเราได้รับโบนัสสำหรับแผนการแยกชิ้นส่วนและ แท็บ "บริบท"ซึ่งคำขอของเราได้รับการนำเสนออย่างสง่างาม:

JSON และ YAML

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM pg_class;

"[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Relation Name": "pg_class",
      "Alias": "pg_class",
      "Startup Cost": 0.00,
      "Total Cost": 1336.20,
      "Plan Rows": 13804,
      "Plan Width": 539,
      "Actual Startup Time": 0.006,
      "Actual Total Time": 1.838,
      "Actual Rows": 10266,
      "Actual Loops": 1,
      "Shared Hit Blocks": 646,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 5.135,
    "Triggers": [
    ],
    "Execution Time": 2.389
  }
]"

ไม่ว่าจะใช้เครื่องหมายคำพูดภายนอกในขณะที่ pgAdmin คัดลอกหรือไม่มี - เราโยนมันลงในฟิลด์เดียวกันและผลลัพธ์ก็คือความสวยงาม:

การแสดงภาพขั้นสูง

เวลาการวางแผน/เวลาดำเนินการ

ตอนนี้คุณสามารถดูได้ดีขึ้นว่าใช้เวลาพิเศษไปกับการดำเนินการค้นหาตรงไหน:

กำหนดเวลา I/O

บางครั้งคุณต้องจัดการกับสถานการณ์ที่ดูเหมือนว่าไม่ได้อ่านและเขียนมากเกินไปในแง่ของทรัพยากร แต่เวลาดำเนินการดูเหมือนจะยาวไม่สอดคล้องกัน

ที่นี่เราต้องพูดว่า: "โอ้ ในขณะนั้นดิสก์บนเซิร์ฟเวอร์อาจโอเวอร์โหลดเกินไป นั่นเป็นเหตุผลว่าทำไมจึงใช้เวลานานในการอ่าน!“แต่ถึงอย่างนั้นมันก็ไม่ถูกต้องมากนัก...

แต่สามารถกำหนดได้อย่างน่าเชื่อถืออย่างแน่นอน ความจริงก็คือมีตัวเลือกการกำหนดค่าเซิร์ฟเวอร์ PG อยู่ track_io_timing:

เปิดใช้งานการกำหนดเวลาของการดำเนินการ I/O ตัวเลือกนี้ถูกปิดใช้งานตามค่าเริ่มต้น เนื่องจากต้องมีการสอบถามระบบปฏิบัติการอย่างต่อเนื่องในช่วงเวลาปัจจุบัน ซึ่งอาจส่งผลให้ประสิทธิภาพการทำงานช้าลงอย่างมากในบางแพลตฟอร์ม หากต้องการประมาณต้นทุนการจับเวลาบนแพลตฟอร์มของคุณ คุณสามารถใช้ยูทิลิตี pg_test_timing สามารถรับสถิติ I/O ได้ผ่านมุมมอง pg_stat_database ในเอาต์พุต EXPLAIN (เมื่อใช้พารามิเตอร์ BUFFERS) และผ่านมุมมอง pg_stat_statements

ตัวเลือกนี้สามารถเปิดใช้งานได้ภายในเซสชันท้องถิ่น:

SET track_io_timing = TRUE;

ตอนนี้ส่วนที่ดีที่สุดคือเราได้เรียนรู้ที่จะเข้าใจและแสดงข้อมูลนี้โดยคำนึงถึงการเปลี่ยนแปลงทั้งหมดของแผนผังการดำเนินการ:

คุณจะเห็นได้ว่าจากเวลาดำเนินการทั้งหมด 0.790 มิลลิวินาที ใช้เวลา 0.718 มิลลิวินาทีในการอ่านหน้าข้อมูล 0.044 มิลลิวินาทีในการเขียน และใช้เวลาเพียง 0.028 มิลลิวินาทีกับกิจกรรมที่มีประโยชน์อื่นๆ ทั้งหมด!

อนาคตด้วย PostgreSQL 13

คุณสามารถดูภาพรวมของนวัตกรรมทั้งหมดได้ ในบทความโดยละเอียดและเรากำลังพูดถึงการเปลี่ยนแปลงแผนโดยเฉพาะ

บัฟเฟอร์การวางแผน

การบัญชีสำหรับทรัพยากรที่จัดสรรให้กับตัวกำหนดเวลาจะสะท้อนให้เห็นในแพตช์อื่นที่ไม่เกี่ยวข้องกับ pg_stat_statements อธิบายด้วยตัวเลือก BUFFERS จะรายงานจำนวนบัฟเฟอร์ที่ใช้ในระหว่างขั้นตอนการวางแผน:
 Seq Scan on pg_class (actual rows=386 loops=1)
   Buffers: shared hit=9 read=4
 Planning Time: 0.782 ms
   Buffers: shared hit=103 read=11
 Execution Time: 0.219 ms

การเรียงลำดับที่เพิ่มขึ้น

ในกรณีที่จำเป็นต้องเรียงลำดับคีย์จำนวนมาก (k1, k2, k3...) ผู้วางแผนสามารถใช้ประโยชน์จากความรู้ที่ว่าข้อมูลถูกจัดเรียงแล้วในคีย์แรกหลายคีย์แล้ว (เช่น k1 และ k2) ในกรณีนี้คุณไม่สามารถจัดเรียงข้อมูลทั้งหมดอีกครั้งได้ แต่แบ่งออกเป็นกลุ่มต่อเนื่องโดยมีค่า k1 และ k2 เท่ากันและ "จัดเรียงใหม่" ด้วยคีย์ k3

ดังนั้นการเรียงลำดับทั้งหมดจึงแบ่งออกเป็นขนาดที่เล็กกว่าหลาย ๆ ประเภทติดต่อกัน ซึ่งจะช่วยลดจำนวนหน่วยความจำที่ต้องการและยังช่วยให้ข้อมูลแรกถูกส่งออกก่อนที่การเรียงลำดับทั้งหมดจะเสร็จสิ้น
 Incremental Sort (actual rows=2949857 loops=1)
   Sort Key: ticket_no, passenger_id
   Presorted Key: ticket_no
   Full-sort Groups: 92184 Sort Method: quicksort Memory: avg=31kB peak=31kB
   ->  Index Scan using tickets_pkey on tickets (actual rows=2949857 loops=1)
 Planning Time: 2.137 ms
 Execution Time: 2230.019 ms

การปรับปรุง UI/UX

ภาพหน้าจอ มันอยู่ทุกที่!

ตอนนี้ในแต่ละแท็บมีโอกาสที่จะได้อย่างรวดเร็ว ถ่ายภาพหน้าจอของแท็บไปยังคลิปบอร์ด ความกว้างและความลึกทั้งหมดของแท็บ - "สายตา" ที่ด้านขวาบน:

ที่จริงแล้ว รูปภาพส่วนใหญ่สำหรับสิ่งพิมพ์นี้ได้มาในลักษณะนี้

คำแนะนำเกี่ยวกับโหนด

ไม่เพียงแต่มีมากขึ้นเท่านั้น แต่คุณยังสามารถพูดคุยเกี่ยวกับแต่ละเรื่องได้อีกด้วย อ่านบทความโดยละเอียดโดยไปตามลิงค์:

กำลังลบออกจากที่เก็บถาวร

บางคนขอให้เพิ่มตัวเลือกนี้จริงๆ ลบ "สมบูรณ์" แม้แต่แผนที่ไม่ได้เผยแพร่ในไฟล์เก็บถาวร - โปรดคลิกไอคอนที่เหมาะสม:

อย่าลืมว่าเราก็มี กลุ่มสนับสนุนซึ่งคุณสามารถเขียนความคิดเห็นและข้อเสนอแนะของคุณได้

ที่มา: will.com

ทำความเข้าใจแผนการสืบค้น PostgreSQL ได้สะดวกยิ่งขึ้น