Các tính năng mới của query planner trong Postgres 16

• PostgreSQL 16 bổ sung 10 cải tiến cho query planner/optimizer, mở rộng lựa chọn kế hoạch thực thi cho DISTINCT, tổng hợp, join, window function và truy vấn trên bảng phân vùng
• Trong SELECT DISTINCT, tổng hợp có ORDER BY/DISTINCT, và xử lý sau Merge Join, PostgreSQL 16 tận dụng đầu vào đã được sắp xếp một phần tích cực hơn để tạo kết quả với ít bộ nhớ hơn so với sắp xếp toàn bộ
• Hỗ trợ Memoize bên trong UNION ALL, Right Anti Join, và parallel hash join cho FULL/RIGHT join tập trung vào việc giảm chi phí tra cứu lặp lại và tạo các bảng băm lớn
• Window function giảm xử lý RANGE không cần thiết và giảm các WindowAgg phải chạy đến hết; một số hàm có thể dừng sớm tùy theo điều kiện
• Tất cả cải tiến đều được bật mặc định, vì vậy trước và sau khi nâng cấp lên PostgreSQL 16, rất đáng để so sánh EXPLAIN và thời gian thực thi trên workload thực tế

---

Phạm vi cải tiến planner của PostgreSQL 16

• PostgreSQL 16 đưa vào nhiều cải tiến cho query planner, giúp nhiều truy vấn SQL có thể chạy nhanh hơn so với các phiên bản PostgreSQL trước đó
• Bài viết giải thích chi tiết hơn các cải tiến planner có trong ghi chú phát hành PG16, đồng thời đề cập đến so sánh đầu ra EXPLAIN giữa PG15 và PG16 cùng các ví dụ kiểm thử có thể tái hiện
• Ở đây, planner là thành phần thường được gọi là optimizer trong các cơ sở dữ liệu quan hệ khác

Tối ưu hóa sắp xếp và DISTINCT

• Dùng Incremental Sort trong SELECT DISTINCT

  • Incremental Sort lần đầu được thêm vào PostgreSQL 13; khi kết quả đã được sắp xếp theo các cột đứng trước, nó chỉ sắp xếp các cột còn lại để giảm chi phí
  • Planner của PostgreSQL 16 cũng cân nhắc Incremental Sort cho các truy vấn SELECT DISTINCT
  • Ví dụ, nếu có chỉ mục btree trên cột a và cần thứ tự a, b, có thể dùng chỉ mục để lấy kết quả đã sắp xếp theo a, rồi chỉ sắp xếp b mỗi khi giá trị a thay đổi
  • Với quicksort của PostgreSQL, sắp xếp nhiều nhóm nhỏ có thể hiệu quả hơn sắp xếp một nhóm lớn
  • Trong truy vấn ví dụ, PG15 dùng HashAggregate và quét tuần tự, còn PG16 chọn chỉ mục distinct_test_a_idx và Incremental Sort
  • Presorted Key: a trong đầu ra PG16 có nghĩa là đầu vào đã được sắp xếp theo a đã được tận dụng
  • Cách dùng hash của PG15 spill khoảng 30MB ra đĩa, trong khi bộ nhớ tối đa của Incremental Sort ở PG16 là 26KB
  • Thời gian thực thi giảm từ 414.226ms trên PG15 xuống 263.167ms trên PG16

• Tối ưu hóa tổng hợp có ORDER BY hoặc DISTINCT

  • Trong PostgreSQL 15 trở xuống, các hàm tổng hợp có mệnh đề ORDER BY hoặc DISTINCT luôn thực hiện sắp xếp bên trong node Aggregate
  • Planner của PostgreSQL 16 có thể tạo kế hoạch thực thi cung cấp các hàng theo đúng thứ tự cho node Aggregate, và executor sẽ bỏ qua sắp xếp nội bộ nếu đầu vào đã được sắp xếp
  • Trong ví dụ COUNT(DISTINCT b), cả PG15 và PG16 đều dùng GroupAggregate và Index Only Scan, nhưng đầu ra PG15 xuất hiện temp read=4540 written=4560
  • I/O file tạm này là kết quả của việc sắp xếp ngầm định trong PG15 bị spill ra đĩa
  • Đầu ra PG16 không có I/O tạm tương ứng, và thời gian thực thi tăng tốc hơn 2 lần, từ 302.693ms trên PG15 xuống 115.534ms trên PG16

