What's New
Function Overview
Product Bulletin
- Product Notice
- Version Support Notes
Service Overview
- GaussDB(DWS) Infographics
- What Is GaussDB(DWS)?
- Data Warehouse Types
- Data Warehouse Flavors
- Advantages
- Application Scenarios
- Functions
- Concepts
- Related Services
- Security
- GaussDB(DWS) Permissions Management
- GaussDB(DWS) Access
- Restrictions
- Technical Support
- Service Quotas
- GaussDB(DWS) Technical Specifications
Billing
- GaussDB(DWS) Billing Overview
- Billing Modes
- Item
- Billing Examples
- Billing Mode Change
- Renewal
  - Overview
  - Manual Renewal
- Bills
- Arrears
- Stopping Billing
- Cost Management
- Billing FAQs
Getting Started
- Checkpoint Vehicle Analysis
- Supply Chain Requirement Analysis of a Company
- Operations Status Analysis of a Retail Department Store
- Creating a Time Series Table
- Best Practices of Hot and Cold Data Management
- Best Practices for Automatic Partition Management
- Creating a Cluster and Connecting to It
- Using CDM to Migrate MySQL Data to the GaussDB(DWS) Cluster
- Using DLI Flink Jobs to Write Kafka Data to GaussDB(DWS) in Real Time
- Basic SQL Operations
- Database Quick Start
- Getting Started with Common Practices
User Guide
- Using GaussDB(DWS)
- Preparations
- Creating a GaussDB(DWS) Cluster
- Connecting to a GaussDB(DWS) Cluster
- Creating a GaussDB(DWS) Database and User
- Migrating Service Data to a GaussDB(DWS) Cluster
  - Migrating Data to a GaussDB(DWS) Cluster Using GDS-Kafka
  - Data Source Management
- GaussDB(DWS) Cluster Data Security and Encryption
- GaussDB(DWS) Cluster Management
- GaussDB(DWS) Cluster O&M
Best Practices
- Import and Export
- Data Migration
- Data Analytics
- Decoupled Storage and Compute
  - DWS 3.0 Decoupled Storage and Compute Usage Suggestions and Performance Optimization
- Data Development
- Database Management
- Performance Tuning
  - Optimizing Table Structure Design to Enhance GaussDB(DWS) Query Performance
  - Analyzing SQL Statements That Are Being Executed to Handle GaussDB(DWS) Performance Issues
- Cluster Management
  - Binding Different Resource Pools to Two Types of Jobs to Balance Load for GaussDB(DWS)
  - Scaling Options for GaussDB(DWS) with a Coupled Storage-Compute Architecture
Data Migration and Synchronization
- Data Migration to GaussDB(DWS)
- Importing Data
- Full Database Migration
  - Using CDM to Migrate Data to GaussDB(DWS)
  - Using DSC to Migrate SQL Scripts
- Real-time Import
  - Using DRS to Import Data to GaussDB(DWS)
  - Real-time Data Import From Kafka GaussDB(DWS)
- Metadata Migration
  - Using gs_dump and gs_dumpall to Export Metadata
  - Using gs_restore to Import Data
