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.