Cải tiến tra cứu lặp lại và kế hoạch join

• Áp dụng Memoize bên trong UNION ALL

  • Plan node Memoize lần đầu được giới thiệu trong PostgreSQL 14, hoạt động như một lớp cache giữa Nested Loop được tham số hóa và đầu vào bên trong
  • Planner của PostgreSQL 16 cũng cân nhắc dùng Memoize khi bên trong Nested Loop được tham số hóa có truy vấn UNION ALL
  • Trong ví dụ, PG15 thực thi Append 1 triệu lần, nhưng PG16 đặt Memoize phía trên Append
  • Memoize của PG16 ghi nhận Hits: 999990, Misses: 10, Memory Usage: 2kB
  • Số lần thực thi Append giảm từ 1 triệu lần trên PG15 xuống còn 10 lần trên PG16
  • Thời gian thực thi nhanh hơn khoảng 6 lần, từ 1926.151ms trên PG15 xuống 282.120ms trên PG16

• Hỗ trợ Right Anti Join

  • Trong Hash Join của INNER JOIN, thường có lợi hơn khi tạo bảng băm trên bảng nhỏ hơn
  • Bảng băm nhỏ cần ít công tạo hơn, thân thiện hơn với cache CPU, và cũng giảm khả năng CPU stall khi phải chờ dữ liệu từ bộ nhớ chính
  • Trước PostgreSQL 16, Anti Join luôn đặt bảng được nhắc tới trong NOT EXISTS ở phía trong của join, nên có thể phải tạo bảng băm trên bảng lớn hơn
  • PostgreSQL 16 hỗ trợ Right Anti Join, cho phép băm phía nhỏ hơn trong hai bảng
  • Trong ví dụ, PG15 băm bảng large có 1 triệu hàng và dùng 6446KB bộ nhớ, còn PG16 băm bảng small có 100 hàng và chỉ dùng 12KB
  • Thời gian thực thi gần như giảm một nửa, từ 139.023ms trên PG15 xuống 77.076ms trên PG16

• Parallel hash join cho FULL/RIGHT join

  • PostgreSQL 11 giới thiệu Parallel Hash Join, trong đó nhiều worker song song cùng tham gia tạo một bảng băm duy nhất
  • Parallel Hash Join của PostgreSQL 16 hỗ trợ các loại join FULL và RIGHT
  • Các kế hoạch FULL OUTER JOIN và Right Join cũng có thể chạy song song
  • Trong ví dụ FULL JOIN, PG15 dùng một Hash Full Join đơn lẻ, còn PG16 dùng Parallel Hash Full Join và Gather
  • Đầu ra PG16 hiển thị Workers Planned: 1, Workers Launched: 1
  • Thời gian thực thi giảm đáng kể, từ 220.677ms trên PG15 xuống 129.769ms trên PG16

Tối ưu hóa window function

• Bỏ qua xử lý RANGE không cần thiết

  • Với các window function như row_number(), rank(), dense_rank(), percent_rank(), cume_dist(), ntile(), nếu window clause không có tùy chọn ROWS, PostgreSQL dùng tùy chọn RANGE mặc định
  • Tùy chọn RANGE phải kiểm tra các hàng phía trước để tìm peer row có cùng giá trị sắp xếp, và chi phí có thể tăng nếu có nhiều giá trị giống nhau theo ORDER BY
  • Các hàm trên không thay đổi hành vi tùy theo việc chỉ định ROWS hay RANGE, nhưng executor trước PostgreSQL 16 không phân biệt được điều này nên phải kiểm tra peer row trong mọi trường hợp
  • Planner của PostgreSQL 16 biết window function nào bị ảnh hưởng bởi tùy chọn ROWS/RANGE, và truyền thông tin để executor bỏ qua xử lý không cần thiết
  • Trong ví dụ row_number() <= 10, PG15 đọc 50.410 hàng từ chỉ mục rồi mới dừng, còn PG16 chỉ đọc 11 hàng
  • PG16 tận dụng việc khi row_number đạt 11 thì sẽ không còn hàng nào thỏa điều kiện <= 10
  • Thời gian thực thi nhanh hơn hơn 500 lần, từ 29.775ms trên PG15 xuống 0.058ms trên PG16

