Search
Optimization capabilities
- Limit the number of queries that contain IN or OR conditions. A large number of these
queries may slow down the root cause analysis (RCA) optimization.
- We recommend that you do not use complex expressions or functions in WHERE conditions.
These complex expressions or functions may cause the optimizer to estimate inaccurate
number of rows.
Indexes
- To avoid full table scans, you can add indexes to the columns referenced in WHERE
conditions.
- We recommend that you do not add the
!=
or <>
operator to WHERE clauses. Indexes can be used only when =
, <
, <=
, >
, >=
, or BETWEEN is added to WHERE clauses.
- We recommend that you do not add OR conditions to WHERE clauses. Large numbers of
OR conditions may make indexes less applicable. When you identify that an SQL statement
has not used an index on a column as expected, you can check whether large numbers
of OR conditions are contained and use a UNION clause to optimize the SQL statement.
- We recommend that you replace LIKE conditions with full-text search. B-tree indexes
support only the leftmost LIKE condition that does not contain wildcards. For other
LIKE conditions, you must use GiST or GIN indexes supported by the pg_trgm extension.
Data types
- We recommend that you use numeric types instead of character string types to improve
query and connection performance.
- We recommend that you replace CHAR(n) with VARCHAR(n) to save storage space, reduce
computing resources, and improve string comparison efficiency.
Data columns
- We recommend that you do not use
SELECT *
statements, but specify the columns you need based on your business requirements.
Temporary tables
- We recommend that you use temporary tables to store intermediate results for complex
queries. These temporary tables facilitate service debugging and avoid repeated computing.
WHERE conditions
- We recommend that you place highly frequent values in front of IN clauses to reduce
the number of comparisons.
Joins
- We recommend that you do not join more than 12 tables in a query. If more than 12
tables are joined in a query, we recommend that you use temporary tables to split
SQL statements.
Stored procedures or functions
- If SQL statements can be used, we recommend that you do not use loop operations in
stored procedures or functions.
Thank you! We've received your feedback.