Optimize SQL

Ví dụ sử dụng EXPLAIN ANALYZE và đọc dữ liệu plan

EXPLAIN ANALYZE SELECT * FROM ENGINEER WHERE first_name = ‘Dominique’ AND last_name LIKE ‘%a%’;

Chú ý cách đọc plan sẽ đi từ trong ra ngoài, không phải từ trên xuống dưới:

Step đầu tiên dễ dàng nhận thấy là index scan với điều kiện first_name = Dominique trước, lọc ra đc 58 rows.
Sau đó thực hiện filter với cả 2 điều kiện first_name = Dominique và last_name LIKE %a%, loại bỏ được 43 rows.
Kết quả thu được 15 rows với thời gian chưa đến 1 ms.

Giải thích thuật ngữ

+ Seq Scan: là quét tuần tự toàn bộ table
+ Bitmap Heap Scan: nghĩa là PostgreSQL đã tìm thấy một tập hợp con nhỏ các hàng để tìm nạp (ví dụ: từ một chỉ mục) và sẽ chỉ tìm nạp những hàng đó. Và như vậy trong các trường hợp mà dữ liệu lớn, hoặc PostgreSQL cho rằng là nó nên như thế, thì thao tác này sẽ nhanh hơn
+ Index Scan:
+ Index: Một Index là một cấu trúc on-disk liên quan tới 1 bảng hoặc view nhằm mục đích tăng tốc độ đọc dữ liệu. Một Index bao gồm các key được lấy từ các cộng trong table hoặc view. Những key này được lưu theo cấu trúc (B-tree) cho phép SQL Server có thể tìm thấy các row liên quan một cách nhanh chóng và hiệu quả nhất. Có 2 loại Index là Clustered Index và Non Clustered Index.
+ Index Cond:
+ B-Tree Index (Balanced Tree index) :
+ Bitmap Index:
+ Nested Loop: vòng lặp lồng nhau

Từ khóa

B-Tree index.
Bitmap index.
Hash index.

GIST.
SP-GIST.
GIN.
BRIN.

Seq Scan và Bitmap Heap Scan
Không gian tìm kiếm
Độ phức tạp
Giảm stress cho CPU

Vài chú ý khi sử dụng B-Tree index:

Không có tác dụng khi tìm kiếm text sử dụng điều kiện LIKE ‘%%’. Index nữa index mãi thì tốc độ tìm kiếm.. không thay đổi (chung thủy thật), tốc độ insert còn tăng lên.
Với composite index, các điều kiện cần match full value hoặc match mostleft column để đạt hiệu quả tối đa.

Hỏi và trả lời

Có nên đánh index cho các cột có ít giá trị (low cardinality), ví dụ như cột giới tính?
Câu trả lời của mình như sau:

Bản chất của việc indexing vẫn là seq scan (dựa vào BST) nhưng trên table index. Mục đích giảm không gian tìm kiếm từ table chính xuống table index, từ đó cải thiện tốc độ query.
Do đó, nếu chỉ indexing cho column gender thì không gian tìm kiếm giảm không đáng kể. Ví dụ gender có 2 giá trị, giảm từ N xuống N/2, complexity vẫn là O(N). Ngoài ra còn thời gian truy cập vào main table để fetch data. Nếu indexing vẫn có khả năng giảm thời gian tìm kiếm.
Cần dựa trên nhu cầu thực tế, có query nào dùng điều kiện với column đó không để tạo index kết hợp với index khác hoặc composite index.

# REF

https://2kvn.com/002-hieu-ve-index-de-tang-performance-voi-postgresql-p1-p5f32363837

PostgreSQL Performance Tuning Tutorial

https://wiki.postgresql.org/wiki/Performance_Optimization

https://www.postgresql.org/docs/8.2/sql-explain.html

https://thoughtbot.com/blog/reading-an-explain-analyze-query-plan

https://helpex.vn/question/su-khac-biet-giua-seq-scan-va-bitmap-heap-scan-trong-postgres-la-gi-6098e80d853bc933ca57859b

https://viblo.asia/p/su-khac-nhau-giua-sql-server-clustered-index-scan-va-index-seed-RQqKLOvN57z

https://2kvn.com/003-hieu-ve-index-de-tang-performance-voi-postgresql-p2-p5f32373030

Series

https://viblo.asia/s/performance-optimization-voi-postgresql-OVlYq8oal8W

Bài viết khác

Laravel

Laravel là gì? Laravel là một framework PHP mã nguồn mở miễn phí được phát hành lần đầu tiên vào năm 2011. Taylor Otwell đã tạo ra Laravel nhằm giúp các Developer xây dựng các ứng dụng web bằng cách sử dụng mẫu kiến ​​trúc Model-View-Controller (MVC). Mô hình Model-View-Controller (MVC) trong Laravel: 1. Model […]

Đọc thêm

PHP

PHP là gì? PHP là từ viết tắt của thuật ngữ Personal Home Page và hiện nay đang có tên Hypertext Preprocessor. Đây là một dạng mã lệnh hoặc một chuỗi ngôn ngữ kịch bản. Trong đó, ngôn ngữ PHP chủ yếu được phát triển để dành cho những ứng dụng nằm trên máy chủ. Mỗi […]

Đọc thêm

Build for global scale: AFK scale cube and basic rule to build an application for global scale

REF https://akfpartners.com/growth-blog/scale-cube

Đọc thêm

PostgreSQL : subquery, CTE

What is subquery in PostgreSQL? In PostgreSQL, a subquery is a query that is nested inside another query. The subquery is executed first, and its results are used as input to the outer query. Subqueries can be used in various contexts, such as in the SELECT, WHERE, and HAVING clauses of a query. For example, […]

Đọc thêm

Optimize SQL : rule and todo list

Some rule and todo list to Optimize SQL REF https://www.pgmustard.com/blog/indexing-best-practices-postgresql

Đọc thêm

PostgreSQL Compound indexes

What is Compound indexes in PostgreSQL? A compound index (also known as a composite index or a multi-column index) refers to an index that is created on two or more columns of a table. It allows PostgreSQL to quickly find rows that match a query condition based on the values in multiple columns, which can […]

Đọc thêm