• Mở rộng dừng sớm cho window function tăng đơn điệu

  • PostgreSQL 15 đã cho phép dừng thực thi WindowAgg sớm khi điều kiện trong mệnh đề WHERE trở thành false một lần và không thể trở lại true đối với một số window function nhất định
  • PostgreSQL 16 mở rộng đối tượng áp dụng tối ưu hóa này sang ntile(), cume_dist(), percent_rank()
  • Trong PostgreSQL 15, tối ưu hóa này chỉ áp dụng cho row_number(), rank(), dense_rank(), count(), count(*)
  • Trong ví dụ percent_rank() <= 0.01, PG15 xử lý điều kiện bằng Filter của subquery, và WindowAgg xử lý toàn bộ 50.000 hàng
  • PG16 dùng cùng điều kiện đó làm Run Condition, qua đó dừng thực thi WindowAgg sớm
  • Thời gian thực thi nhanh hơn hơn 4 lần, từ 84.358ms trên PG15 xuống 19.454ms trên PG16

Bảng phân vùng và xử lý DISTINCT hiển nhiên

• Loại bỏ LEFT JOIN trên bảng phân vùng

  • Từ lâu PostgreSQL đã có thể loại bỏ LEFT JOIN không cần thiết cho truy vấn và không có khả năng gây trùng lặp hàng
  • Trước PostgreSQL 16, loại bỏ LEFT JOIN đối với bảng phân vùng chưa được hỗ trợ
  • Lý do là chưa có chứng minh cần thiết để xác định các hàng bên trong không có khả năng nhân đôi các hàng bên ngoài trong bảng phân vùng
  • Planner của PostgreSQL 16 cũng áp dụng tối ưu hóa loại bỏ LEFT JOIN cho bảng phân vùng
  • Tối ưu hóa này có thể đặc biệt hữu ích với view
    • Vì ngay cả khi view có nhiều cột, truy vấn thực tế không phải lúc nào cũng đọc tất cả các cột
  • Trong ví dụ, kế hoạch PG15 bao gồm join với part_tab, còn kế hoạch PG16 chỉ thực hiện quét tuần tự normal_table

• Xử lý DISTINCT có kết quả chắc chắn là một giá trị bằng Limit

  • Nếu planner của PostgreSQL có thể phát hiện rằng tất cả hàng đều có cùng giá trị, nó có thể bỏ qua plan node dùng để loại bỏ trùng lặp kết quả
  • PostgreSQL 16 tận dụng việc kết quả chỉ chứa cùng một giá trị khi tất cả các cột đích của DISTINCT đều bị cố định bằng điều kiện bằng trong mệnh đề WHERE, và xử lý bằng LIMIT 1
  • Trong truy vấn ví dụ SELECT DISTINCT a,b,c FROM abc WHERE a = 5 AND b = 5 AND c = 5, mỗi cột DISTINCT đều bị giới hạn về cùng một giá trị
  • PG15 đọc toàn bộ kết quả rồi dùng toán tử Unique để rút xuống 1 hàng
  • PG16 dùng Limit và quét tuần tự để chỉ trả về 1 hàng
  • Thời gian thực thi nhanh hơn hơn 1200 lần, từ 30.381ms trên PG15 xuống 0.025ms trên PG16

Mở rộng tận dụng Incremental Sort sau Merge Join

