Trong các hệ quản trị cơ sở dữ liệu MogDB và openGauss, bộ tối ưu hóa truy vấn (Query Optimizer) cung cấp cơ chế linh hoạt thông qua tham số rewrite_rule. Một trong những quy tắc quan trọng của tham số này là uniquecheck, được thiết kế để cải thiện hiệu năng khi xử lý các truy vấn con (subquery) có tương quan trong mệnh đề WHERE mà không chứa hàm tập hợp.

Theo mặc định, khi optimizer gặp truy vấn con phụ thuộc vào bảng ngoài, nó thường áp dụng chiến lược Nested Loop Filter. Cách tiếp cận này an toàn nhưng kém hiệu quả trên các tập dữ liệu quy mô lớn, vì truy vấn con sẽ được thực thi lặp lại cho từng dòng của bảng dẫn đầu.

Xét tình huống sau với hai bảng orders_main (bảng chính chứa hàng triệu bản ghi) và customer_ref (bảng tham chiếu):

postgres=# explain analyze select count(*)
  from orders_main om
 where om.product_sku =
       (select product_sku
          from customer_ref cr
         where om.client_id = cr.client_id);

Kết quả phân tích thực thi cho thấy bảng orders_main bị quét tuần tự, và mỗi dòng đều kích hoạt SubPlan để tra cứu trên customer_ref. Với hơn 40 triệu dòng dữ liệu, truy vấn con được thực hiện hàng chục triệu lần, dẫn đến tổng thời gian xử lý lên tới khoảng 122 giây:

Aggregate  (cost=369500968.48..369500968.49 rows=1 width=20) (actual time=122494.785..122494.785 rows=1 loops=1)
  ->  Seq Scan on orders_main om  (cost=0.00..369500411.84 rows=222656 width=12) (actual time=0.912..121981.180 rows=4696064 loops=1)
        Filter: (product_sku = (SubPlan 1))
        Rows Removed by Filter: 39841280
        SubPlan 1
          ->  Index Scan using idx_cr_clientid on customer_ref cr  (cost=0.00..8.27 rows=1 width=6) (actual time=94019.753..99841.735 rows=44537344 loops=44537344)
                Index Cond: (om.client_id = client_id)
Total runtime: 122494.942 ms

Để khắc phục tình trạng này, ta có thể kích hoạt quy tắc rewrite bằng cách gán giá trị uniquecheck cho tham số rewrite_rule. Khi áp dụng, optimizer sẽ cố gắng "nâng" (pull-up) truy vấn con ra khỏi mệnh đề WHERE và chuyển đổi thành thao tác Hash Join:

postgres=# explain analyze select /*+ set(rewrite_rule uniquecheck) */ count(*)
  from orders_main om
 where om.product_sku =
       (select product_sku
          from customer_ref cr
         where om.client_id = cr.client_id);

Lúc này, kế hoạch thực thi thay đổi hoàn toàn. Thay vì lặp lại việc quét chỉ mục, bảng customer_ref chỉ được truy vấn một lần duy nhất, sau đó kết hợp với orders_main thông qua Hash Join. Thời gian thực thi giảm mạnh còn khoảng 10 giây:

Aggregate  (cost=1467229.99..1467230.00 rows=1 width=8) (actual time=10758.577..10758.577 rows=1 loops=1)
  ->  Hash Join  (cost=5660.88..1467225.97 rows=1608 width=0) (actual time=66.574..10424.858 rows=4696064 loops=1)
        Hash Cond: ((om.client_id = subquery."?column?") AND (om.product_sku = subquery.product_sku))
        ->  Seq Scan on orders_main om  (cost=0.00..1227776.53 rows=44531153 width=12) (actual time=0.003..3561.282 rows=44537344 loops=1)
        ->  Hash  (cost=4356.08..4356.08 rows=86987 width=12) (actual time=65.462..65.462 rows=9172 loops=1)
               Buckets: 131072  Batches: 1  Memory Usage: 1427kB
              ->  Subquery Scan on subquery  (cost=2616.34..4356.08 rows=86987 width=12) (actual time=33.027..54.531 rows=86987 loops=1)
                    ->  HashAggregate  (cost=2616.34..3486.21 rows=86987 width=12) (actual time=33.025..47.518 rows=86987 loops=1)
                          Group By Key: cr.client_id
                          Unique Check Required
                          ->  Seq Scan on customer_ref cr  (cost=0.00..2398.87 rows=86987 width=12) (actual time=0.005..9.467 rows=86987 loops=1)
Total runtime: 10758.989 ms

Dòng ghi chú Unique Check Required trong kế hoạch thực thi phản ánh cơ chế kiểm tra nghiêm ngặt của quy tắc này. Hệ thống sẽ xác minh xem cột kết nối trong truy vấn con (cr.client_id) có đảm bảo tính duy nhất hay không. Nếu tồn tại nhiều bản ghi trả về cho cùng một giá trị khóa, toán tử = sẽ vi phạm quy tắc một đối một, và cơ sở dữ liệu ngay lập tức từ chối thực thi kèm thông báo lỗi:

postgres=# insert into customer_ref select * from customer_ref;
INSERT 0 86987
postgres=# explain analyze select /*+ set(rewrite_rule uniquecheck) */ count(*)
  from orders_main om
 where om.product_sku =
       (select product_sku
          from customer_ref cr
         where om.client_id = cr.client_id);
ERROR:  more than one row returned by a subquery used as an expression

Một điểm đáng lưu ý khác là quy tắc uniquecheck chỉ cần thiết cho các truy vấn con dạng biểu thức đơn lẻ không có hàm tổng hợp. Khi truy vấn con sử dụng các hàm như MAX(), MIN() hoặc AVG(), optimizer của MogDB/openGauss đã tự động nhận diện tính chất xác định của chúng và tự động thực hiện việc nâng cấp cấu trúc truy vấn sang dạng Join mà không yêu cầu can thiệp thủ công từ tham số rewrite_rule.

postgres=# explain analyze select count(*)
  from orders_main om
 where om.product_sku =
       (select max(product_sku)
          from customer_ref cr
         where om.client_id = cr.client_id);