SELECT

• [Rule] Do not use the wildcard character (*) in the SELECT statement.

If the table structure is changed due to service or database upgrade when a wildcard column is used to query a table, the table structure may be incompatible with service statements. Therefore, the service must specify the name of the table column to be queried and avoid using the wildcard character.

• [Rule] Do not perform operations that may cause sorting, such as ORDER BY, DISTINCT, GROUP BY, and UNION, on large columns (such as VARCHAR(2000)).

  These operations consume a large number of CPU and memory resources, resulting in low execution efficiency.

• [Rule] Do not use the LOCK TABLE statement to lock a table. Instead, use the SELECT .. FOR UPDATE statement.

  LOCK TABLE provides multiple levels of locks. However, if you do not fully understand the database principles and services, misuse of table locks may trigger deadlocks, causing the database to be unavailable.

• [Recommendation] Use UNION ALL instead of UNION. Consider data deduplication if necessary.

  UNION ALL does not deduplicate data and does not require sorting operations. Therefore, UNION ALL is faster than UNION. If deduplication is not required, UNION ALL is preferred.

• [Recommendation] Do not frequently use count() to obtain the number of rows in a large table. This operation consumes a large number of resources and affects the execution efficiency of parallel jobs.
  If you do not need the real-time row statistics, run the following statement to obtain the number of rows in the table:

  SELECT reltules FROM pg_class WHERE relname = 'tablename';

  The number of rows recorded in pg_class is updated only after ANALYZE is executed on the table.

  Currently, ANALYZE is triggered in either of the following conditions:

  • The service sends an ANALYZE statement. For example:

    -- Analyze all tables in the connection library.
    ANALYZE;
    -- Analyze the specified table.
    ANALYZE tablename;

  • This event is triggered when the number of rows added or deleted at a specified interval or in a table reaches a specified value by using the AUTO VACUUM mechanism. The interval and addition/deletion ratio can be set through the GUC parameters.