• Trước PostgreSQL 16, khi cân nhắc Merge Join, planner chỉ dùng thứ tự sắp xếp của join nếu thứ tự đó khớp chính xác với yêu cầu của tác vụ cấp trên như DISTINCT, GROUP BY, ORDER BY
• Quy tắc này chưa phản ánh đầy đủ việc Incremental Sort có thể tận dụng đầu vào đã được sắp xếp một phần trong tác vụ cấp trên
• PostgreSQL 16 nới lỏng quy tắc xét thứ tự Merge Join từ “phải khớp chính xác” thành “ít nhất 1 cột đứng trước phải được sắp xếp đúng”
• Thay đổi này giúp planner có thể dùng Incremental Sort thường xuyên hơn để điều chỉnh kết quả Merge Join cho phù hợp với tác vụ cấp trên
  • Incremental Sort tận dụng đầu vào đã sắp xếp một phần để sắp xếp theo từng batch nhỏ, nhờ đó có thể giảm lượng bộ nhớ dùng và số lần so sánh so với sắp xếp toàn bộ
• Trong ví dụ, PG15 dùng Sort toàn bộ sau Merge Join, còn PG16 dùng Incremental Sort
  • Bộ nhớ tối đa của Incremental Sort trên PG16 là 26KB
  • Thời gian thực thi giảm nhẹ từ 1010.738ms trên PG15 xuống 915.589ms trên PG16, còn bộ nhớ dùng cho sắp xếp thì giảm đáng kể

Cách áp dụng và kiểm chứng thực tế

• Cả 10 cải tiến planner của PostgreSQL 16 đều được bật mặc định
• Mỗi tối ưu hóa được áp dụng trong mọi trường hợp có thể, hoặc được áp dụng có chọn lọc khi planner nhận định là hữu ích
• Nếu đang dùng phiên bản PostgreSQL cũ, bạn có thể chạy workload thực tế trên PostgreSQL 16 để xác định những truy vấn nào nhanh hơn
• Phản hồi từ sử dụng thực tế có thể được chia sẻ qua mailing list [e-mail]

[GN⁺]

Ý kiến trên Hacker News

• Sẽ thật tuyệt nếu query planner của PostgreSQL có thể lập kế hoạch lại truy vấn khi đang thực thi giữa chừng
  Các truy vấn chậm một cách bệnh lý thường xảy ra vì planner không biết những thông tin cần thiết về phân bố dữ liệu nên ước tính chi phí sai; chuyện thời gian chạy thành 1 giây thay vì 1ms, tức chênh lệch 1000 lần, cũng rất dễ xảy ra
  Thống kê bảng không thể chính xác 100%, nên sau khi bắt đầu truy vấn, nếu tiến độ chậm hơn dự kiến, sẽ tốt nếu đưa lại các thông tin tiến độ hiện tại như số trang đã quét và số tuple khớp vào planner để tạo kế hoạch mới
  Tuy nhiên PostgreSQL không tạo xong toàn bộ kết quả rồi mới gửi, mà gửi theo kiểu streaming, nên để đổi kế hoạch giữa chừng sẽ phải theo dõi các kết quả đã gửi cho client, kéo theo thay đổi hạ tầng lớn
  Hơn nữa client còn có thể đảo hướng giữa truy vấn để yêu cầu lại các kết quả trước đó theo thứ tự ngược, khiến độ phức tạp tăng thêm

  • Với tư cách là tác giả blog và committer PostgreSQL, tôi cũng nghĩ có tính năng này thì tốt. Tuy nhiên vấn đề gửi tuple cho client còn khó hơn những gì nói ở trên
    Vì thậm chí không có gì đảm bảo kế hoạch mới sẽ trả về cùng các tuple. Ví dụ, nếu không có ORDER BY như SELECT * FROM table LIMIT 10, tuple nào được trả về là không xác định
    Có lẽ cách dễ hơn là xếp X tuple vào một hàng đợi, rồi khi hàng đợi đầy thì bắt đầu gửi. Sau khi hàng đợi đã đầy thì coi như đã quá muộn để lập kế hoạch lại và cố định ở kế hoạch hiện tại
    Người dùng có thể điều chỉnh X để chấp nhận dùng nhiều bộ nhớ hơn và tăng độ trễ đến tuple đầu tiên, đổi lại có thêm thời gian để đổi kế hoạch
  • Một góc nhìn khác là có thể cho phép một số truy vấn được lập kế hoạch lâu hơn. Cho phép dùng 1 giây hoặc vài giây để chọn kế hoạch tối ưu, và trong quá trình đó thu thập thêm thống kê hoặc chạy thử truy vấn trong chốc lát
  • Tôi tò mò tính năng client đảo hướng giữa truy vấn để nhận lại các kết quả trước đó theo thứ tự ngược hữu ích ở đâu
  • Nếu truy vấn không xác định hoàn toàn thứ tự sắp xếp, tôi tò mò liệu kế hoạch truy vấn có thể ảnh hưởng đến thứ tự kết quả hay không. Nếu có, cách được đề xuất có thể gần như bất khả thi
    Truy vấn mới không thể chỉ đơn giản bỏ qua N kết quả đầu, mà phải đối chiếu từng hàng đã gửi với một danh sách
  • Bài báo này và các bài được trích dẫn có thể đáng quan tâm: https://arxiv.org/pdf/1902.08291

