So sánh các món VIEWS, PROCEDURE, FUNCTION, CURSOR trong PostgreSQL

SO SÁNH TỔNG QUÁT HIỆU SUẤT CÁC TIỆN ÍCH TRONG POSTGRES

(VIEWS, PROCEDURE, FUNCTION, CURSOR,...)

Các tập dữ liệu

+ Tập dữ liệu nhỏ:

• Customer: 599
• Address: 603
• City: 600
• Country: 109

+ Tập dữ liệu lớn:

• Customer: 1599
• Address: 1603
• City: 1600
• Country: 1109

1. so sánh giữa view vs martiriazlize view

Tạo một view và một materialized view từ bảng customer và address.

-- Tạo View
DROP VIEW IF EXISTS customer_with_address;
CREATE VIEW customer_with_address AS
SELECT c.customer_id, c.first_name, c.last_name, ci.city, co.country
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country co ON ci.country_id = co.country_id;
-- Tạo Materialized View
DROP MATERIALIZED VIEW IF EXISTS customer_with_address_mat;
CREATE MATERIALIZED VIEW customer_with_address_mat AS
SELECT c.customer_id, c.first_name, c.last_name, ci.city, co.country
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country co ON ci.country_id = co.country_id;

Ta sẽ thực hiện 2 câu query sau để test:

-- Thực hiện query
EXPLAIN ANALYSE SELECT * FROM customer_with_address WHERE last_name = 'Smith';
EXPLAIN ANALYSE SELECT * FROM customer_with_address_mat WHERE last_name = 'Smith';

Kết quả test tập dữ liệu nhỏ:

Views thường:

Materialize view:

Kết quả tập dữ liệu lớn

View thường:

2. so sánh giữa function bình thường và function có dùng cursor

Tạo hai function: một không dùng CURSOR và một dùng CURSOR.

-- Function không dùng CURSOR
CREATE OR REPLACE FUNCTION get_customers_normal()
RETURNS TABLE(customer_id INT, first_name VARCHAR, last_name VARCHAR, city VARCHAR, country VARCHAR) AS $$
BEGIN
RETURN QUERY
SELECT c.customer_id, c.first_name, c.last_name, ci.city, co.country
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country co ON ci.country_id = co.country_id;
END; $$ LANGUAGE plpgsql;

-- FUNCTION dùng CURSOR

CREATE OR REPLACE FUNCTION get_customers_cursor()
RETURNS TABLE(customer_id INT, first_name VARCHAR, last_name VARCHAR, city VARCHAR, country VARCHAR) AS $$
DECLARE
customer_cur CURSOR FOR
SELECT c.customer_id, c.first_name, c.last_name, ci.city, co.country
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country co ON ci.country_id = co.country_id;
BEGIN
OPEN customer_cur;
LOOP
FETCH customer_cur INTO customer_id, first_name, last_name, city, country;
EXIT WHEN NOT FOUND;
RETURN NEXT;
END LOOP;
CLOSE customer_cur;
END; $$ LANGUAGE plpgsql;

Ta chạy 2 câu query sau để test:

EXPLAIN ANALYSE SELECT * FROM get_customers_normal();
EXPLAIN ANALYSE SELECT * FROM get_customers_cursor();

Test trên tập nhỏ:

+ Không dùng cursor:

+ Dùng cursor:

Test trên tập lớn:

+ Không dùng cursor:

+ Dùng cursor:

Tuy nhiên, phía trên là chạy lúc đầu, có thể do đã được cache do query sau nên dùng cursor sẽ có execution time nhanh hơn, dưới đây là query lại lần nữa:

+ Không dùng cursor

+ Dùng cursor:

Kết quả gần như tương tự nhau, mặc dùng dùng cursor sẽ nhỉnh hơn một chút.

3. so sánh giữa dùng view vs martiriazlize view vs function dùng cursor vs dunction không dùng cursor vs dùng procedure

4. so sánh 3 câu query: join với table, join với function, join với view

Đầu tiên ta tạo mới function và view như sau:

CREATE OR REPLACE VIEW customer_address_view AS
SELECT c.customer_id, c.first_name, c.last_name, a.address
FROM customer c
JOIN address a ON c.address_id = a.address_id;
CREATE OR REPLACE FUNCTION get_customers_with_address()
RETURNS TABLE(customer_id INT, first_name VARCHAR, last_name VARCHAR, address VARCHAR) AS $$
BEGIN
RETURN QUERY
SELECT c.customer_id, c.first_name, c.last_name, a.address
FROM customer c
JOIN address a ON c.address_id = a.address_id;
END;

$$
LANGUAGE plpgsql;

Test với tập nhỏ

Join với bảng bình thường:

EXPLAIN ANALYSE SELECT c.customer_id, c.first_name, c.last_name, subquery.address
FROM (
SELECT c.customer_id, c.first_name, c.last_name, a.address
FROM customer c
JOIN address a ON c.address_id = a.address_id
) subquery
JOIN customer c ON subquery.customer_id = c.customer_id;

Join với view

EXPLAIN ANALYZE SELECT c.customer_id, c.first_name, c.last_name, v.address FROM customer c JOIN customer_address_view v ON c.customer_id = v.customer_id;

Join với function

EXPLAIN ANALYZE SELECT c.customer_id, c.first_name, c.last_name, f.address FROM customer c JOIN get_customers_with_address() f ON c.customer_id = f.customer_id;

Test với tập lớn

