การปรับปรุง 10 ปีของ Optimizer ใน PostgreSQL

 (rmarcus.info)
2 คะแนน โดย GN⁺ 2024-04-19 | 1 ความคิดเห็น | แชร์ทาง WhatsApp
  • เปรียบเทียบ latency ของคิวรีที่เปอร์เซ็นไทล์ที่ 90 ด้วย Join Order Benchmark ตั้งแต่ PostgreSQL 8 ถึง 16 และยืนยันเชิงประจักษ์ถึงการปรับปรุงประสิทธิภาพระยะยาวในส่วน tail
  • เมื่อเทียบกับ PostgreSQL 8 แล้ว PostgreSQL 16 มี tail latency ลดลงเกือบครึ่ง และช่วงเวอร์ชัน 13~16 โดยรวมคงอยู่ในระดับที่ค่อนข้างเสถียร
  • จากการวิเคราะห์ regression พบว่าเมื่อเวอร์ชันหลักเพิ่มขึ้นหนึ่งเวอร์ชัน จะมี การปรับปรุงประสิทธิภาพ 15% โดยเฉลี่ย แต่โมเดลเชิงเส้นอาจอธิบายรูปแบบการเปลี่ยนแปลงได้ไม่ดีนัก
  • การทดลองตรึงเงื่อนไขไว้ที่ GCC 13.2, Arch Linux Docker, shared_buffers 8GB, work_mem 8MB เพื่อโฟกัสที่ คุณภาพของ query optimizer
  • เมื่อตีความระดับการปรับปรุง ควรพิจารณาไม่เพียง optimizer แต่รวมถึงการเปลี่ยนแปลงของ execution engine เช่น parallel worker และ JIT compilation ด้วย

การตั้งค่า benchmark ของ PostgreSQL 8~16

  • เป้าหมายการวิเคราะห์คือเวอร์ชันหลักตั้งแต่ 8 ถึง 16 ของ PostgreSQL ซึ่งเป็น query optimizer แบบโอเพนซอร์ส
  • benchmark ใช้ Join Order Benchmark ซึ่งเป็นชุดคิวรีที่มี join ซับซ้อนจำนวนมาก
    • benchmark นี้ถูกนำเสนอในงานวิจัย “How Good are Query Optimizers, Really?”
  • PostgreSQL แต่ละเวอร์ชันถูก build ด้วย GCC 13.2 ภายในคอนเทนเนอร์ Arch Linux Docker
  • สภาพแวดล้อมการวัดถูกปรับให้เน้นดู คุณภาพของ query optimizer มากกว่าประสิทธิภาพของ index หรือ I/O
    • ตั้งค่า shared_buffers เป็น 8GB ซึ่งใหญ่พอที่จะบรรจุทั้ง database ได้
    • ตรึง work_mem ไว้ที่ 8MB ในทุกเวอร์ชัน
  • แต่ละคิวรีถูกรันหนึ่งครั้งเพื่ออุ่น cache จากนั้นบันทึก median latency ของการรันเพิ่มเติมอีก 5 ครั้ง
  • แต่ละเวอร์ชันหลักใช้เวอร์ชันย่อยล่าสุด
    • ตัวอย่างเช่น PostgreSQL 8 ใช้ 8.4.22 เป็นเป้าหมาย
    • โดยทั่วไปเวอร์ชันย่อยเหล่านี้ออกมาหลังจากเวอร์ชันหลักใหม่ แต่โดยปกติมีเพียง bug fix และไม่มีฟีเจอร์ใหม่หรือการปรับปรุงประสิทธิภาพ