• Tôi dùng công cụ này để trực quan hóa truy vấn: https://explain.dalibo.com/
  Cũng có https://www.pgexplain.dev/; trước đây output kém hơn, nhưng giờ cả hai trông có vẻ tương tự nhau

  • Công cụ rất tuyệt nên tôi vẫn dùng, nhưng tôi chưa hiểu đủ sâu để nhìn vào phần có vẻ không tốt trong kế hoạch rồi biết phải sửa cách tiếp cận của mình như thế nào
  • Xem hồ sơ thì bạn là CTO fintech, tôi tò mò bạn xử lý thế nào với cảnh báo của công cụ rằng “khuyến nghị không gửi thông tin quan trọng hoặc nhạy cảm”
    Tôi cũng tò mò liệu có công cụ làm sạch execution plan nào hữu ích trong tình huống này không

• Cải thiện query planner luôn được hoan nghênh, và đó là một phần rất quan trọng trong cơ sở dữ liệu. Tất nhiên thường thì nó nổi bật nhất khi không làm đúng như tôi muốn
  Cá nhân tôi khá bực với JIT trong các phiên bản PostgreSQL gần đây. Heuristic quyết định khi nào dùng nó trông hoàn toàn không vững
  Tôi đã thấy điều này trong các truy vấn điển hình do ORM tạo ra: bản thân truy vấn thì đơn giản nhưng kéo vào nhiều bảng bằng join. Không có JIT thì xong trong vài mili giây, nhưng JIT thêm 1–1,5 giây, khiến nó cực chậm ngay cả với dữ liệu nhỏ
  Giờ thì tôi biết chỉ cần tắt JIT là được, nhưng với người dùng chưa tìm ra vì sao chậm, điều này có thể phá hỏng ấn tượng về PostgreSQL rất nhiều. Tôi thích PostgreSQL, nhưng bật JIT theo mặc định có vẻ quá rủi ro

  • Với tư cách là tác giả blog và committer PostgreSQL, tôi rất đồng ý rằng mã quyết định có dùng JIT hay không cần được cải thiện
    Trong PG16, nó chỉ nhìn vào tổng chi phí ước tính của kế hoạch, chứ không xét số biểu thức cần biên dịch
    Biên dịch vài biểu thức thì nhanh, nhưng nếu truy vấn một bảng phân vùng có hàng trăm partition và tất cả partition đó đều nằm trong kế hoạch, trình biên dịch JIT sẽ có rất nhiều việc phải làm
    Tôi cùng một đồng nghiệp có đoạn mã để cải thiện việc này, nhưng ở thời điểm hiện tại chưa chắc nó có vào PG17 hay không
  • Một điều nữa ở JIT khiến tôi thấy kỳ lạ là mã được sinh ra không được cache. Nó thường là phần đắt nhất trong quá trình chạy truy vấn, vậy mà không hiểu sao lại không cache
    Tôi đã tìm các thảo luận liên quan đến JIT trên mailing list PostgreSQL nhưng không thấy lý do nào thuyết phục
    Với workload OLTP thì tắt JIT là đúng
  • Tôi xem JIT gần như là một thất bại. Ý định thì tốt, nhưng LLVM không phải công cụ phù hợp cho việc này. Tôi đã tắt nó trên toàn hệ thống
    Tôi không dùng ORM, nên đây cũng không đơn giản chỉ là do các mẫu truy vấn kỳ lạ
    Ngược lại, song song hóa truy vấn có thể thực sự hữu ích, và quan trọng hơn là hiếm khi gây hại
  • Gần đây tôi gặp một bug kỳ quặc liên quan đến JIT trong production
    Sau khi cập nhật vài package bằng apt, một truy vấn lớn chạy mỗi 5 phút đột nhiên bắt đầu thất bại. Chính xác là PostgreSQL âm thầm ngắt kết nối giữa lúc thực thi truy vấn mà không để lại log
    Tôi kiểm tra bằng cách chạy EXPLAIN thủ công và thấy chỉ biến thể truy vấn dùng JIT mới hỏng, còn biến thể không dùng thì ổn. Tắt JIT xong mọi thứ lại bình thường
  • Tôi tò mò liệu bạn đã thử dùng prepared statement để chỉ biên dịch một lần, rồi tái sử dụng kết quả biên dịch cho mỗi lần chạy truy vấn đó chưa

