ฟีเจอร์ใหม่ใน Query Planner ของ Postgres 16

 (citusdata.com)
1 คะแนน โดย GN⁺ 2024-02-11 | 1 ความคิดเห็น | แชร์ทาง WhatsApp
  • PostgreSQL 16 เพิ่มการปรับปรุง 10 รายการให้กับ query planner/optimizer ทำให้มีตัวเลือกแผนการรันที่กว้างขึ้นสำหรับคิวรี DISTINCT, aggregation, join, window function และคิวรีบน partitioned table
  • ใน SELECT DISTINCT, aggregation ที่มี ORDER BY/DISTINCT และการประมวลผลหลัง Merge Join สามารถใช้ประโยชน์จาก อินพุตที่ถูกจัดเรียงบางส่วน ได้เชิงรุกมากขึ้น เพื่อสร้างผลลัพธ์โดยใช้หน่วยความจำน้อยกว่าการ sort ทั้งหมด
  • การรองรับ Memoize ภายใน UNION ALL, Right Anti Join และ parallel hash join สำหรับ FULL/RIGHT join มุ่งลดต้นทุนของการค้นหาซ้ำและการสร้าง hash table ขนาดใหญ่
  • Window function ลดการประมวลผล RANGE ที่ไม่จำเป็นและลด WindowAgg ที่ต้องรันจนจบ โดยบางฟังก์ชันสามารถ หยุดก่อนเวลา ได้ตามเงื่อนไข
  • การปรับปรุงทั้งหมดเปิดใช้งานเป็นค่าเริ่มต้น จึงคุ้มค่าที่จะเปรียบเทียบ EXPLAIN และเวลารันของ workload จริงก่อนและหลังอัปเกรดเป็น PostgreSQL 16

ขอบเขตของการปรับปรุง planner ใน PostgreSQL 16

  • PostgreSQL 16 นำการปรับปรุงหลายอย่างมาสู่ query planner ทำให้คิวรี SQL จำนวนมากรันได้เร็วกว่า PostgreSQL เวอร์ชันก่อนหน้า
  • อธิบายรายละเอียดเพิ่มเติมเกี่ยวกับการปรับปรุง planner ที่อยู่ใน บันทึกรีลีส PG16 พร้อมเปรียบเทียบเอาต์พุต EXPLAIN ระหว่าง PG15 กับ PG16 และตัวอย่างทดสอบที่ทำซ้ำได้
  • planner ในที่นี้คือคอมโพเนนต์ที่ในฐานข้อมูลเชิงสัมพันธ์อื่น ๆ มักเรียกว่า optimizer

การปรับแต่ง sort และ DISTINCT

  • ใช้ Incremental Sort ใน SELECT DISTINCT

    • Incremental Sort ถูกเพิ่มครั้งแรกใน PostgreSQL 13 และลดต้นทุนด้วยการ sort เฉพาะคอลัมน์ที่เหลือ เมื่อผลลัพธ์ถูกจัดเรียงตามคอลัมน์นำหน้าอยู่แล้ว
    • planner ของ PostgreSQL 16 พิจารณา Incremental Sort สำหรับคิวรี SELECT DISTINCT ด้วย
    • เช่น หากมี btree index บนคอลัมน์ a และต้องการลำดับ a, b ก็สามารถใช้ index เพื่อได้ผลลัพธ์ที่จัดเรียงตาม a แล้วค่อย sort เฉพาะ b ทุกครั้งที่ค่า a เปลี่ยน
    • ใน quicksort ของ PostgreSQL การ sort กลุ่มเล็กหลายกลุ่มอาจมีประสิทธิภาพกว่าการ sort กลุ่มใหญ่กลุ่มเดียว
    • ในคิวรีตัวอย่าง PG15 ใช้ HashAggregate และ sequential scan ส่วน PG16 เลือก index distinct_test_a_idx และ Incremental Sort
    • Presorted Key: a ในเอาต์พุตของ PG16 หมายความว่าใช้อินพุตที่ถูกจัดเรียงตาม a อยู่แล้ว
    • วิธี hash ของ PG15 spill ลงดิสก์ประมาณ 30MB แต่หน่วยความจำสูงสุดของ Incremental Sort ใน PG16 อยู่ที่ 26KB
    • เวลารันลดจาก 414.226ms ใน PG15 เหลือ 263.167ms ใน PG16

  • ปรับแต่ง aggregation ที่มี ORDER BY หรือ DISTINCT

    • ใน PostgreSQL 15 หรือต่ำกว่า aggregate function ที่มี clause ORDER BY หรือ DISTINCT จะทำการ sort ภายในโหนด Aggregate เสมอ
    • planner ของ PostgreSQL 16 สามารถสร้างแผนการรันที่ป้อนแถวในลำดับที่ถูกต้องให้กับโหนด Aggregate ได้ และ executor จะข้ามการ sort ภายในหากอินพุตถูกจัดเรียงอยู่แล้ว
    • ในตัวอย่าง COUNT(DISTINCT b) ทั้ง PG15 และ PG16 ใช้ GroupAggregate และ Index Only Scan แต่เอาต์พุตของ PG15 แสดง temp read=4540 written=4560
    • I/O ของไฟล์ชั่วคราวนี้เป็นผลจาก implicit sort ของ PG15 ที่ spill ลงดิสก์
    • เอาต์พุตของ PG16 ไม่มี I/O ชั่วคราวดังกล่าว และเวลารันเร็วขึ้นมากกว่า 2 เท่า จาก 302.693ms ใน PG15 เป็น 115.534ms ใน PG16