ผลการวัดและการตีความ

  • tail performance ของ PostgreSQL โดยรวมดีขึ้นอย่างมาก
    • เมื่อเทียบ PostgreSQL 8 กับ 16 tail latency ลดลงเกือบครึ่ง
    • ตั้งแต่ PostgreSQL 13 ถึง 16 โดยรวมรักษาระดับที่ค่อนข้างเสถียร
  • ใช้ regression analysis เพื่อตรวจสอบว่าแนวโน้มขาลงระหว่างหมายเลขเวอร์ชันหลักกับ latency ของคิวรีมีนัยสำคัญหรือไม่ และเพื่อวัดระดับการปรับปรุงในแต่ละเวอร์ชันเป็นตัวเลข
    • ตาม linear regression พบว่าใน Join Order Benchmark มี การปรับปรุงประสิทธิภาพ 15% โดยเฉลี่ยต่อเวอร์ชันหลักใหม่แต่ละเวอร์ชัน
    • อย่างไรก็ตาม โมเดลเชิงเส้นอาจไม่เหมาะกับการวัดรูปแบบการเปลี่ยนแปลงจริง
  • เป็นเรื่องยากที่จะอธิบายการปรับปรุงทั้งหมดด้วย query optimizer เพียงอย่างเดียว
    • การปรับปรุง execution engine เช่น parallel worker และ JIT compilation ก็ส่งผลต่อประสิทธิภาพเช่นกัน
    • การที่ query plan แต่ละรายการใน JOB เปลี่ยนไปอย่างไรในแต่ละปียังคงเป็นหัวข้อสำหรับการวิเคราะห์แยกต่างหาก
  • หากอัปเกรดจาก PostgreSQL 8 ไปเป็น 16 มีความเป็นไปได้ที่ tail latency ของ workload จะลดลงอย่างมาก
  • ในการเปรียบเทียบงานวิจัย สิ่งสำคัญคือ PostgreSQL เองเป็น baseline ที่แข็งแกร่งขึ้นเรื่อย ๆ
    • Neo และ Bao เปรียบเทียบกับ PostgreSQL 11 แต่งานวิจัยที่ใหม่กว่านั้นเปรียบเทียบกับ PostgreSQL 14, 15, 16
    • แม้ว่าวิธีในอดีตจะปรับปรุงได้ 30% เมื่อเทียบกับ PostgreSQL และวิธีล่าสุดปรับปรุงได้ 25% แต่วิธีล่าสุดอาจกำลังถูกเทียบกับ PostgreSQL ที่แข็งแกร่งกว่า
  • สามารถดูค่าการวัดต้นฉบับได้ที่ raw data

บทความที่เกี่ยวข้อง