- Exporting Data
- Other Operations
Developer Guide
- Standard data warehouse (9.1.0.x)
- Standard data warehouse (8.1.3.x)
- Hybrid Data Warehouse (9.1.0.x）
- Hybrid Data Warehouse (8.1.3.x）
- Historical Versions
SQL Syntax Reference
- SQL Syntax Reference (9.1.0.x)
- SQL Syntax Reference (8.1.3.x)
- Historical Versions
Performance Tuning
- Overview of Query Performance Optimization
- Query Execution Process
- SQL Execution Plan
- SQL Optimization Guide
- Optimization Cases
- SQL Execution Troubleshooting
- query_band Load Identification
- Common Performance Parameter Optimization Design
Tool Guide
- Overview
- Downloading Related Tools
- gsql
- Data Studio
- GDS
- DSC
- DataCheck
- DWS-Connector
- Server Tool
API Reference
- Before You Start
- API Overview
- Calling APIs
- Getting Started
- API Description
- Application Cases
  - Using Postman to Call the API for Creating a Cluster
  - Using Postman to Call the API for Creating a Snapshot
- Introduction
- Appendix
SDK Reference
- SDK Overview
FAQs
- Top FAQs
- Product Consulting
- Database Connections
- Data Migration
- Database Usage
- Cluster Management
- Account Permissions
- Database Performance
- Backup and Restoration
  1. Why Does It Take a Long Time to Create an Automated Snapshot in GaussDB(DWS)?
  2. Does a GaussDB(DWS) Snapshot Have the Same Function as an EVS Snapshot?
Troubleshooting
- Database Connections
- JDBC/ODBC
- Data Import and Export
- Database Parameter Modification
- Account/Permission/Password
- Cluster Performance
- Cluster Exceptions
  - The Disk Usage Alarm Is Frequently Generated
- Database Use
Videos
Performance White Paper
- Overview
- Test Result
  - TPC-H Single Query Test
  - TPC-DS Single Query Test
- Test Methods
- Appendixes
  - TPC-H Test Sets
  - TPC-DS Test Sets
Technical White Paper
- GaussDB(DWS)
- Platforms and Technical Specifications Supported by GaussDB(DWS)
  - Technical Specifications
- GaussDB(DWS) Core Technologies
- GaussDB(DWS) Tools
  - Client Tools
  - Database Monitoring Tool
- External APIs
Error Code Reference
- 8.2.0 and earlier versions
  - Management Console Error Code
  - Data Warehouse Service Error Codes
- 8.2.1 or later versions
Glossary
More Documents
- User Guide
- API Reference (ME-Abu Dhabi Region)
- Developer Guide (ME-Abu Dhabi Region)
- SQL Syntax Reference (ME-Abu Dhabi Region)
- Tool Guide (ME-Abu Dhabi Region)
- Error Code Reference (ME-Abu Dhabi Region)
  - Management Console Error Code
- User Guide (Paris Region)
- API Reference (Paris Region)
- Developer Guide (Paris Region)
- SQL Syntax Reference (Paris Region)
- Tool Guide (Paris Region)
- Error Code Reference (Paris Region)
  - Management Console Error Code
- User Guide (Kuala Lumpur Region)
- API Reference (Kuala Lumpur Region)
- Developer Guide (Kuala Lumpur Region)
- SQL Syntax Reference (Kuala Lumpur Region)
- Tool Guide (Kuala Lumpur Region)
- Error Code Reference (Kuala Lumpur Region)
  - Management Console Error Code
General Reference
- Glossary
- Service Level Agreement
- White Papers
- Endpoints
- Permissions

On this page

Rule 3.7: Avoiding Executing SQL Statements That Do Not Support Pushdown
Rule 3.8: Specifying Association Conditions when Multiple Tables Are Associated
Rule 3.9: Ensuring Consistency of Data Types in Associated Fields across Multiple Tables
Suggestion 3.10: Avoiding Function Calculation on Association and Filter Condition Fields
Suggestion 3.11: Performing Pressure Tests and Concurrency Control for Resource-intensive SQL Statements
Rule 3.12: Avoiding Excessive COUNT Operations on Large Row-store Tables
Suggestion 3.13: Avoid Getting Large Result Sets (Except for Data Exports)
Suggestion 3.14: Avoiding the Usage of SELECT * for Queries
Suggestion 3.15: Using WITH RECURSIVE with Defined Termination Condition for Recursion
Suggestion 3.16: Setting Schema Prefix for Table and Function Access
Suggestion 3.17: Identifying an SQL Statement with a Unique SQL Comment

Show all

Help Center/ GaussDB(DWS)/ Developer Guide/ Standard data warehouse (9.1.0.x)/ GaussDB(DWS) Development Design Proposal/ GaussDB(DWS) SQL Statement Development Specifications/ SELECT Operation

SELECT Operation

Updated on 2024-12-18 GMT+08:00

View PDF

Rule 3.7: Avoiding Executing SQL Statements That Do Not Support Pushdown

NOTE:

GaussDB(DWS) uses a distributed architecture, and to achieve optimal performance, SQL statements need to be pushed down to utilize distributed computing resources.

Impact of rule violation:

SQL statements that are not pushed down may experience poor execution performance and, in severe cases, can lead to CN resource bottlenecks, impacting overall services.

Solution:

Do not use syntax or functions that cannot be executed near the data source. For details, see Optimizing Statement Pushdown.

Rule 3.8: Specifying Association Conditions when Multiple Tables Are Associated

NOTE:

Impact of rule violation:

If no association condition is specified when linking multiple tables, it will result in a Cartesian product calculation. This can lead to an expanded result set, posing risks of performance issues and resource overload.

Solution:

Specify filter and association conditions for each table during the association process.

Rule 3.9: Ensuring Consistency of Data Types in Associated Fields across Multiple Tables

NOTE:

Impact of rule violation:

Ensure consistent data types for associated fields to avoid unnecessary type conversions, data redistribution issues, and hindered generation of optimal plans.

Solution:

Use the same data type for associated fields when tables are associated.

Suggestion 3.10: Avoiding Function Calculation on Association and Filter Condition Fields

NOTE:

Impact of rule violation:

In cases where function calculations are involved in association and filter conditions, the optimizer may fail to obtain accurate field statistics, impacting execution performance.

Solution:

When comparing association condition fields, process the data before importing it into the database, especially when calculations are required for comparison.

When filter criteria are compared with constants, perform function calculation only on constant columns. The following is an example:

        
           SELECT id, from_image_id, from_person_id, from_video_id
FROM face_data
WHERE SS.DEL_FLAG = 'N'
AND NVL(SS.DELETE_FLAG, 'N') = 'N'
The modification is as follows:
SELECT id, from_image_id, from_person_id, from_video_id
FROM face_data
where SS.DEL_FLAG = 'N'
AND (SS.DELETE_FLAG = 'N' or SS.DELETE_FLAG is null)

Suggestion 3.11: Performing Pressure Tests and Concurrency Control for Resource-intensive SQL Statements

NOTE:

Impact of rule violation:

Storage and computing resources are overloaded, and the overall running performance deteriorates.

Solution:

A resource-intensive SQL statement contains:

A large number of UNION ALL.
A large number of AGGs (such as COUNT DISTINCT and MAX).
A lot of JOIN operations for a large number of tables.
A large number of STREAM operators (plan dimension).

Before rolling out, conduct pressure tests and implement concurrency control for certain SQL statements. If the resource capacity is exceeded, optimizing the service should be prioritized before reassessing the rollout plan.

Rule 3.12: Avoiding Excessive COUNT Operations on Large Row-store Tables

NOTE:

If SSDs or other high-performance disk types are used, it may not be necessary to adhere strictly to this rule, but it is still crucial to monitor the I/O consumption.

Impact of rule violation:

Performing frequent COUNT operations on large row-store tables can consume a significant amount of I/O resources, potentially leading to performance issues if an I/O bottleneck occurs.

Solution:

Reduce the frequency of COUNT operations, use result caching, and collect statistics by partition to minimize I/O consumption.

Suggestion 3.13: Avoid Getting Large Result Sets (Except for Data Exports)

NOTE:

Impact of rule violation:

If you do not need to view all the results, querying ultra-large result sets becomes inefficient and wasteful in terms of resources.

Solution:

Use the LIMIT clause to retrieve only the necessary result segments.
Use a cursor to obtain the result sets by segment and set an appropriate value for FETCH SIZE if you need to query a large number of result sets.

Suggestion 3.14: Avoiding the Usage of SELECT * for Queries

NOTE:

Impact of rule violation:

Querying unnecessary columns increases the computing load and wastes computing resources.

Solution:

Clearly list the fields required for the query in the SELECT statement to improve the query performance.

Suggestion 3.15: Using WITH RECURSIVE with Defined Termination Condition for Recursion

NOTE:

Impact of rule violation:

In cases where there is no specific termination condition, recursive operations can enter an infinite loop.
Recursive operations generate duplicate data and occupy excessive resources.

Solution:

Design proper termination conditions based on the volume and characteristics of the data in the service table.

Suggestion 3.16: Setting Schema Prefix for Table and Function Access

NOTE:

Impact of rule violation:

If the schema name prefix is not specified, the search will be performed sequentially across all tablespaces based on the tablespace list in the current search_path. This can lead to accessing unexpected tables due to schema switchover.

Solution:

To enhance readability, stability, and portability, explicitly specify the schema prefix as SCHEMA. when accessing tables and function objects.

Suggestion 3.17: Identifying an SQL Statement with a Unique SQL Comment

NOTE:

Impact of rule violation:

The service's source tracing capability is limited. You can only verify it with R&D engineers using the database, user name, and client IP address.

Solution:

You are advised to use query_band. The following is an example:

        
           SET query_band='JobName=abc;AppName=test;UserName=user';

Add a unique comment for each SQL statement to facilitate troubleshooting and application performance analysis. The following is an example of such comment.
```
/* Module name_Tool name_Job name_Step */, for example, /* mca_python_xxxxxx_step1 */ insert into xxx select … from
```