• Tôi tò mò không biết những thay đổi này thực sự phát huy tác dụng thường xuyên đến mức nào trong các truy vấn thực tế. Đặc biệt, thay đổi “dùng Limit thay vì Unique để triển khai DISTINCT khi có thể” có cảm giác chỉ áp dụng cho những truy vấn cực kỳ ngớ ngẩn
  Tôi cũng tò mò liệu các nhà phát triển PostgreSQL có nguồn thông tin nào để đánh giá điều này không

  • Tôi nghĩ nó sẽ có tác dụng khá thường xuyên. DISTINCT là thứ các lập trình viên ít kinh nghiệm hay thêm vào để cố sửa các truy vấn kém, và thông thường việc đầu tiên khi bắt đầu cải thiện hiệu năng là viết lại truy vấn để không cần đến nó nữa
    Nếu cải tiến DISTINCT giúp hệ thống vững hơn trước các truy vấn kém thì lợi ích thu được là lớn. Nó sẽ không sửa được mọi vấn đề, nhưng bất kỳ cải tiến nào cũng đáng hoan nghênh
  • Với tư cách là tác giả bài blog và cũng là người viết tính năng này, trường hợp này thực ra đã xuất hiện trên mailing list pgsql-hackers
    Tôi đồng ý rằng khả năng được áp dụng thường xuyên là thấp, nhưng điểm hay là việc phát hiện xem có thể áp dụng hay không đơn giản như kiểm tra một con trỏ có phải NULL hay không
    Việc phát hiện rất đơn giản và phần lớn trường hợp sẽ không áp dụng được, nhưng trong những trường hợp có thể áp dụng thì nó có thể đem lại cải thiện hiệu năng đáng kể
  • Vấn đề là ORM có thói quen tạo ra những truy vấn rất ngớ ngẩn, còn các lập trình viên thì từ chối sửa bằng cách tự viết SQL vì cảm thấy như thế không “thuần khiết” cho lắm
    Có lẽ đây không phải vấn đề cực kỳ phổ biến, nhưng nếu thỉnh thoảng xuất hiện thì tôi cũng không ngạc nhiên
  • Ở công ty cũ, vì lý do legacy, bảng người dùng cho phép địa chỉ email bị trùng, nhưng chúng tôi không muốn thêm các bản ghi trùng mới, nên trước khi tạo người dùng mới đã chạy truy vấn select distinct email from users where email = ?
    Tôi nghĩ số hàng có cùng email không vượt quá 100. Phần lớn là người dùng thử nghiệm lẽ ra có thể xóa, nhưng chuyện hơi lạc đề rồi