การปรับปรุงการค้นหาซ้ำและแผน join

  • ใช้ Memoize ภายใน UNION ALL

    • plan node Memoize ถูกนำเข้ามาครั้งแรกใน PostgreSQL 14 และทำงานเหมือน ชั้นแคช ระหว่าง Nested Loop แบบ parameterized กับอินพุตฝั่งใน
    • planner ของ PostgreSQL 16 พิจารณาการใช้ Memoize แม้เมื่อมีคิวรี UNION ALL อยู่ภายในของ Nested Loop แบบ parameterized
    • ในตัวอย่าง PG15 รัน Append 1 ล้านครั้ง แต่ PG16 วาง Memoize ไว้เหนือ Append
    • Memoize ของ PG16 บันทึกค่า Hits: 999990, Misses: 10, Memory Usage: 2kB
    • จำนวนครั้งที่รัน Append ลดจาก 1 ล้านครั้งใน PG15 เหลือ 10 ครั้งใน PG16
    • เวลารันเร็วขึ้นประมาณ 6 เท่า จาก 1926.151ms ใน PG15 เป็น 282.120ms ใน PG16

  • รองรับ Right Anti Join

    • ใน Hash Join ของ INNER JOIN โดยทั่วไปการสร้าง hash table บนตารางที่เล็กกว่าจะได้เปรียบกว่า
    • hash table ขนาดเล็กสร้างงานน้อยกว่า เป็นมิตรกับ CPU cache มากกว่า และมีโอกาสเกิด CPU stall จากการรอข้อมูลในหน่วยความจำหลักน้อยกว่า
    • ก่อน PostgreSQL 16 Anti Join จะวางตารางที่กล่าวถึงใน NOT EXISTS ไว้เป็นฝั่งในของ join เสมอ จึงอาจต้องสร้าง hash table บนตารางที่ใหญ่กว่า
    • PostgreSQL 16 รองรับ Right Anti Join ทำให้สามารถ hash ฝั่งที่เล็กกว่าระหว่างสองตารางได้
    • ในตัวอย่าง PG15 hash ตาราง large ที่มี 1 ล้านแถวและใช้หน่วยความจำ 6446KB แต่ PG16 hash ตาราง small ที่มี 100 แถวและใช้เพียง 12KB
    • เวลารันลดลงเกือบครึ่ง จาก 139.023ms ใน PG15 เป็น 77.076ms ใน PG16

  • Parallel hash join สำหรับ FULL/RIGHT join

    • PostgreSQL 11 นำ Parallel Hash Join เข้ามา ซึ่งให้ parallel worker หลายตัวร่วมกันสร้าง hash table เดียว
    • Parallel Hash Join ของ PostgreSQL 16 รองรับชนิด join แบบ FULL และ RIGHT
    • แผน FULL OUTER JOIN และ Right Join ก็สามารถรันแบบ parallel ได้
    • ในตัวอย่าง FULL JOIN PG15 ใช้ Hash Full Join ตัวเดียว ส่วน PG16 ใช้ Parallel Hash Full Join และ Gather
    • เอาต์พุตของ PG16 แสดง Workers Planned: 1, Workers Launched: 1
    • เวลารันลดลงอย่างมาก จาก 220.677ms ใน PG15 เป็น 129.769ms ใน PG16

