🥇Antipatterns của PostgreSQL: JOIN và OR có hại

Cảnh giác với các hoạt động mang lại bộ đệm...
Lấy một truy vấn nhỏ làm ví dụ, chúng ta hãy xem xét một số phương pháp phổ biến để tối ưu hóa các truy vấn trong PostgreSQL. Việc bạn có sử dụng chúng hay không là tùy thuộc vào bạn, nhưng bạn nên biết về chúng.

Trong một số phiên bản tiếp theo của PG, tình huống có thể thay đổi khi bộ lập lịch trở nên thông minh hơn, nhưng đối với 9.4/9.6, nó trông gần giống như trong các ví dụ ở đây.

Hãy đưa ra một yêu cầu rất thực tế:

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

về tên bảng và trườngTên trường và bảng "tiếng Nga" có thể được xử lý khác nhau, nhưng đây là vấn đề về sở thích. Bởi vì ở đây tại Tensor không có nhà phát triển nước ngoài nào và PostgreSQL cho phép chúng tôi đặt tên ngay cả bằng chữ tượng hình, nếu họ kèm theo dấu ngoặc kép, thì chúng ta muốn đặt tên các đối tượng một cách rõ ràng và rõ ràng để không có sự khác biệt.
Hãy xem kế hoạch kết quả:
Antipatterns của PostgreSQL: THAM GIA và OR có hại
[xem giải thích.tensor.ru]

Bộ đệm 144ms và gần 53K - tức là hơn 400MB dữ liệu! Và chúng tôi sẽ may mắn nếu tất cả chúng đều nằm trong bộ đệm vào thời điểm chúng tôi yêu cầu, nếu không sẽ mất nhiều thời gian hơn khi đọc từ đĩa.

Thuật toán là quan trọng nhất!

Để bằng cách nào đó tối ưu hóa bất kỳ yêu cầu nào, trước tiên bạn phải hiểu nó nên làm gì.
Bây giờ chúng ta hãy để việc phát triển cấu trúc cơ sở dữ liệu nằm ngoài phạm vi của bài viết này và đồng ý rằng chúng ta có thể tương đối “rẻ” viết lại yêu cầu và/hoặc lăn lên đế một số thứ chúng ta cần Chỉ số.

Vì vậy, yêu cầu:
- kiểm tra sự tồn tại của ít nhất một số tài liệu
- trong điều kiện chúng ta cần và thuộc một loại nhất định
- nơi tác giả hoặc người biểu diễn là nhân viên chúng tôi cần

THAM GIA + GIỚI HẠN 1

Thông thường, nhà phát triển sẽ dễ dàng hơn khi viết một truy vấn trong đó một số lượng lớn các bảng được nối lần đầu tiên và sau đó chỉ còn lại một bản ghi trong toàn bộ tập hợp này. Nhưng dễ dàng hơn cho nhà phát triển không có nghĩa là hiệu quả hơn cho cơ sở dữ liệu.
Trong trường hợp của chúng tôi chỉ có 3 bảng - và tác dụng là gì...

Trước tiên chúng ta hãy loại bỏ kết nối với bảng "Loại tài liệu", đồng thời thông báo cho cơ sở dữ liệu rằng bản ghi loại của chúng tôi là duy nhất (chúng tôi biết điều này, nhưng người lên lịch chưa biết):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

Có, nếu bảng/CTE bao gồm một trường của một bản ghi, thì trong PG bạn thậm chí có thể viết như thế này, thay vì

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

Đánh giá lười biếng trong các truy vấn PostgreSQL

BitmapOr so với UNION

Trong một số trường hợp, Bitmap Heap Scan sẽ khiến chúng tôi tốn rất nhiều chi phí - ví dụ: trong trường hợp của chúng tôi, khi có khá nhiều bản ghi đáp ứng điều kiện bắt buộc. Chúng tôi có được nó bởi vì Điều kiện OR được chuyển thành BitmapOr- Vận hành theo kế hoạch.
Hãy quay lại vấn đề ban đầu - chúng ta cần tìm bản ghi tương ứng cho bất kỳ từ các điều kiện - nghĩa là không cần phải tìm kiếm tất cả các bản ghi 59K trong cả hai điều kiện. Có một cách để giải quyết một điều kiện, và chỉ chuyển sang phần thứ hai khi không tìm thấy gì ở phần đầu tiên. Thiết kế sau đây sẽ giúp chúng ta:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

GIỚI HẠN 1 “Bên ngoài” đảm bảo rằng việc tìm kiếm kết thúc khi tìm thấy bản ghi đầu tiên. Và nếu nó đã được tìm thấy ở khối đầu tiên thì khối thứ hai sẽ không được thực thi (không bao giờ thực hiện đôi vơi).

“Ẩn điều kiện khó khăn trong CASE”

Có một thời điểm cực kỳ bất tiện trong truy vấn ban đầu - kiểm tra trạng thái đối với bảng liên quan “DocumentExtension”. Bất kể sự đúng đắn của các điều kiện khác trong biểu thức (ví dụ: d.“Đã xóa” KHÔNG ĐÚNG), kết nối này luôn được thực thi và “tiêu tốn tài nguyên”. Số tiền đó sẽ được chi tiêu nhiều hay ít - tùy thuộc vào kích thước của bảng này.
Nhưng bạn có thể sửa đổi truy vấn để việc tìm kiếm bản ghi liên quan chỉ diễn ra khi thực sự cần thiết:

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

Một lần từ bảng được liên kết với chúng tôi không có trường nào là cần thiết cho kết quả, thì chúng ta có cơ hội biến THAM GIA thành một điều kiện trong truy vấn phụ.
Hãy để các trường được lập chỉ mục “bên ngoài dấu ngoặc CASE”, thêm các điều kiện đơn giản từ bản ghi vào khối WHEN - và bây giờ truy vấn “nặng” chỉ được thực thi khi chuyển đến THEN.

Họ của tôi là "Total"

Chúng tôi thu thập truy vấn kết quả bằng tất cả các cơ chế được mô tả ở trên:

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

Điều chỉnh [thành] chỉ mục

Một con mắt được huấn luyện nhận thấy rằng các điều kiện được lập chỉ mục trong các khối con UNION hơi khác nhau - điều này là do chúng ta đã có các chỉ mục phù hợp trên bảng. Và nếu chúng không tồn tại thì đáng để tạo ra: Tài liệu(Person3, DocumentType) и Tài liệu(Loại tài liệu, Nhân viên).
về thứ tự các trường trong điều kiện ROWTất nhiên, từ quan điểm của người lập kế hoạch, bạn có thể viết (A, B) = (constA, constB)Và (B, A) = (constB, constA). Nhưng khi ghi âm theo thứ tự của các trường trong chỉ mục, yêu cầu như vậy sẽ thuận tiện hơn cho việc gỡ lỗi sau này.
Có gì trong kế hoạch?
Antipatterns của PostgreSQL: THAM GIA và OR có hại
[xem giải thích.tensor.ru]

Thật không may, chúng tôi đã không may mắn và không tìm thấy gì trong khối UNION đầu tiên, vì vậy khối thứ hai vẫn được thực thi. Nhưng ngay cả như vậy - chỉ 0.037ms và 11 bộ đệm!
Chúng tôi đã tăng tốc yêu cầu và giảm việc bơm dữ liệu trong bộ nhớ vài nghìn lần, sử dụng các kỹ thuật khá đơn giản - một kết quả tốt chỉ với một chút thao tác sao chép-dán. 🙂

Nguồn: www.habr.com