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

Tìm hiểu Hexagonal Architecture

Hexagonal Architecture là gì? Hexagonal Architecture (tên gọi khác là ports and adapters architecture), là một mẫu kiến trúc được dùng trong thiết kế phần mềm. Nó hướng tới việc xây dựng ứng dụng xoay quanh business/application logic mà không ảnh hưởng hoặc phụ thuộc bởi bất kì thành phần bên ngoài, mà chỉ giao […]

Đọc thêm

Go-pg

go-pg là một thư viện ORM (Object-Relational Mapping) và trình điều khiển PostgreSQL cho ngôn ngữ lập trình Go. Nó cung cấp một cách tiện lợi để tương tác với cơ sở dữ liệu PostgreSQL bằng cách ánh xạ các cấu trúc (structs) trong Go thành các bảng trong cơ sở dữ liệu và ngược […]

Đọc thêm

Ngôn ngữ lập trình Golang

Golang là gì? Go (hay còn gọi là Golang) là một ngôn ngữ lập trình được thiết kế dựa trên tư duy lập trình hệ thống. Go được phát triển bởi Robert Griesemer, Rob Pike và Ken Thompson tại Google vào năm 2007. Điểm mạnh của Go là bộ thu gom rác và hỗ trợ […]

Đọc thêm

Tìm hiểu RESTful API

RESTful là gì? REST (Representational State Transfer) là gì? REST (Representational State Transfer) không phải là một ngôn ngữ lập trình hay một framework, mà là một kiểu kiến trúc phần mềm (architectural style) để thiết kế các hệ thống mạng phân tán, đặc biệt là các dịch vụ web (web services). Nó được giới […]

Đọc thêm

Hệ quản trị CSDL PostgreSQL

SQL là gì? SQL (Structured Query Language) là viết tắt của Ngôn ngữ truy vấn có cấu trúc, là ngôn ngữ được tiêu chuẩn hóa để tương tác với các hệ thống quản lý cơ sở dữ liệu quan hệ (RDBMS). Cơ sở dữ liệu quan hệ là tập hợp dữ liệu được tổ chức thành các […]

Đọc thêm

Flutter Form

FLUTTER FORM LÀ GÌ? Form trong Flutter là một widget dùng để thu thập và kiểm tra dữ liệu người dùng nhập vào. Khi ứng dụng yêu cầu nhập nhiều trường (fields) và cần xác minh tính hợp lệ của các giá trị này, việc sử dụng Form kết hợp với TextFormField là giải pháp […]

Đọc thêm