การปรับแต่ง window function

  • ข้ามการประมวลผล RANGE ที่ไม่จำเป็น

    • ใน window function เช่น row_number(), rank(), dense_rank(), percent_rank(), cume_dist(), ntile() หากไม่มีตัวเลือก ROWS ใน window clause PostgreSQL จะใช้ตัวเลือกเริ่มต้น RANGE
    • ตัวเลือก RANGE ต้องตรวจสอบแถวด้านหน้าเพื่อหา peer row ที่มีค่าการจัดเรียงเดียวกัน และอาจมีต้นทุนสูงหากมีค่าที่ซ้ำกันจำนวนมากตาม ORDER BY
    • ฟังก์ชันข้างต้นมีพฤติกรรมไม่ต่างกันไม่ว่าจะระบุ ROWS หรือ RANGE แต่ executor ก่อน PostgreSQL 16 แยกแยะเรื่องนี้ไม่ได้ จึงต้องตรวจ peer row ทุกกรณี
    • planner ของ PostgreSQL 16 รู้ว่า window function ใดได้รับผลจากตัวเลือก ROWS/RANGE และส่งข้อมูลให้ executor ข้ามการประมวลผลที่ไม่จำเป็น
    • ในตัวอย่าง row_number() <= 10 PG15 อ่าน 50,410 แถวจาก index แล้วจึงหยุด แต่ PG16 อ่านเพียง 11 แถว
    • PG16 ใช้ประโยชน์จากข้อเท็จจริงที่ว่าเมื่อ row_number ถึง 11 แล้ว จะไม่มีแถวอื่นที่ตรงกับเงื่อนไข <= 10
    • เวลารันเร็วขึ้นมากกว่า 500 เท่า จาก 29.775ms ใน PG15 เป็น 0.058ms ใน PG16

  • ขยายการหยุดก่อนเวลาสำหรับ window function ที่เพิ่มแบบโมโนโทนิก

    • PostgreSQL 15 ทำให้สามารถหยุดการรัน WindowAgg ได้เร็วขึ้น หากเงื่อนไขใน WHERE clause กลายเป็น false ครั้งหนึ่งแล้วไม่สามารถกลับมาเป็น true ได้อีกสำหรับ window function บางตัว
    • PostgreSQL 16 ขยายเป้าหมายของการปรับแต่งนี้ไปยัง ntile(), cume_dist(), percent_rank()
    • ใน PostgreSQL 15 ใช้ได้เฉพาะกับ row_number(), rank(), dense_rank(), count(), count(*)
    • ในตัวอย่าง percent_rank() <= 0.01 PG15 ประมวลผลเงื่อนไขเป็น Filter ของ subquery และ WindowAgg ประมวลผลครบทั้ง 50,000 แถว
    • PG16 ใช้เงื่อนไขเดียวกันเป็น Run Condition เพื่อหยุดการรัน WindowAgg ได้ก่อนเวลา
    • เวลารันเร็วขึ้นมากกว่า 4 เท่า จาก 84.358ms ใน PG15 เป็น 19.454ms ใน PG16

Partitioned table และการจัดการ DISTINCT ที่ชัดเจน

  • ลบ LEFT JOIN ของ partitioned table

    • PostgreSQL สามารถลบ LEFT JOIN ที่ไม่จำเป็นต่อคิวรีและไม่มีโอกาสทำให้แถวซ้ำได้มานานแล้ว
    • ก่อน PostgreSQL 16 ยังไม่รองรับ การลบ LEFT JOIN สำหรับ partitioned table
    • เพราะ partitioned table ยังไม่มีหลักฐานที่จำเป็นในการตัดสินว่าแถวฝั่งในไม่มีโอกาสทำให้แถวฝั่งนอกซ้ำหรือไม่
    • planner ของ PostgreSQL 16 นำการปรับแต่งการลบ LEFT JOIN มาใช้กับ partitioned table ด้วย
    • การปรับแต่งนี้อาจมีประโยชน์เป็นพิเศษใน view
      • เพราะแม้ view จะมีหลายคอลัมน์ แต่คิวรีจริงไม่ได้ดึงทุกคอลัมน์เสมอไป
    • ในตัวอย่าง แผนของ PG15 มี join กับ part_tab แต่แผนของ PG16 ทำเฉพาะ sequential scan บน normal_table

  • จัดการ DISTINCT ที่ผลลัพธ์ถูกกำหนดให้มีค่าเดียวด้วย Limit

    • PostgreSQL planner สามารถข้าม plan node สำหรับลบค่าซ้ำในผลลัพธ์ได้ หากตรวจพบว่าทุกแถวมีค่าเดียวกัน
    • PostgreSQL 16 ใช้ประโยชน์จากข้อเท็จจริงว่าผลลัพธ์มีเพียงค่าเดียวกัน เมื่อคอลัมน์เป้าหมายของ DISTINCT ทั้งหมดถูกตรึงด้วยเงื่อนไขเท่ากันใน WHERE clause และจัดการด้วย LIMIT 1
    • ในคิวรีตัวอย่าง SELECT DISTINCT a,b,c FROM abc WHERE a = 5 AND b = 5 AND c = 5 แต่ละคอลัมน์ DISTINCT ถูกจำกัดให้เป็นค่าเดียวกัน
    • PG15 อ่านผลลัพธ์ทั้งหมดแล้วลดเหลือ 1 แถวด้วย operator Unique
    • PG16 ใช้ Limit และ sequential scan เพื่อคืนค่าเพียง 1 แถว
    • เวลารันเร็วขึ้นมากกว่า 1200 เท่า จาก 30.381ms ใน PG15 เป็น 0.025ms ใน PG16

