Postgres SQL Optimization

ทำไม query ช้า, รัน query แล้ว timeout บ่อย ที่นี่มีคำตอบ คลิก!!

COUNT(*) vs COUNT(1)

COUNT(1) หรือ COUNT({column_name}) จะเร็วกว่า COUNT(*) เสมอ (ถ้าในตารางไม่ได้มีคอลัมน์เดียว)

• (*) คำสั่งนี้จะเรียกข้อมูลทุกคอลัมน์ในตารางนั้นขึ้นมารอก่อน แล้วค่อยนับว่ามีผลลัพธ์กี่ record
• (1) หรือ ({column_name}) จะเรียกข้อมูลมานับเฉพาะคอลัมน์แรก หรือคอลัมน์ที่เรากำหนด

UNION vs UNOIN ALL …more

• UNION ใช้สำหรับรวมสองตารางเข้าด้วยกันโดยกัน (ผลลัพธ์เป็นค่าที่ไม่ซ้ำกัน)
• UNION ALL คือการนำสองตารางมาต่อกัน (ผลลัพธ์มีค่าที่ซ้ำกันได้)

TIP: ถ้าผลลัพธ์ที่ต้องการมี record ที่ซ้ำกันได้ หรือรู้อยู่แล้วว่าไม่มีข้อมูลซ้ำในการ union ครั้งนี้ หการใช้ UNION ALL จะเร็วกว่า เพราะระบบไม่ต้องวนลูปเช็คข้อมูลซ้ำเหมือนคำสั่ง UNION

คิดดีๆก่อนใช้ “ WITH” statement

ข้อมูลที่ทำการ query จาก with statement จะ ไม่มี index, เพราะฉนั้น การใช้ with เพื่อมา query ต่อจึงทำให้ช้ากว่าการใช้ Sub Query ใน SQL หลักตรงๆไปเลย ( ถ้าคอลัมน์ที่ใช้ JOIN มี INDEX)

sub query ภายใน with statement ไม่ควรมีผลลัพธืที่เป้น table ใหญ่ๆ เช่นการ ‘select * from table’ แบบไม่มีฟิลเตอร์อะไรเลย เพราะจะทำให้ query ช้า และใช้ memory ของระบบเป็นจำนวนมาก

เมื่อไหร่ควรใช้ WITH ?

• มีผลลัพธ์ปลายทางชัดเจน ( มีการ where ใน subquery ผลลัพธ์ไม่ใหญ่มาก)
• INDEX ไม่มีผลต่อการ JOIN กับตารางหลัก (optional)

***ถ้าใช้ with statement ใน SQL บ่อยๆ หมายความว่าควรสร้างตารางใหม่สำหรับเรื่องนั้นๆแล้ว

ใช้ ‘NOT EXISTS’ แทนการ ‘LEFT JOIN ON null id’

Slow Query (A):

SELECT developper.name
        FROM developper
        LEFT JOIN talent ON developper.id = talent.foreign_id
        WHERE talent.id IS NULL
Execution time: 1.585 ms

Optimized Query (B):

SELECT developper.name
        FROM developper
        WHERE NOT EXISTS (SELECT 1 talent.id WHERE developper.id = talent.foreign_id)
Execution time: 1.050 ms

จาก SQL ด้านบน ทั้งสองแบบมีผลลัพธ์แบบเดียวกัน แต่ performance ของ B จะดีกว่าเกือบเท่าตัว

Distinct On vs Partition

เมื่อต้องการเลือกข้อมูล record ล่าสุด หรือ record แรกตามกลุ่มที่แบ่งไว้ การใช้คำสั่ง Distinct on จะเร็วกว่าการทำ Partitions ( window function )

Slow Query (A):

SELECT employee_id, branch_id FROM (
    SELECT
      ROW_NUMBER() OVER( partition by employee_id ORDER BY  employee_branch.created_at) as row_num,
      employee_id,
      branch_id
    FROM employee_branch
) AS twr
WHERE row_num = 1

Optimized Query (B):

SELECT 
  DISTINCT ON ( employee_id)
  employee_id,
  branch_id
FROM employee_branch
ORDER BY employee_branch.created_at

จาก SQL ด้านบน ทั้งสองแบบมีผลลัพธ์แบบเดียวกัน แต่ performance ของ B จะดีกว่า

• *ใช้ได้แค่กรณีที่ต้องการเลือก record แรก หรือสุดท้ายเท่านั้น

มี correlated subqueries ให้น้อยที่สุด

Slow Query (A):

SELECT employee_number, name
   FROM employees emp
   WHERE salary > (
     SELECT AVG(salary)
       FROM employees
       WHERE department = emp.department);

จาก SQL ข้างต้น ใน sub query จะมีการเรียก emp.department ซึ่ง emp นั้นเป็น table ที่อยู่นอก sub query เพราะฉนั้น sub query จะถูกคำนวณใหม่ทุกครั้ง ตามจำนวน record ของ query หลัก ทำให้ performance แย่มากๆ

Optimized Query (B):

SELECT employee_number, name
   FROM employees emp
   JOIN (
     SELECT 
        AVG(salary) as salary
        employee_number
       FROM employees
       GROUP BY employee_number
   ) as emp_avg_salary 
   ON emp_avg_salary.employee_number = emp.employee_number
   WHERE emp.salary > emp_avg_salary.salary

TIP: เปลี่ยนไปใช้ JOIN แทนสิ!

EXISTS statement

การใช้ EXISTS statement เพื่อหาว่ามีข้อมูลนั้นอยู่จริงรึเปล่า จะเร็วกว่าการใช้ JOIN ในการหา เนื่องจาก JOIN จะเช็คข้อมูลทั้งหมดว่ามีข้อมูลซ้ำเท่าไหร่ แต่ EXISTS จะเช็คแค่ว่ามีข้อมูลนั้นรึเปล่า และจะหยุดหาต่อเมื่อเจอข้อมูลที่กำลังหาอยู่

//เช่น

SELECT EXISTS(SELECT 1 FROM employee where gender = 'male');

จาก SQL ข้างต้น ระบบจะหยุดหาเมื่อเจอ gender = ‘male’ record แรก

TIP: ใช้เมื่อต้องการหาว่ามีข้อมูลที่ต้องการอยู่ในตารางนั้นๆหรือไม่ โดยไม่สนว่าจะมีเท่าไหร่