• Tôi ước PostgreSQL có chế độ nghiêm ngặt để kiểm thử ứng dụng. Đó sẽ là một chế độ chỉ nhìn vào bản thân truy vấn, độc lập với thống kê, trong đó nếu có một chỉ mục giúp truy vấn cải thiện về mặt tiệm cận nhưng chỉ mục đó lại không tồn tại thì trả về lỗi
  Cũng sẽ tốt nếu có lệnh CREATE INDICES FOR để tạo các chỉ mục đó cho việc nâng cấp ứng dụng, và có cả chế độ tự động tạo chỉ mục cho mục đích tương tác/phát triển
  Nhìn chung, hệ thống nên được thiết kế để việc thực thi không tối ưu về mặt tiệm cận tuyệt đối không bao giờ xảy ra

• Tôi không hiểu vì sao họ không triển khai hint

  • Có extension pg_hint_plan. Rủi ro của hint là dù đúng ở thời điểm viết, nó có thể trở nên tệ hơn khi kích thước bảng hoặc độ lệch dữ liệu thay đổi
    Lần trước khi xem thảo luận về hint, tôi nhớ là không có phản đối chung nếu đó là cách không ràng buộc planner quá chặt và vẫn có thể thích ứng với các thay đổi dữ liệu cơ bản
    Ví dụ, thay vì chỉ định rằng một predicate cụ thể khớp với 10 hàng, ta báo cho hệ thống biết rằng có tương quan giữa hai cột
  • Thảo luận liên quan: Why PostgreSQL doesn't have query hints
    https://news.ycombinator.com/item?id=2179433 (60 bình luận, năm 2011)
    Lập trường chính thức trên wiki PostgreSQL nằm ở https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion
    Lập trường là “không quan tâm đến hint theo đúng cách thường được triển khai trong các cơ sở dữ liệu khác”
    Các vấn đề của hệ thống hint hiện có được nêu gồm làm giảm khả năng bảo trì mã ứng dụng, cản trở nâng cấp, khuyến khích thói quen DBA xấu, và không phù hợp khi quy mô dữ liệu mở rộng
    Tôi không muốn trách lập trường đó, nhưng thật bực bội khi PostgreSQL chọn một kế hoạch ngớ ngẩn mà ta lại không thể thuyết phục nó đưa ra lựa chọn hợp lý