ขยายการใช้ Incremental Sort หลัง Merge Join

  • ก่อน PostgreSQL 16 เมื่อ planner พิจารณา Merge Join จะใช้ลำดับของ join ก็ต่อเมื่อลำดับการจัดเรียงของ join ตรงกับข้อกำหนดของงานชั้นบนอย่าง DISTINCT, GROUP BY, ORDER BY พอดีเท่านั้น
  • กฎนี้ยังสะท้อนความจริงที่ว่า Incremental Sort สามารถใช้ประโยชน์จากอินพุตที่ถูกจัดเรียงบางส่วนในงานชั้นบนได้ไม่เพียงพอ
  • PostgreSQL 16 ผ่อนกฎการพิจารณาลำดับของ Merge Join จาก “ต้องตรงกันพอดี” เป็น “อย่างน้อย 1 คอลัมน์นำหน้าต้องถูกจัดเรียงถูกต้อง”
  • จากการเปลี่ยนแปลงนี้ planner จึงสามารถใช้ Incremental Sort เพื่อปรับผลลัพธ์ของ Merge Join ให้ตรงกับงานชั้นบนได้บ่อยขึ้น
    • Incremental Sort ใช้อินพุตที่ถูกจัดเรียงบางส่วนเพื่อ sort เป็น batch เล็ก ๆ จึงลดการใช้หน่วยความจำและจำนวนครั้งในการเปรียบเทียบเมื่อเทียบกับการ sort ทั้งหมด
  • ในตัวอย่าง PG15 ใช้ Sort ทั้งหมดหลัง Merge Join ส่วน PG16 ใช้ Incremental Sort
    • หน่วยความจำสูงสุดของ Incremental Sort ใน PG16 อยู่ที่ 26KB
    • เวลารันลดลงเล็กน้อยจาก 1010.738ms ใน PG15 เป็น 915.589ms ใน PG16 และหน่วยความจำที่ใช้ในการ sort ลดลงมาก

