SELECT

Updated on 2025-02-27 GMT+08:00

View PDF

[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] SELECT LIMIT statements must contain ORDER BY for query.
As a distributed database, distributes table data across multiple DNs. If the SQL statement contains only LIMIT but not ORDER BY, the database sends the result (meeting the query requirements) sent by the DN with fast network transmission to the client as the final result. The network transmission efficiency may change at different time points. As a result, the returned results are inconsistent when the SQL statement is executed for multiple times.
[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 cluster to be unavailable.
[Recommendation] Do not use subqueries in the SELECT target columns. Otherwise, the plan may fail to be pushed down to DNs for execution, affecting the execution performance.
[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.