1 ความคิดเห็น

 
GN⁺ 2024-04-19
ความคิดเห็นจาก Hacker News

  • ผมใช้ Postgres มา 15 ปี และใช้เวลาส่วนใหญ่ในอาชีพไปกับการสร้างโมเดลและแก้ ปัญหาการปรับให้เหมาะสมเชิงคณิตศาสตร์ ผมมองว่าแก่นของหัวข้อนี้มีสามอย่าง
    ปัญหาการปรับให้เหมาะสมทุกอย่างต้องมีข้อมูลต้นทุน และยิ่งข้อมูลมากและดีเท่าไรก็ยิ่งดีขึ้นเท่านั้น Postgres มีการปรับปรุงอย่างสถิติข้ามคอลัมน์ แต่ยังมีช่องว่างใหญ่ ๆ เหลืออยู่ เช่น เวลาแฝงของ system call เวลาแฝงในการอ่านเพจจากดิสก์แตกต่างกันมากในแต่ละระบบ แต่ Postgres ไม่ได้วัดสิ่งนี้โดยตรงและพึ่งพาค่าตั้งไว้ สถิติของ foreign key ก็ยังขาดอยู่ด้วย ดังนั้น join ที่ไล่ตาม foreign key ไม่ควรได้แผนที่แย่ แต่บางครั้งก็ยังเกิดขึ้นอยู่
    โดยเฉพาะกับคิวรีขนาดใหญ่และมีต้นทุนสูง จำเป็นต้องมีการวางแผนแบบเลื่อนเวลาออกไปหรือการวางแผนฉากทัศน์ทางเลือก ตอนนี้แผนถูกกำหนดก่อนเริ่มรัน แต่จำนวนแถวหรือค่าประมาณคาร์ดินาลิตีที่ได้จากช่วงต้นของการรันสามารถช่วยปรับปรุงแผนช่วงหลังได้มาก
    Machine learning ก็เป็นพื้นที่ที่ยังปรับปรุงได้ แต่ความพยายามที่ผมเห็นมาจนถึงตอนนี้ยังไม่น่าประทับใจ ควรใช้ machine learning กับ การค้นพบและการประมาณต้นทุน มากกว่าจะใช้กับตัวแผนเอง ควรสร้าง cost model ที่ดีขึ้น แล้วให้เอนจินปรับให้เหมาะสมใช้ข้อมูลนั้น

    • อยากฟังความเห็นเพิ่มเติม เช่น ผมแปลกใจที่ เวลาแฝงของ system call อยู่ในรายการอันดับต้น ๆ มุมมองทั่วไปในชุมชนฐานข้อมูลน่าจะเป็นว่า cost model โดยรวมค่อนข้างใช้ได้ แต่การประมาณคาร์ดินาลิตีนั้นแย่มากจริง ๆ
      ในเรื่องการวางแผนแบบเลื่อนเวลา/แผนทางเลือก ผมสงสัยว่า adaptive query execution เป็นวิธีที่สมเหตุสมผลหรือไม่ สามารถทำให้ข้อมูลช่วงต้นของการรันคิวรีส่งผลต่อแผนภายหลังได้ก็จริง แต่ผมกังวลว่าถ้าเลือก join แรก ๆ ผิด ซึ่งเกิดขึ้นได้บ่อย หากไม่มีอะไรอย่าง Yannakakis/SIPs ก็จะกู้กลับได้ยาก
      สำหรับ “machine learning สำหรับ query optimization” ผมมีอคติแน่นอน อย่างไรก็ตาม แนวทาง “machine learning สำหรับการวางแผน” ทั้งหมดที่ผมเห็น สุดท้ายภายในก็ใช้ machine learning กับการค้นพบ/ประมาณต้นทุนอยู่ดี แนวทางเหล่านี้พยายามหาสมดุลระหว่างข้อมูลที่เก็บรวบรวม หรือก็คือการสำรวจ กับคุณภาพของแผนที่สร้างขึ้น หรือก็คือการใช้ประโยชน์ สิ่งที่น่าสนใจคือ ถ้าใช้ machine learning ในแบบที่แยกขาดจากการวางแผนโดยสิ้นเชิง แม้การประมาณจะแม่นขึ้น แต่แผนคิวรีจริงกลับแย่ลง: https://people.csail.mit.edu/tatbul/publications/flowloss_vl...
      ผมมีส่วนได้ส่วนเสียในสาขานี้ ดังนั้นควรพิจารณาความเห็นผมโดยคำนึงถึงเรื่องนั้นด้วย
    • แผนทางเลือก ฟังดูดีมากจริง ๆ ไม่นานมานี้ผมเห็นแผนคิวรีที่คาดว่าซับคิวรีหนึ่งจะได้ประมาณ 1,000 แถว จึงใช้ nested loop ต่อกับ index scan แต่จริง ๆ แล้วมีประมาณ 1 พันล้านแถว
      ยังไม่รู้ว่าทำไมการประมาณถึงผิดขนาดนั้น แต่ถ้าพอจำนวนแถวเกิน threshold บางอย่างแล้วสามารถเปลี่ยนจาก nested loop ไปเป็น hash join ได้ ก็น่าจะช่วยหลีกเลี่ยงแผนหายนะได้มาก
    • อยากรู้ว่าที่บอกว่าสถิติของ foreign key ขาดอยู่ หมายความว่าอย่างไรกันแน่ Postgres ก็เหมือนฐานข้อมูลเชิงสัมพันธ์จำนวนมากที่ไม่ได้สร้าง ดัชนีบน foreign key ให้อัตโนมัติ ซึ่งคิดว่าน่าจะรู้อยู่แล้ว
      หมายถึงปัญหาเรื่องลำดับ join หรือเปล่า?
    • อยากรู้ว่ามองว่า MSSQL ดีกว่าในส่วนนี้หรือไม่

  • ตัวปรับให้เหมาะสมคิวรีของ Postgres พยายามลดจำนวนเพจที่อ่านจากดิสก์และจำนวนเพจที่เขียนลงดิสก์เป็นผลลัพธ์กลาง ดังนั้นการตั้ง shared buffers ให้ใหญ่พอเก็บข้อมูลทั้งหมดแล้ว benchmark ตัวปรับให้เหมาะสมคิวรีจึงดูเหมือนเป็นวิธีที่ผิด
    แบบนั้นจะเป็นการวัดความเร็วของตัวปรับให้เหมาะสมคิวรีและตัวประมวลผล join ไม่ใช่คุณภาพของแผนคิวรีที่สร้างขึ้น ในความเป็นจริงก็ไม่น่าแปลกใจหากแผนที่แต่ละเวอร์ชันสร้างออกมาทั้งหมดเหมือนกัน และวัดแค่ความเร็วในการรันเท่านั้น

    • ไม่ใช่แบบนั้น เป้าหมายของการปรับให้เหมาะสมคือต้นทุนที่รวมสิ่งต่าง ๆ เช่น การใช้ CPU ด้วย ไม่ใช่แค่เพจที่อ่านจากดิสก์
      ต้นทุนเป็นหน่วยสมมติที่ออกแบบให้สัมพันธ์กับเวลาที่ใช้ ไม่ใช่จำนวนครั้งของการอ่านดิสก์ ดังนั้นการเปรียบเทียบแผนในสภาพที่ทุกอย่างอยู่ใน RAM ก็สมเหตุสมผลพอสมควร ตามธรรมเนียมจะสเกลการอ่านหนึ่งเพจจากดิสก์เป็น 1.0 แต่นั่นไม่เหมือนกับการพูดว่า “ตัวปรับให้เหมาะสมลดจำนวนการอ่านเพจดิสก์ให้น้อยที่สุด” จะตั้งให้ 1ms บนเครื่องใด ๆ เป็น 1.0 ก็ยังได้
    • เป็นไปได้แน่นอนว่าแผนคล้ายกันและสิ่งที่วัดคือการปรับปรุงของ execution engine Join Order Benchmark ถูกออกแบบมาเพื่อทดสอบคุณภาพของตัวปรับให้เหมาะสม
      ตัวปรับให้เหมาะสมของ PG ไม่ได้พยายามลดแค่จำนวนเพจที่อ่านจากดิสก์ แต่ยังลดจำนวน tuple ที่ CPU ต้องตรวจสอบ จำนวนครั้งที่ประเมินเงื่อนไข ฯลฯ ด้วย และตัวเลขเหล่านี้ทั้งหมดถูกนำมารวมเป็น “ต้นทุน” ซึ่งเป็นฟังก์ชันที่ตัวปรับให้เหมาะสมพยายามทำให้ต่ำสุด
      การวัดประสิทธิภาพแบบ cold cache กับ warm cache อาจให้ผลลัพธ์ต่างกัน และการทดลองนี้เป็นสถานการณ์ warm cache อย่างชัดเจน แต่ cold cache ก็มีปัญหาที่กล่าวถึงเช่นกัน สำหรับขนาดข้อมูลของ Join Order Benchmark การปรับปรุง B-tree ของ PG ที่ช่วยประหยัด I/O ได้ไม่กี่ครั้งอาจมีผลเหนือกว่าการปรับปรุงที่อิง CPU
      เพื่อเป็นข้อมูลอ้างอิง แผนของคิวรีที่มีเวลาแฝง P90 เปลี่ยนจากแผนที่ใช้ loop join และ merge join ใน PG 8.4 มาเป็นแผนที่ใช้ hash join ใน PG 16 และคิวรีนี้ไม่ใช่คิวรี P90 อีกต่อไป อย่างน้อยสิ่งนี้ก็นับเป็นหลักฐานบางส่วนของการปรับปรุงตัวปรับให้เหมาะสมได้

  • บทความพูดถึง JIT compiler ของ PostgreSQL แต่จนถึงตอนนี้ผมเห็นแต่ว่ามันทำให้ประสิทธิภาพคิวรีแย่ลง ผมใส่การปิดใช้งานมันไว้ใน checklist ตอนติดตั้ง

    • ลูกค้ารายหนึ่งเจอปัญหาประสิทธิภาพแย่ที่สุดหลังย้ายมาใช้ Postgres แปลกตรงที่เกิดเฉพาะใน Docker และการตั้งค่าเซิร์ฟเวอร์ทดสอบ แต่ไม่เกิดบนเครื่องของนักพัฒนา นักพัฒนาใช้ Postgres ผ่าน Homebrew
      ปรากฏว่า Homebrew ติดตั้ง Postgres โดยไม่มีการรองรับ JIT และบนเครื่องนักพัฒนาคิวรีหนึ่งจบใน 200ms แต่ในสภาพแวดล้อมที่เปิด JIT กลับใช้เวลา 4–5 วินาที ผมไม่ได้ใช้ Postgres แบบลึกมาก จึงใช้เวลาพอสมควรกว่าจะหาสาเหตุเจอ และหลังจากนั้นก็ปิด JIT เสมอและไม่หันกลับไปอีก
    • JIT compiler ยอดเยี่ยมสำหรับ คิวรีเชิงวิเคราะห์
      ใน PostgreSQL สามารถตั้งค่า threshold สำหรับการเปิดใช้ JIT ได้ด้วย ดังนั้นจึงตั้งเกณฑ์ให้ JIT เริ่มทำงานสูงขึ้นได้
    • JIT ของ pg แสดงให้เห็นค่อนข้างชัดว่า LLVM ไม่ได้เหมาะกับ JIT เท่าไรนัก และยิ่งแย่ลงเพราะ Postgres ไม่มี shared query cache แบบคงอยู่ต่อเนื่อง
      ถ้าสามารถคอมไพล์แบบ async สำหรับคิวรีในอนาคตได้ ก็น่าจะเป็นโทษน้อยลง อันที่จริง JIT ทั่วไป โดยเฉพาะ optimization backend ก็ใกล้เคียงกับวิธีนั้นมากกว่า
    • Postgres ไม่สามารถ JIT compile คิวรีหนึ่งครั้ง แล้วรันคิวรีที่คอมไพล์แล้วหลาย ๆ ครั้งได้หรือ?

  • น่าสนใจ แต่ระบบเลขเวอร์ชันของ Postgres เปลี่ยนไปตั้งแต่ v10 เป็นต้นมา 9.6, 9.5, 9.4, 9.3, 9.2, 9.1, 9.0, 8.4, 8.3, 8.2, 8.1, 8.0 จริง ๆ แล้วล้วนเป็น เมเจอร์เวอร์ชัน แยกกันทั้งหมด
    ถ้าได้ดูด้วยว่าประสิทธิภาพเปลี่ยนไปอย่างไรในเวอร์ชันเหล่านั้นก็น่าจะน่าสนใจ

    • ถึงอย่างนั้น ผมก็ขอบคุณที่ตั้งแต่ v9.0 ถึง 9.6 ยังรักษา ความเข้ากันได้ของไฟล์ซิสเต็มระหว่างเมเจอร์เวอร์ชัน ไว้ ทำให้สามารถอัปเกรดแบบ in-place ได้เร็วขึ้นด้วยการเปลี่ยนแค่ไบนารี
      นั่นอาจทำให้ถูกจำกัดอยู่บ้าง แต่การอัปเดตรายปีที่ต้องมี downtime มากขึ้นหรือต้อง reindex ก็ไม่ใช่เรื่องสนุกนัก และอาจเป็นเหตุผลที่หลายไซต์เลื่อนการอัปเกรดออกไปจนกว่าการซัพพอร์ตเวอร์ชันเก่าจะสิ้นสุด โดยเฉพาะผู้ใช้ AWS RDS
      การอัปเกรดด้วย logical replication หลัง v10 มีข้อดีด้าน availability แต่ถ้า schema ไม่ได้ค่อนข้างเรียบง่าย ก็เป็นโปรเจกต์ใหญ่ที่มีต้นทุนที่หลีกเลี่ยงไม่ได้และความเสี่ยงสูง
    • เห็นด้วยอย่างยิ่ง ผมตีความเลขเวอร์ชันแบบ semver แล้วเลือกเวอร์ชันล่าสุดของแต่ละเมเจอร์เวอร์ชัน ซึ่งต่างจากวิธีที่ PostgreSQL จัดการเลขเมเจอร์เวอร์ชันตามธรรมเนียม
      เช่น PG 8.2 กับ 8.1 เป็นเมเจอร์เวอร์ชันคนละตัวกัน แต่ผมตีความเหมือนเป็นไมเนอร์เวอร์ชัน เหตุผลหลักที่ทำแบบนี้คือเพื่อลดจำนวนเวอร์ชันที่ต้องทดสอบ และผมเห็นด้วยว่าการวิเคราะห์ที่สมบูรณ์กว่านี้ควรทดสอบเมเจอร์เวอร์ชันจริงแต่ละตัว

  • ที่บอกว่า “แน่นอนว่าการปรับปรุงเหล่านี้ไม่ได้มาจาก query optimizer ทั้งหมด” ถ้าได้ดูว่ามี การเปลี่ยนแปลง execution plan ในแต่ละเวอร์ชันหรือไม่น่าจะน่าสนใจ

  • นึกถึงกฎของ Proebsting: https://proebsting.cs.arizona.edu/law.html

    • ข้อดีของการปรับแต่ง compiler optimization คือสามารถเพิ่มประสิทธิภาพได้โดยไม่ต้องไปแตะ CPU เดิมในเชิงกายภาพ ทุกปีจะมีคนเค้นประสิทธิภาพเพิ่มขึ้นจากเครื่องที่ใครบางคนออกแบบไว้ และเมื่อสะสมไปเรื่อย ๆ ก็ใหญ่ขึ้น
      ลองจินตนาการดูว่าถ้า optimize ประสิทธิภาพของ Python ได้ 1% จะส่งผลต่อสิ่งแวดล้อมแค่ไหน จะลด CO2 ในชั้นบรรยากาศได้เท่าไร? น่าจะมากกว่ารอยเท้าทางสิ่งแวดล้อมของตัวคุณเอง ครอบครัว และเพื่อน ๆ รวมกันเสียอีก บางทีอาจเทียบได้กับทั้งเมืองที่คุณอาศัยอยู่เลยด้วยซ้ำ ทั้งหมดนั้นเพียงเพราะมีใครบางคนใช้เวลา implement ทริก bit operation ไม่กี่อย่าง
    • ไม่รู้ว่าทำไมถึงเป็นแบบนั้น กฎนั้นดูเหมือนจะบอกว่าการปรับปรุงประสิทธิภาพซอฟต์แวร์ไม่ได้มีความหมายมากนัก แต่บทความนี้พูดว่าการปรับปรุงของ Postgres มีนัยสำคัญมาก
      เป็นเพราะมองว่า 15% เป็นตัวเลขต่ำหรือเปล่า? ในบริบทนี้ไม่ได้ต่ำเลย แม้มันจะน้อยกว่า 60% ในกฎที่ลิงก์มา และถ้าแบ่งแบบ 15/10 ก็จะยิ่งเล็กลง แต่ไม่ควรเอาประสิทธิภาพของ Postgres ไปเทียบกับการพัฒนาฮาร์ดแวร์ การเพิ่มประสิทธิภาพ 1% ในสิ่งที่วัดอยู่นี้ต้องใช้ การปรับปรุงฮาร์ดแวร์มหาศาล ถึงจะเทียบเท่า
      ผมไม่คิดว่ากฎนั้นตลกอย่างที่หลายคนพูดกัน แต่มันเป็นเรื่องเวลาคอมไพล์ของภาษาโปรแกรม ผมจะไม่เอาสิ่งที่ค่อนข้างไม่สำคัญแบบนั้นไปเทียบกับการจัดเก็บและการใช้ข้อมูล ซึ่งพูดได้ว่าเป็นหนึ่งในเรื่องสำคัญที่สุดของวิทยาการคอมพิวเตอร์
    • ในกรณีนี้ ผู้วิจัย build PostgreSQL ทุกเวอร์ชันด้วย GCC 13.2 เดียวกัน และทดสอบบนระบบปฏิบัติการเดียวกัน
    • ดูเหมือนเป็น “กฎ” ที่ค่อนข้างอ่อนมาก สร้างขึ้นมาเป็นมุกหรือเปล่า? ตัวเลขที่ใช้เป็นหลักฐานก็ไม่รู้มาจากไหน ระดับ “สมมติว่า” และข้อสรุปก็คลาดเคลื่อนไปมาก ดูเหมือนกำลังบอกเป็นนัยว่า optimization ที่ช่วยเพิ่มประสิทธิภาพของซอฟต์แวร์จำนวนมากทั่วโลกปีละ 4% เป็นการเสียเวลา
      สิ่งที่ยกมาเปรียบเทียบมีแค่กฎของ Murphy เท่านั้น อยากรู้ว่าต้นทุนในการพัฒนาฮาร์ดแวร์ที่เร็วขึ้นกับต้นทุนในการปรับปรุง compiler อย่างต่อเนื่องต่างกันแค่ไหน หากเทียบ ROI ในรูปแบบดอลลาร์ต่อเปอร์เซ็นต์ประสิทธิภาพที่เพิ่มขึ้น “กฎ” นี้ก็อาจมีน้ำหนักอยู่บ้างขึ้นกับผลเทียบ
      ในทางกลับกัน บทความ Postgres นี้ดูเหมือนจะแสดง ผลตอบแทนที่ลดลง จาก optimization ซึ่งหักล้างสมมติฐานของ “กฎ” นั้นที่ถือว่าผลได้เท่ากันทุกปี พร้อมกันนั้นก็อาจยืนยันนัยของ Proebsting ที่ว่าในระยะยาว optimization เป็นการลงทุนที่ไม่ดีได้ด้วย

  • การวิเคราะห์นี้ชวนสับสนนิดหน่อย ไม่แน่ใจว่าเขายืนยันแนวโน้มขาลงจากข้อมูลได้อย่างไร ทั้งที่กราฟไม่เห็นแนวโน้มนั้น
    ค่ามัธยฐานดูเหมือนจะลดลงเล็กน้อยในเวอร์ชันแรก ๆ แล้วกลับเพิ่มขึ้นอีกในเวอร์ชันล่าสุด ๆ ค่า R² ต่ำมาก จนความสัมพันธ์ดูไม่น่าเชื่อถือ โดยพื้นฐานแล้วดูเหมือน tail latency ดีขึ้น ส่วนที่เหลือขึ้นอยู่กับสภาพแวดล้อม

    • ผมเป็นผู้เขียนบล็อก
      การตีความว่า “tail latency ดีขึ้น ส่วนที่เหลือขึ้นอยู่กับสภาพแวดล้อม” นั้นสมเหตุสมผล แต่ผมมองว่าเป็นการอ่านแบบระมัดระวัง แน่นอนว่าในหลาย ๆ แอปพลิเคชัน หรืออาจเป็นส่วนใหญ่ tail latency สำคัญมาก อีกทั้ง tail latency ยังเป็นสิ่งที่วิศวกร optimizer มักเล็งเป้าอยู่แล้ว นั่นคือการลดเวลารันของ query ที่ใช้เวลานานที่สุด

  • การเพิ่มประสิทธิภาพคิวรีมีหน้าตาอย่างไร? สงสัยว่าเป็นการปรับให้เหมาะสมในระดับ SQL หรือเป็นการปรับให้เหมาะสมในระดับอัลกอริทึม

    • ในฐานข้อมูลที่ผมเคยใช้ส่วนใหญ่ (ยกเว้น PostgreSQL) การปรับให้เหมาะสมส่วนมากเกิดขึ้นในระดับอัลกอริทึม กล่าวคือเป็นการเลือกอัลกอริทึมที่เหมาะที่สุดและลำดับการทำงานสำหรับคิวรีหนึ่ง ๆ
      ดูเหมือนว่าเป็นเพราะคิวรี SQL หลายแบบที่ต่างกันสามารถถูกแปลงเป็น “คำสั่ง” หรือแผนการรันเดียวกันได้ และตัว semantics ของ SQL เองก็ไม่ได้เปิดช่องให้ปรับให้เหมาะสมในระดับภาษามากนัก
      อย่างที่มีคนพูดไว้ในคอมเมนต์อื่น หนึ่งในการตัดสินใจสำคัญคือสามารถเปลี่ยนการสแกนทั้งตารางให้เป็นการค้นผ่านอินเด็กซ์หรือการสแกนอินเด็กซ์ได้หรือไม่
      ตัวอย่างเช่น หากจำเป็นต้องสแกนทั้งตาราง และต้องคำนวณค่อนข้างมากสำหรับแต่ละแถวเพื่อตัดสินว่าจะรวมไว้ในชุดผลลัพธ์หรือไม่ ตัวปรับแผนให้เหมาะสมอาจเปลี่ยนการสแกนทั้งตารางให้เป็นการสแกนตารางแบบขนาน แล้วรวมผลลัพธ์จากงานขนานแต่ละงานได้
      เวลาเขียนโค้ดประสิทธิภาพสูงสำหรับคอมไพเลอร์ คุณต้องรู้ว่าตัวปรับให้เหมาะสมของคอมไพเลอร์จะแปลงซอร์สโค้ดเป็นภาษาเครื่องอย่างไร เพื่อจะได้เลือกเขียนโค้ดที่ตัวปรับให้เหมาะสมจัดการได้ดี และหลีกเลี่ยงแพตเทิร์นที่ทำให้ได้ภาษาเครื่องที่ช้ากว่า ท้ายที่สุด ตัวปรับให้เหมาะสมก็ถูกโปรแกรมมาให้ตรวจจับและแปลงแพตเทิร์นเฉพาะ
      ตัวปรับให้เหมาะสมคิวรีและแผนการรันก็เช่นเดียวกัน คุณต้องเรียนรู้ว่าตัวปรับให้เหมาะสมคิวรีของฐานข้อมูลที่คุณใช้สามารถจัดการแพตเทิร์นใดได้บ้างเพื่อสร้างแผนการรันที่มีประสิทธิภาพ
    • อธิบายวิธีทั้งหมดที่สามารถรัน SQL ได้ แล้วเลือกแผนที่เร็วกว่า ตัวอย่างเช่น ถ้าจะหาแถวผู้ใช้ที่มี user_id เป็น xx ก็เป็นการเลือกระหว่างอ่านทั้งตารางแล้วกรอง หรือใช้โครงสร้างข้อมูลเฉพาะ
      ถ้าใช้อินเด็กซ์ จะหาได้ในเวลาเชิงลอการิทึมตามจำนวนแถว นอกจากนี้ยังทำได้อีกมาก เช่น เลือกลำดับการ join เลือกกลยุทธ์การ join และผลักเงื่อนไขการกรองลงไปยังฝั่งต้นทาง เป็นต้น นี่คือขอบเขตกว้าง ๆ ของการเพิ่มประสิทธิภาพ SQL
    • มองในระดับสูงมาก ๆ เป้าหมายของ query planner คือทำให้ต้นทุนการอ่านข้อมูลจากดิสก์ต่ำที่สุด โดยรวบรวมสถิติคอลัมน์ที่คำนวณไว้ล่วงหน้า เช่น จำนวนแถวและจำนวนค่าที่ไม่ซ้ำ เพื่อประมาณจำนวนแถวที่คิวรีจะ match
      ใช้ข้อมูลนี้ในการกำหนดลำดับการ join การเลือกอินเด็กซ์ และอื่น ๆ การ join สามารถทำได้ด้วยอัลกอริทึมหลายแบบ เช่น hash, loop, merge ตัวเลือกที่ถูกที่สุดขึ้นอยู่กับปัจจัยต่าง ๆ เช่น ฝั่งหนึ่งใส่ลงใน working memory ได้หรือไม่ ทั้งสองฝั่งถูก sort ไว้แล้วหรือไม่ เช่น เพราะมีการสแกนอินเด็กซ์ เป็นต้น
    • การเพิ่มประสิทธิภาพคิวรีคือการเลือกอัลกอริทึมที่จะให้ผลลัพธ์ตามที่ SQL ขอ

  • ดูเหมือนว่าไซต์จะล่มไปแล้ว ดังนั้นดูอันนี้แทนได้: https://web.archive.org/web/20240417050840/https://rmarcus.i...