วิธีนำไปใช้และการตรวจสอบในงานจริง

  • การปรับปรุง planner ทั้ง 10 รายการของ PostgreSQL 16 เปิดใช้งานเป็นค่าเริ่มต้น ทั้งหมด
  • การปรับแต่งแต่ละอย่างจะถูกใช้ในทุกกรณีที่เป็นไปได้ หรือถูกเลือกใช้เมื่อ planner ประเมินว่าเป็นประโยชน์
  • หากใช้งาน PostgreSQL เวอร์ชันเดิมอยู่ สามารถรัน workload จริงบน PostgreSQL 16 เพื่อดูว่าคิวรีใดเร็วขึ้นบ้าง
  • สามารถแชร์ฟีดแบ็กจากการใช้งานจริงได้ที่เมลลิงลิสต์ pgsql-general@postgresql.org

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

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

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

  • คงจะดีมากถ้า query planner ของ PostgreSQL สามารถวางแผนคิวรีใหม่ระหว่างการรันได้
    คิวรีที่ช้าผิดปกติมักเกิดจาก planner ไม่มีข้อมูลที่จำเป็นเกี่ยวกับการกระจายตัวของข้อมูล จึงประเมินต้นทุนผิด และเกิดความต่างได้ง่ายถึง 1000 เท่า เช่น เวลารันกลายเป็น 1 วินาทีแทนที่จะเป็น 1ms
    สถิติของตารางไม่มีทางแม่นยำ 100% อยู่แล้ว ดังนั้นหลังเริ่มคิวรี ถ้าความคืบหน้าช้ากว่าที่คาด ก็น่าจะนำข้อมูลความคืบหน้าปัจจุบัน เช่น จำนวนเพจที่สแกนแล้วและทูเพิลที่แมตช์ กลับใส่ให้ planner เพื่อสร้างแผนใหม่
    แต่ PostgreSQL ไม่ได้สร้างผลลัพธ์จนจบแล้วค่อยส่ง แต่ส่งแบบสตรีมมิง ดังนั้นถ้าจะเปลี่ยนแผนกลางทาง ต้องติดตามผลลัพธ์ที่ส่งไปให้ไคลเอนต์แล้ว ทำให้ต้องเปลี่ยนโครงสร้างพื้นฐานครั้งใหญ่
    อีกทั้งไคลเอนต์ยังอาจกลับทิศทางกลางคิวรีเพื่อขอผลลัพธ์ก่อนหน้าแบบเรียงย้อนกลับได้ ทำให้ซับซ้อนยิ่งขึ้น

    • ในฐานะผู้เขียนบล็อกและ committer ของ PostgreSQL ผมคิดว่าถ้ามีฟีเจอร์นี้ก็คงดี อย่างไรก็ตาม ปัญหาการส่งทูเพิลให้ไคลเอนต์ ยุ่งยากกว่าที่กล่าวไว้ข้างต้น
      เพราะไม่มีแม้แต่การรับประกันว่าแผนใหม่จะคืนทูเพิลชุดเดียวกัน เช่น ถ้าไม่มี ORDER BY อย่าง SELECT * FROM table LIMIT 10 ทูเพิลที่จะออกมานั้นไม่กำหนดแน่นอน
      วิธีที่ง่ายกว่าอาจเป็นการสะสมทูเพิล X ตัวไว้ในคิว และเมื่อคิวเต็มแล้วจึงเริ่มส่ง หลังจากคิวเต็มก็ถือว่าช้าเกินไปสำหรับการวางแผนใหม่และตรึงอยู่กับแผนปัจจุบัน
      ผู้ใช้สามารถปรับค่า X เพื่อแลกกับการใช้หน่วยความจำมากขึ้นและ latency ของทูเพิลแรกที่นานขึ้น เพื่อเพิ่มช่วงเวลาที่สามารถเปลี่ยนแผนได้
    • อีกมุมหนึ่งคืออาจมีคิวรีที่ยอมให้ ใช้เวลาวางแผนนาน ได้ อนุญาตให้ใช้เวลา 1 วินาทีหรือหลายวินาทีในการเลือกแผนที่ดีที่สุด และระหว่างนั้นอาจเก็บสถิติเพิ่มเติมหรือทดลองรันคิวรีสั้น ๆ ได้
    • สงสัยว่าฟีเจอร์ที่ไคลเอนต์สามารถกลับทิศทางกลางคิวรีเพื่อรับผลลัพธ์ก่อนหน้าแบบย้อนลำดับนั้นมีประโยชน์ตรงไหน
    • ถ้าคิวรีไม่ได้กำหนดลำดับการเรียงอย่างสมบูรณ์ สงสัยว่า แผนคิวรี จะมีผลต่อลำดับผลลัพธ์ได้หรือไม่ ถ้าได้ วิธีที่เสนอก็อาจแทบเป็นไปไม่ได้
      คิวรีใหม่ไม่สามารถแค่ข้ามผลลัพธ์ N แถวแรกได้ แต่ต้องตรวจเทียบทุกแถวที่ส่งไปแล้วกับชุดผลลัพธ์
    • บทความวิจัยนี้และงานที่อ้างถึงอาจน่าสนใจ: https://arxiv.org/pdf/1902.08291

  • สำหรับการแสดงภาพคิวรี ผมใช้เครื่องมือนี้: https://explain.dalibo.com/
    ยังมี https://www.pgexplain.dev/ ด้วย เมื่อก่อนผลลัพธ์ไม่ค่อยดีเท่าไร แต่ตอนนี้ทั้งสองดูใกล้เคียงกันแล้ว

    • เครื่องมือนั้นยอดเยี่ยมและผมก็ใช้อยู่ แต่ยังไม่ได้เข้าใจลึกพอที่จะเห็นส่วนที่ดูไม่ดีในแผนแล้วรู้ว่าควรแก้แนวทางของตัวเองอย่างไร
    • ดูจากโปรไฟล์แล้วเป็น CTO ด้านฟินเทค เลยสงสัยว่าจัดการกับคำแนะนำของเครื่องมือนั้นที่ว่า “แนะนำว่าอย่าส่งข้อมูลสำคัญหรือข้อมูลอ่อนไหว” อย่างไร
      สงสัยว่ามี เครื่องมือทำความสะอาด execution plan ที่ช่วยในสถานการณ์แบบนี้หรือไม่

  • การปรับปรุง query planner เป็นสิ่งที่ยินดีเสมอ และเป็นส่วนสำคัญมากของฐานข้อมูล แน่นอนว่าปกติมันจะเด่นชัดที่สุดตอนที่ไม่ทำงานอย่างที่ผมต้องการ
    สิ่งที่ผมค่อนข้างหงุดหงิดเป็นการส่วนตัวคือ JIT ใน PostgreSQL รุ่นใหม่ ๆ heuristic ที่ใช้ตัดสินใจว่าจะใช้เมื่อไรดูไม่แข็งแรงเลย
    เห็นในคิวรีแบบทั่วไปที่ ORM สร้างขึ้น ตัวคิวรีเองเรียบง่าย แต่ดึงตารางจำนวนมากเข้ามาด้วย join ถ้าไม่มี JIT ก็จบในไม่กี่มิลลิวินาที แต่ JIT เพิ่มเวลาอีก 1–1.5 วินาที ทำให้ช้ามากแม้กับข้อมูลขนาดเล็ก
    ตอนนี้รู้แล้วว่าแค่ปิด JIT ก็พอ แต่สำหรับผู้ใช้ที่ยังหาสาเหตุไม่ได้ว่าทำไมถึงช้า มันอาจทำลายความประทับใจต่อ PostgreSQL ได้มาก ผมชอบ PostgreSQL แต่การเปิด JIT เป็นค่าเริ่มต้นดูเสี่ยงเกินไป

    • ในฐานะผู้เขียนบล็อกและ committer ของ PostgreSQL ผมเห็นด้วยอย่างยิ่งว่า โค้ดที่ตัดสินใจว่าจะใช้ JIT หรือไม่ จำเป็นต้องปรับปรุง
      ใน PG16 ดูแค่ต้นทุนรวมโดยประมาณของแผน และไม่พิจารณาจำนวน expression ที่ต้องคอมไพล์
      การคอมไพล์ expression ไม่กี่ตัวทำได้เร็ว แต่ถ้าคิวรีตารางแบบ partition ที่มีหลายร้อย partition และแผนรวม partition เหล่านั้นทั้งหมด JIT compiler ก็จะมีงานให้ทำมาก
      ผมกับเพื่อนร่วมงานมีโค้ดสำหรับปรับปรุงเรื่องนี้อยู่ แต่ ณ ตอนนี้ยังไม่แน่ใจว่าจะเข้า PG17 หรือไม่
    • อีกอย่างที่รู้สึกแปลกเกี่ยวกับ JIT คือ โค้ดที่สร้างขึ้นไม่ได้ถูกแคช ทั้งที่มักเป็นส่วนที่แพงที่สุดของการรันคิวรี แต่ไม่รู้ว่าทำไมถึงไม่แคช
      ลองค้นการอภิปรายเกี่ยวกับ JIT ใน mailing list ของ PostgreSQL แล้วก็ยังหาเหตุผลที่ฟังขึ้นไม่ได้
      สำหรับ workload แบบ OLTP การปิด JIT น่าจะถูกต้อง
    • ผมมองว่า JIT แทบจะเป็นความล้มเหลว เจตนาดี แต่ LLVM ไม่ใช่เครื่องมือที่เหมาะกับเรื่องนี้ ผมปิดแบบ global ไว้แล้ว
      ผมไม่ได้ใช้ ORM ดังนั้นมันก็ไม่ใช่แค่เพราะ pattern คิวรีแปลก ๆ
      ในทางกลับกัน query parallelization มีประโยชน์จริงได้ และที่สำคัญคือแทบไม่ค่อยสร้างผลเสีย
    • ช่วงหลังเจอบั๊กประหลาดเกี่ยวกับ JIT ใน production
      พออัปเดตแพ็กเกจบางตัวด้วย apt คิวรีขนาดใหญ่ที่รันทุก 5 นาทีเริ่มล้มเหลวกะทันหัน พูดให้แม่นคือ PostgreSQL ตัดการเชื่อมต่อเงียบ ๆ ระหว่างรันคิวรีโดยไม่เหลือ log ไว้เลย
      ลองรัน EXPLAIN ด้วยมือเพื่อตรวจสอบ พบว่าเฉพาะรูปแบบคิวรีที่ไปใช้ JIT เท่านั้นที่พัง ส่วนแบบที่ไม่ใช้ JIT ยังปกติ พอปิด JIT ก็กลับมาปกติ
    • สงสัยว่าเคยลองใช้ prepared statement เพื่อคอมไพล์แค่ครั้งเดียว แล้วนำผลการคอมไพล์กลับมาใช้ซ้ำทุกครั้งที่รันคิวรีนั้นหรือไม่

  • สงสัยว่าการเปลี่ยนแปลงเหล่านี้ได้ผลบ่อยแค่ไหนในคิวรีจริง โดยเฉพาะการเปลี่ยนแปลง “ใช้ Limit แทน Unique ในการทำ DISTINCT เมื่อทำได้” รู้สึกเหมือนจะใช้ได้เฉพาะกับคิวรีที่โง่มาก ๆ เท่านั้น
    สงสัยว่านักพัฒนา PostgreSQL มีแหล่งข้อมูลสำหรับตัดสินเรื่องนี้หรือไม่

    • น่าจะได้ผลค่อนข้างบ่อย DISTINCT เป็นสิ่งที่นักพัฒนาประสบการณ์น้อยมักใส่เข้าไปเพื่อแก้คิวรีแย่ ๆ และโดยปกติสิ่งแรกที่ทำเมื่อเริ่มปรับปรุงประสิทธิภาพคือเขียนคิวรีใหม่ให้ไม่ต้องใช้มัน
      ถ้าการปรับปรุง DISTINCT ทำให้ทนต่อคิวรีแย่ ๆ ได้มากขึ้น ก็มีอะไรให้ได้ประโยชน์มากอยู่ แม้จะแก้ไม่ได้ทุกปัญหา แต่การปรับปรุงใด ๆ ก็ยินดีทั้งนั้น
    • ในฐานะผู้เขียนบล็อกและผู้เขียนฟีเจอร์นี้ เรื่องนี้มาจากเมลลิงลิสต์ pgsql-hackers จริง ๆ
      เห็นด้วยว่าโอกาสที่มันจะถูกนำไปใช้บ่อยคงต่ำ แต่ข้อดีคือการตรวจว่ามันใช้ได้หรือไม่นั้นง่ายพอ ๆ กับการเช็กว่าพอยน์เตอร์เป็น NULL หรือไม่
      การตรวจจับนั้นง่ายมาก และส่วนใหญ่จะใช้ไม่ได้ แต่ในกรณีที่ใช้ได้ ก็อาจให้ประสิทธิภาพดีขึ้นอย่างมาก
    • ปัญหาคือ ORM มีนิสัยสร้างคิวรีที่โง่มาก ๆ และนักพัฒนาก็ปฏิเสธที่จะเขียน SQL เองเพื่อแก้ไข เพราะรู้สึกว่ามันไม่ค่อย “บริสุทธิ์” ยังไงไม่รู้
      คงไม่ใช่ปัญหาที่พบบ่อยมาก แต่ถ้ามันโผล่มาเป็นครั้งคราวก็ไม่น่าแปลกใจ
    • ที่ทำงานเก่า ด้วยเหตุผลด้าน legacy เราอนุญาตให้มีอีเมลซ้ำในตารางผู้ใช้ได้ แต่ไม่อยากให้มีรายการซ้ำใหม่ ๆ เลยรันคิวรี select distinct email from users where email = ? ก่อนสร้างผู้ใช้ใหม่
      คิดว่าแถวที่มีอีเมลเดียวกันคงไม่เกิน 100 แถว ส่วนใหญ่เป็นผู้ใช้ทดสอบที่น่าจะลบได้ แต่เรื่องชักออกนอกประเด็นแล้ว

  • อยากให้ PostgreSQL มี strict mode สำหรับทดสอบแอป เป็นโหมดที่ดูแค่ตัวคิวรีเอง โดยไม่ขึ้นกับสถิติ และถ้ามีดัชนีแล้วคิวรีจะดีขึ้นในเชิง asymptotic แต่ไม่มีดัชนีนั้น ก็ให้คืนข้อผิดพลาด
    อยากให้มีคำสั่ง CREATE INDICES FOR ที่สร้างดัชนีนั้นสำหรับการอัปเกรดแอปด้วย และสำหรับการใช้งานแบบ interactive/พัฒนา ก็อยากให้มีโหมดสร้างดัชนีอัตโนมัติ
    โดยรวมแล้วระบบควรถูกออกแบบให้ไม่มีทางเกิดการดำเนินการที่ไม่เหมาะสมในเชิง asymptotic เลย

  • ไม่เข้าใจว่าทำไมไม่ทำ hint

    • มีส่วนขยาย pg_hint_plan อยู่ ความเสี่ยงของ hint คือ แม้ตอนที่เขียนจะถูกต้อง แต่ถ้าขนาดตารางหรือความเอนเอียงของข้อมูลเปลี่ยนไป มันอาจกลับทำให้แย่ลงได้
      เท่าที่จำได้ ตอนที่เคยเห็นการถกเถียงเรื่อง hint หากเป็นวิธีที่ไม่ผูก planner ไว้แน่นเกินไป และยังปรับตัวตามการเปลี่ยนแปลงของข้อมูลพื้นฐานได้ ก็ไม่ได้มีการคัดค้านทั่วไปนัก
      เช่น แทนที่จะระบุว่า predicate หนึ่งจะจับคู่กับ 10 แถว ก็แจ้งว่ามีความสัมพันธ์กันระหว่างสองคอลัมน์แทน
    • การถกเถียงที่เกี่ยวข้อง: Why PostgreSQL doesn't have query hints
      https://news.ycombinator.com/item?id=2179433 (60 ความเห็น, ปี 2011)
      จุดยืนอย่างเป็นทางการในวิกิของ PostgreSQL อยู่ที่ https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion
      จุดยืนคือ “เราไม่สนใจ hint ในรูปแบบเดียวกันเป๊ะ ๆ กับที่มักถูกนำไปใช้ในฐานข้อมูลอื่น”
      ปัญหาของระบบ hint ที่มีอยู่ ได้แก่ ทำให้การบำรุงรักษาโค้ดแอปพลิเคชันแย่ลง ขัดขวางการอัปเกรด ส่งเสริมนิสัย DBA ที่ไม่ดี และไม่สอดคล้องกับการขยายตัวของขนาดข้อมูล
      ไม่ได้อยากตำหนิจุดยืนนั้น แต่เวลาที่ PostgreSQL เลือกแผนโง่ ๆ แล้วเรากล่อมให้มันเลือกอย่างสมเหตุสมผลไม่ได้ มันก็น่าหงุดหงิด

  • เพื่อนของผมที่เป็น Microsoft DBA สำหรับบริษัทขนาดกลางบอกว่าใช้ PostgreSQL ทำงานจริงจังไม่ได้ เขาถึงกับบอกว่าช็อกที่รู้ว่า PostgreSQL ไม่มีแม้แต่ query planner
    พักเรื่องล้อเลียนไว้ก่อน ผมสงสัยว่าคำกล่าวอ้างที่ใหญ่กว่านั้นว่า MSSQL รับมือกับสเกลที่ PostgreSQL ไม่เหมาะได้ มีน้ำหนักแค่ไหน สัญชาตญาณบอกว่ามันเหลวไหล แต่ผมก็ไม่ใช่ DBA เลย

    • ก็มีแง่นั้นอยู่ ถ้าต้องการฐานข้อมูลที่รับมือแทบทุกอย่างที่ต้องการได้ดีพอ MSSQL กับ Oracle ก็น่าจะทำได้
      พวกเขาแก้ปัญหาด้วยวิธีอัดเงินกับฮาร์ดแวร์ หรือก็คือเงินเพิ่ม เข้าไปเรื่อย ๆ จนกว่าปัญหาจะหาย แน่นอนว่ามีเทคโนโลยีฉลาด ๆ อยู่ด้วย แต่โดยพื้นฐานแล้วมีงานวิศวกรรมถูกทุ่มเข้าไปมากกว่ามากตลอดเวลายาวนาน
      สามารถ scale out ในแนวนอนได้ใหญ่กว่าที่ PostgreSQL ทำได้อย่างสมเหตุสมผล
      แต่ PostgreSQL ก็กำลังไล่ตามอยู่ และ MySQL/MariaDB ก็อาจถือได้ว่าเรื่องฝั่งนี้ทำได้ดีมาตลอด ตัวเลือกการ scale แนวนอนดีขึ้นเรื่อย ๆ
      ตอนนี้การรันคลัสเตอร์ PostgreSQL ระดับหลายเทราไบต์บนเครื่องจำนวนน้อยเพื่อรับทราฟฟิกสูง ๆ และโยน “big data” ไปไว้ในฐานข้อมูลที่เฉพาะทางกว่า ก็ทำได้ง่ายขึ้นแล้ว วิธีแบบเก่าที่อัดทุกอย่างเข้า MSSQL/Oracle อาจจะล้าสมัยไปหน่อย
    • ผมเคยพัฒนา MSSQL มาเยอะ และ PostgreSQL ก็มีฟีเจอร์บางอย่างที่ขาดไปแบบน่าแปลกใจอยู่
      สิ่งที่เพื่อนคุณพูดถึงอาจเป็นข้อเท็จจริงที่ว่า PostgreSQL ไม่มีวิธี แคชหรือปักหมุด query plan PostgreSQL จะวางแผนใหม่ทุก statement เว้นแต่จะใช้ prepared statement แบบทำเอง และถึงอย่างนั้นก็ทำงานเฉพาะต่อ connection เท่านั้น
      MSSQL แคชและนำ plan กลับมาใช้ซ้ำมานานแล้ว ดังนั้น planner จึงใช้เวลากับการสร้าง plan ได้มากกว่า อีกทั้งยังมี hint และสามารถปักหมุด plan ได้ด้วย
      PostgreSQL ต้องการ hint จริง ๆ ต่อให้ optimizer ยอดเยี่ยมแค่ไหน บางครั้งผมก็รู้ดีกว่า และอยากบังคับให้มันฟังผม
      อีกอย่าง PostgreSQL ไม่มี clustered index ที่แท้จริง และทุกตารางเป็น heap ใน MSSQL คนส่วนใหญ่ใช้บ่อย และมักตั้ง primary key เป็น clustered index ทำให้ตัวตารางเองกลายเป็น index และการ lookup ด้วย key ไม่มีการอ้างอิงทางอ้อม
      ที่น่าสนใจคือ SQLite กลับตรงกันข้าม ตารางจะมี clustered index เสมอ ไม่ว่าคุณจะสร้างหรือไม่ก็ตาม ส่วน MSSQL ให้เลือกได้ระหว่าง heap กับ index-organized table
    • PostgreSQL มี query planner อยู่ บทความนี้ทั้งบทก็พูดถึงการปรับปรุงมัน ดังนั้นน่าจะเป็นการสื่อสารผิดพลาด หรือเพื่อนคุณดูเหมือนไม่รู้จัก PostgreSQL เลย
      มีกรณีใช้งานฐานข้อมูล PostgreSQL ขนาดใหญ่มากที่ทำงานได้ดี ดังนั้น PostgreSQL ก็ scale ได้แน่นอน
      อย่างไรก็ตาม SQL Server ก็มีฟีเจอร์ที่ PostgreSQL ไม่มี และถ้าฟีเจอร์เหล่านั้นสำคัญ ก็อาจเหมาะกับบาง use case มากกว่า สุดท้ายแล้วมันคือฐานข้อมูลคนละตัวที่มีจุดแข็งและจุดอ่อนต่างกัน
    • ผมเคยใช้ทั้งสองตัวทั้งใน OLTP และ data warehousing และทั้งสองตัวก็โอเค
      ตอนแรกผมตั้งใจจะเขียนว่า ถ้าไม่ใช่เพราะแอปจาก vendor ที่ต้องใช้ SQL Server ผมคงแนะนำให้บริษัทย้ายไป PostgreSQL
      แต่แล้วก็รู้ตัวว่าการแทนที่สิ่งที่ Microsoft แถมมาให้ เช่น reporting services, integration services, jobs, การผสานกับ AD, service broker จะมีงานมากแค่ไหน notify/listen ไม่มีประเภทข้อความ
      analysis services เราไม่ใช้แล้ว แต่ตอนที่เคยใช้ ก็คงแทนที่ได้ยากเช่นกัน
      สิ่งเหล่านี้แหละที่ทำให้คนยังติดอยู่ ผมนึกไม่ออกด้วยซ้ำว่าจะใช้เวลานานแค่ไหนในการแทนที่ทั้งหมดนี้ และการใช้เวลา 1 ปีเพื่อแทนที่สิ่งที่มีอยู่แล้วก็ไม่ใช่ผลตอบแทนจากการลงทุนที่ดี
    • Aurora ของ AWS ดูเหมือนจะรับมือได้ค่อนข้างดี และตั้งเป้าเป็นตัวแทนแบบ drop-in ของ PostgreSQL และ MySQL

  • สงสัยว่าทำไมเนื้อหานี้ถึงเผยแพร่ที่ citusdata ไม่ใช่ postgresql.org ไม่รู้ว่าเป็นฟีเจอร์เฉพาะแบบเสียเงิน หรือเป็นส่วนเพิ่มแบบโอเพนซอร์ส

    • เพราะผู้เขียนทำงานที่ Citus Data และยังเป็นคนเขียน optimization บางส่วนเหล่านั้นเองด้วย

  • เมื่อไหร่กันนะที่จะใช้ index เพื่อทำให้คิวรี IS NOT DISTINCT FROM เร็วขึ้นได้ ;)