You can optimize SQL queries to improve query performance and simplify database operations. For example, you can add indexes, select appropriate data types, exclude SELECT *
statements, and use temporary tables to store intermediate results.
Optimization capabilities
Limit the number of queries that contain IN or OR conditions. A large number of these queries may slow down 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 an 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 conditions. Indexes can be used only when=
,<
,<=
,>
,>=
, or BETWEEN is added to WHERE conditions.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)
withvarchar(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.
JOIN
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.