• Một người bạn làm Microsoft DBA cho các doanh nghiệp tầm trung nói rằng không thể làm việc nghiêm túc với PostgreSQL. Thậm chí anh ấy còn bảo đã sốc khi biết PostgreSQL không có query planner
  Tạm gác chuyện chế giễu sang một bên, tôi tò mò liệu lập luận lớn hơn rằng MSSQL có thể xử lý quy mô mà PostgreSQL không phù hợp có hợp lý chút nào không. Trực giác thì thấy vô lý, nhưng tôi hoàn toàn không phải DBA

  • Ở khía cạnh đó thì có. Nếu cần một cơ sở dữ liệu xử lý đủ tốt gần như bất cứ thứ gì bạn cần, MSSQL và Oracle có khả năng làm được
    Họ đã giải quyết bằng cách đổ tiền và phần cứng, tức là thêm tiền, vào cho đến khi vấn đề được xử lý. Tất nhiên cũng có công nghệ thông minh trong đó, nhưng về cơ bản đã có nhiều kỹ sư hơn rất nhiều được投入 trong thời gian dài
    Chúng có thể mở rộng theo chiều ngang lớn hơn mức PostgreSQL có thể làm một cách hợp lý
    Tuy vậy PostgreSQL cũng đang bắt kịp, và MySQL/MariaDB có thể nói là từ trước đến nay vẫn khá ổn ở mảng này. Các lựa chọn mở rộng theo chiều ngang đang ngày càng tốt hơn
    Giờ đây cũng dễ hơn để vận hành các cụm PostgreSQL nhiều terabyte trên số lượng máy ít, xử lý lưu lượng lớn, và đưa “big data” vào các cơ sở dữ liệu chuyên biệt hơn. Cách cũ là nhồi mọi thứ vào MSSQL/Oracle có thể đã hơi lỗi thời
  • Tôi đã phát triển MSSQL khá nhiều, và PostgreSQL có một số thiếu sót hơi đáng ngạc nhiên
    Điều bạn của bạn nói có thể là việc PostgreSQL không có cách cache hoặc cố định query plan. PostgreSQL sẽ lập kế hoạch lại cho mỗi câu lệnh trừ khi dùng câu lệnh được chuẩn bị thủ công, và việc đó cũng chỉ hoạt động theo từng kết nối
    MSSQL đã cache và tái sử dụng plan từ lâu, nên planner có thể dành nhiều thời gian hơn cho việc lập kế hoạch. Nó cũng có hint và có thể cố định plan
    PostgreSQL thật sự cần hint. Dù optimizer rất tốt, đôi khi tôi biết rõ hơn và muốn buộc nó nghe theo mình
    Ngoài ra PostgreSQL không có clustered index thật sự và mọi bảng đều là heap. Trong MSSQL, phần lớn mọi người dùng nó thường xuyên, thường đặt khóa chính làm clustered index để bản thân bảng trở thành index và không có tham chiếu gián tiếp khi tra cứu theo khóa
    Thú vị là SQLite thì ngược lại: bảng luôn có clustered index, dù bạn có tạo hay không; còn MSSQL cho phép chọn giữa heap và bảng tổ chức theo index
  • PostgreSQL có query planner. Toàn bộ bài này nói về những cải tiến của nó. Vì vậy có vẻ là giao tiếp bị hiểu sai, hoặc bạn của bạn hoàn toàn không biết PostgreSQL
    Có những trường hợp cơ sở dữ liệu PostgreSQL rất lớn vẫn hoạt động tốt, nên PostgreSQL chắc chắn cũng có thể mở rộng
    Tuy nhiên SQL Server cũng có các tính năng mà PostgreSQL không có, và nếu những tính năng đó quan trọng thì nó có thể phù hợp hơn cho một số use case nhất định. Rốt cuộc đây là các cơ sở dữ liệu khác nhau, với điểm mạnh và điểm yếu khác nhau
  • Tôi đã dùng cả hai cho cả OLTP lẫn data warehousing, và cả hai đều ổn
    Ban đầu tôi định viết rằng nếu không phải vì các ứng dụng do vendor cung cấp yêu cầu SQL Server, tôi đã khuyến nghị công ty chuyển sang PostgreSQL
    Nhưng rồi tôi nhận ra sẽ phải thay thế nhiều thứ đến mức nào trong những thứ Microsoft cung cấp kèm, như reporting services, integration services, jobs, tích hợp AD, service broker. notify/listen không có kiểu thông điệp
    Chúng tôi không còn dùng analysis services nữa, nhưng hồi còn dùng thì thứ đó cũng sẽ rất khó thay thế
    Những thứ này giữ chân người ta. Tôi thậm chí không hình dung được sẽ mất bao lâu để thay thế tất cả, và bỏ ra 1 năm chỉ để thay thế những thứ đã có sẵn thì tỷ suất lợi nhuận trên đầu tư không tốt
  • Aurora của AWS có vẻ xử lý khá tốt, và hướng tới việc trở thành lựa chọn thay thế drop-in cho PostgreSQL và MySQL

• Tôi thắc mắc tại sao nội dung này được công bố trên citusdata chứ không phải postgresql.org. Không rõ đây là tính năng chỉ dành cho bản trả phí hay là bổ sung mã nguồn mở

  • Vì tác giả làm việc tại Citus Data, và một số tối ưu hóa đó cũng do chính tác giả viết

• Khi nào thì có thể dùng index để tăng tốc truy vấn IS NOT DISTINCT FROM nhỉ ;)