Join với bảng bình thường:

EXPLAIN ANALYSE SELECT c.customer_id, c.first_name, c.last_name, subquery.address
FROM (
SELECT c.customer_id, c.first_name, c.last_name, a.address
FROM customer c
JOIN address a ON c.address_id = a.address_id
) subquery
JOIN customer c ON subquery.customer_id = c.customer_id;

Join với view

EXPLAIN ANALYZE
SELECT c.customer_id, c.first_name, c.last_name, v.address
FROM customer c
JOIN customer_address_view v ON c.customer_id = v.customer_id;

Join với function

EXPLAIN ANALYZE
SELECT c.customer_id, c.first_name, c.last_name, f.address
FROM customer c
JOIN get_customers_with_address() f ON c.customer_id = f.customer_id;

Tại sao function lại nhanh hơn 2 cái còn lại?

Điểm khác biệt nằm ở đây:

Về bản chất thì view và native query thì y như nhau, nên ta sẽ so sánh view với function

Dưới đây là một số yếu tố quan trọng dẫn đến sự khác biệt về thời gian thực thi:

1. Loại kế hoạch thực thi:

• Function: Sử dụng loại kế hoạch thực thi là Function Scan, cho phép PostgreSQL xử lý truy vấn trong một ngữ cảnh tách biệt và tối ưu hóa function như một khối mã đã được lập kế hoạch trước đó. Trong trường hợp này, truy vấn trong function không cần tính toán lại chi phí cho các phần của truy vấn (như join hay scan), giúp tiết kiệm thời gian.
• View: Sử dụng loại kế hoạch Hash Join. Trong trường hợp này, PostgreSQL phải tính toán kế hoạch join hai bảng customer và address mỗi khi view được truy vấn. Thao tác join bao gồm việc tính toán hash table cho một bảng và thực hiện quét tuần tự (sequential scan) cả hai bảng, dẫn đến chi phí cao hơn.

2. Planning Time (Thời gian lập kế hoạch):

• Function: Có thời gian lập kế hoạch là 0.025 ms, rất nhanh vì function đã được lập kế hoạch từ trước. PostgreSQL chỉ cần thực thi kế hoạch đã được lưu.
• View: Cần thời gian lập kế hoạch là 0.153 ms, lâu hơn nhiều so với function. Điều này xảy ra do PostgreSQL cần tính toán lại cách thực hiện join cho hai bảng mỗi lần view được gọi.

3. Cơ chế thực thi:

• Function Scan trong function có chi phí thấp hơn vì PostgreSQL chỉ đơn giản là thực thi truy vấn đã được lưu sẵn, không cần phân tích lại cách join hoặc scan dữ liệu.
• Hash Join trong view yêu cầu PostgreSQL phải tính toán lại cấu trúc hash table cho bảng address, sau đó thực hiện việc join. Việc này tiêu tốn nhiều tài nguyên hơn (như bộ nhớ và CPU), dẫn đến thời gian thực thi cao hơn.

4. Execution Time (Thời gian thực thi):

• Function: Có tổng thời gian thực thi là 4.829 ms.
• View: Có tổng thời gian thực thi là 12.653 ms. Sự chênh lệch này chủ yếu đến từ việc PostgreSQL phải xử lý join và scan trong view.

5. Số lượng buffer hits:

• Cả hai truy vấn đều có số lượng buffer hits tương đương (43 hits). Điều này cho thấy cả hai truy vấn đều sử dụng dữ liệu từ bộ nhớ đệm, nhưng cách tổ chức và thực thi khác nhau dẫn đến thời gian thực hiện khác nhau.

Còn query native thì cũng tuơng tự, nên không cần bàn

Vậy nếu sử dụng materizlize view thì sao?

Có thể thấy matetialize view cho thời gian thực thi nhanh nhất, bởi vì đơn giản nó tạo và gom sẵn các trường của nhiều bảng khác vào chung một bảng vật lý, nên khi query nó không cần phải join gì cả.

5. So sánh cùng 1 query có group by, where so sánh giữa dùng query native, view, function

Test trên tập nhỏ

Native query:

-- native query EXPLAIN ANALYZE SELECT co.country, COUNT(c.customer_id) FROM customer c JOIN address a ON c.address_id = a.address_id JOIN city ci ON a.city_id = ci.city_id JOIN country co ON ci.country_id = co.country_id WHERE co.country = 'USA' GROUP BY co.country;

View query

Function query

Test trên tập lớn

Native query:

-- native query
EXPLAIN ANALYZE
SELECT co.country, COUNT(c.customer_id)
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country co ON ci.country_id = co.country_id
WHERE co.country = 'USA'
GROUP BY co.country;

View query

Function query

Vậy còn materialize view, không cần phải nói chắc chắn nó sẽ là cái nhanh nhất:

Tổng kết:

• Function: Thích hợp cho logic phức tạp và tính toán, có thể tối ưu hóa tốt hơn trong một số trường hợp.
• View: Dễ sử dụng để đơn giản hóa truy vấn, nhưng hiệu suất có thể kém hơn vì phải lập kế hoạch lại mỗi lần.
• Materialized View: Tối ưu hóa cho hiệu suất trong các truy vấn mà không cần phải thực hiện lại phép toán join mỗi lần.
• Function với Cursor: Hữu ích khi cần xử lý nhiều hàng trong một truy vấn lớn, nhưng có thể chậm hơn nếu cần khởi tạo cursor cho từng hàng.