Using the Doris Query Optimizer (Understanding Query Plans)

Doris is an open-source columnar storage system that features an efficient query optimizer internally, which can help improve application performance and availability.

Theoretical Basis

The query optimizer is a component responsible for analyzing SQL queries and generating the most effective execution plans. To create the most suitable execution plan, it needs to understand information such as the database's storage structure, indexes, and data distribution.

Doris's query optimizer uses a method called "cost-based optimization" to evaluate the cost of different execution plans. It considers various factors, such as the number of data reads, write operations, and storage space utilization, rather than simply selecting the fastest execution plan.

Components of the Query Optimizer

Doris's query optimizer consists of the following components:

1. Pattern Recognition

Pattern recognition is an important step in the query optimizer, responsible for determining the pattern of the SQL query. This pattern can help the optimizer generate more effective execution plans.

2. Batch Analysis

Batch analysis is another key component of the optimizer. It analyzes each stage of the SQL query, such as filtering and sorting, and generates the most suitable execution plan.

3. Extended Cost Estimation

Extended cost estimation is a technique used by the optimizer to help it more accurately estimate the cost of execution plans. This technique achieves this by considering multiple factors, such as data distribution and indexes.

Implementation Principles

Doris's query optimizer uses a method called "graph-based computation" to analyze and optimize SQL queries. This method creates a graph where each node represents an operation or step, and edges represent the relationships between two nodes.

1. Cost-Based Optimization

Doris's optimizer uses cost-based optimization, which selects the most suitable execution plan by evaluating the cost of different execution plans. This method can help the optimizer minimize data storage and transmission.

2. Support for Multiple Storage Devices

Doris's optimizer supports multiple storage devices, such as hard drives and SSDs, and can select the most suitable execution plan based on the specific performance and availability of the devices.

Application Scenarios

Doris's query optimizer can be used in various application scenarios, including:

1. Big Data Processing

Doris's optimizer can help big data processing applications handle large volumes of data more efficiently.

2. Real-time Data Analysis

Doris's optimizer can help real-time data analysis applications process data at higher speeds and efficiency.

3. Low-latency Applications

Doris's optimizer can help low-latency applications process data at the fastest speed and efficiency.