DuckDB：QueryProfiler（查询分析器）

在 DuckDB 中， QueryProfiler是一个用于查询性能分析的工具，帮助用户和开发者理解查询的执行过程、性能瓶颈以及优化方向。以下是对 QueryProfiler的功能、作用以及使用方法的详细解析。

---

1.QueryProfiler的作用

QueryProfiler的主要作用是提供详细的查询执行性能数据，帮助用户分析和优化查询。它能够记录以下信息：

• 查询计划的执行时间。

• 每个操作符的执行时间、数据量（Cardinality）和性能指标。

• 查询优化器和计划生成器的性能开销。

• 查询的总体延迟和资源使用情况。

通过这些数据，用户可以快速定位性能瓶颈，优化查询逻辑或数据库配置。

---

2.QueryProfiler的功能

QueryProfiler提供了多种功能，用于全面分析查询性能：

2.1查询计划分析

通过EXPLAIN和EXPLAIN ANALYZE语句，用户可以查看查询计划的详细信息：

• EXPLAIN：显示查询的逻辑计划和物理计划，但不执行查询。

• EXPLAIN ANALYZE：执行查询并显示实际的性能数据，包括每个操作符的执行时间、数据量等。

2.2性能指标

QueryProfiler提供以下性能指标：

• CPU_TIME：操作符的累计执行时间。

• LATENCY：查询的总体延迟。

• OPERATOR_CARDINALITY：每个操作符处理的数据量。

• OPERATOR_TIMING：每个操作符的执行时间。

2.3详细模式

通过设置profiling_mode = 'detailed'，用户可以获取更多性能指标，例如：

• 查询优化器的执行时间（如OPTIMIZER_JOIN_ORDER）。

• 查询计划生成器的性能开销（如PLANNER和PHYSICAL_PLANNER）。

2.4自定义指标

用户可以通过custom_profiling_settings动态启用或禁用特定的性能指标。例如：

PRAGMA custom_profiling_settings = '{"CPU_TIME": "false", "OPERATOR_CARDINALITY": "true"}';

2.5输出格式

QueryProfiler支持多种输出格式，包括：

• 文本格式：直接在控制台输出。

• JSON 格式：便于后续处理和可视化。

---

3.使用方法

QueryProfiler的使用方法非常灵活，可以通过 SQL 命令或 API 进行配置。

3.1启用和配置

通过以下 SQL 命令启用和配置QueryProfiler：

PRAGMA enable_profiling = 'json'; -- 启用性能分析并设置输出格式为 JSON
PRAGMA profiling_output = '/path/to/output.json'; -- 设置输出文件路径
PRAGMA profiling_mode = 'detailed'; -- 启用详细模式

3.2执行查询

使用EXPLAIN ANALYZE执行查询并获取性能数据：

EXPLAIN ANALYZE SELECT name FROM students JOIN exams USING (sid) WHERE name LIKE 'Ma%';

3.3查看结果

查询执行完成后，性能数据会输出到指定的文件中。例如，JSON 格式的输出可能如下：

{
   
    "query_name": "SELECT name\nFROM students\nJOIN exams USING (sid)\nWHERE name LIKE 'Ma%';",
    "cpu_time": 0.000095,
    "latency": 0.003708,
    "children": [
        {
   
            "operator_type": "PROJECTION",
            "operator_timing": 0.000001,
            "operator_cardinality": 2,
            "extra_info": {
   
                "Projections": "name",
                "Estimated Cardinality": "1"
            }
        },
        {
   
            "operator_type": "HASH_JOIN",
            "operator_timing": 0.000238,
            "operator_cardinality": 2,
            "extra_info": {
   
                "Join Type": "INNER",
                "Conditions": "sid = sid"
            }
        }
    ]
}

3.4可视化

DuckDB 提供了一个 Python 脚本，可以将 JSON 格式的性能数据渲染为查询图（Query Graph），便于直观分析：

python -m duckdb.query_graph /path/to/output.json

---

4.QueryProfiler的优势

QueryProfiler提供了以下优势：

• 全面的性能数据：能够记录查询的每个阶段（如解析、优化、执行）的性能开销。

• 灵活的配置：用户可以根据需要启用或禁用特定的性能指标。

• 多种输出格式：支持文本和 JSON 格式，便于后续处理和可视化。

• 易于使用：通过简单的 SQL 命令即可启用和配置。

---

5.总结

QueryProfiler是 DuckDB 中用于查询性能分析的强大工具。它通过EXPLAIN ANALYZE和多种性能指标，帮助用户深入理解查询的执行过程和性能瓶颈。通过灵活的配置和多种输出格式，用户可以轻松地分析和优化查询性能。

如果你需要更深入地了解QueryProfiler的实现细节，可以参考 DuckDB 的源码文件：

• src/main/query_profiler.cpp
• src/include/duckdb/main/query_profiler.hpp

这些文件中包含了QueryProfiler的具体实现和使用方式。
query_profiler.hpp

//===----------------------------------------------------------------------===//
//                         DuckDB
//
// duckdb/main/query_profiler.hpp
//
//
//===----------------------------------------------------------------------===//

#pragma once

#include "duckdb/common/common.hpp"
#include "duckdb/common/deque.hpp"
#include "duckdb/common/enums/profiler_format.hpp"
#include "duckdb/common/enums/explain_format.hpp"
#include "duckdb/common/pair.hpp"
#include "duckdb/common/profiler.hpp"
#include "duckdb/common/reference_map.hpp"
#include "duckdb/common/string_util.hpp"
#include "duckdb/common/types/data_chunk.hpp"
#include "duckdb/common/unordered_map.hpp"
#include "duckdb/common/winapi.hpp"
#include "duckdb/execution/expression_executor_state.hpp"
#include "duckdb/execution/physical_operator.hpp"
#include "duckdb/main/profiling_info.hpp"
#include "duckdb/main/profiling_node.hpp"

#include <stack>

namespace duckdb {
   
class ClientContext;
class ExpressionExecutor;
class ProfilingNode;
class PhysicalOperator;
class SQLStatement;

struct OperatorInformation {
   
	explicit OperatorInformation(double time_p = 0, idx_t elements_returned_p = 0, idx_t elements_scanned_p = 0,
	                             idx_t result_set_size_p = 0)
	    : time(time_p), elements_returned(elements_returned_p), result_set_size(result_set_size_p) {
   
	}

	double time;
	idx_t elements_returned;
	idx_t result_set_size;
	string name;
	InsertionOrderPreservingMap<string> extra_info;

	void AddTime(double n_time) {
   
		time += n_time;
	}

	void AddReturnedElements(idx_t n_elements) {
   
		elements_returned += n_elements;
	}

	void AddResultSetSize(idx_t n_result_set_size) {
   
		result_set_size += n_result_set_size;
	}
};

//! The OperatorProfiler measures timings of individual operators
//! This class exists once for all operators and collects `OperatorInfo` for each operator
class OperatorProfiler {
   
	friend class QueryProfiler;

public:
	DUCKDB_API explicit OperatorProfiler(ClientContext &context);
	~OperatorProfiler() {
   
	}

public:
	DUCKDB_API void StartOperator(optional_ptr<const PhysicalOperator> phys_op);
	DUCKDB_API void EndOperator(optional_ptr<DataChunk> chunk);

	//! Adds the timings in the OperatorProfiler (tree) to the QueryProfiler (tree).
	DUCKDB_API void Flush(const PhysicalOperator &phys_op);
	DUCKDB_API OperatorInformation &GetOperatorInfo(const PhysicalOperator &phys_op);

public:
	ClientContext &context;

private:
	//! Whether or not the profiler is enabled
	bool enabled;
	//! Sub-settings for the operator profiler
	profiler_settings_t settings;

	//! The timer used to time the execution time of the individual Physical Operators
	Profiler op;
	//! The stack of Physical Operators that are currently active
	optional_ptr<const PhysicalOperator> active_operator;
	//! A mapping of physical operators to profiled operator information.
	reference_map_t<const PhysicalOperator, OperatorInformation> operator_infos;
};

struct QueryInfo {
   
	QueryInfo() : blocked_thread_time(0) {
   };
	string query_name;
	double blocked_thread_time;
};

//! The QueryProfiler can be used to measure timings of queries
class QueryProfiler {
   
public:
	DUCKDB_API explicit QueryProfiler(ClientContext &context);

public:
	// Propagate save_location, enabled, detailed_enabled and automatic_print_format.
	void Propagate(QueryProfiler &qp);

	using TreeMap = reference_map_t<const PhysicalOperator, reference<ProfilingNode>>;

private:
	unique_ptr<ProfilingNode> CreateTree(const PhysicalOperator &root, const profiler_settings_t &settings,
	                                     const idx_t depth = 0);
	void Render(const ProfilingNode &node, std::ostream &str) const;
	string RenderDisabledMessage(ProfilerPrintFormat format) const;

public:
	DUCKDB_API bool IsEnabled() const;
	DUCKDB_API bool IsDetailedEnabled() const;
	DUCKDB_API ProfilerPrintFormat GetPrintFormat(ExplainFormat format = ExplainFormat::DEFAULT) const;
	DUCKDB_API bool PrintOptimizerOutput() const;
	DUCKDB_API string GetSaveLocation() const;

	DUCKDB_API static QueryProfiler &Get(ClientContext &context);

	DUCKDB_API void StartQuery(string query, bool is_explain_analyze = false, bool start_at_optimizer = false);
	DUCKDB_API void EndQuery();

	DUCKDB_API void StartExplainAnalyze();

	//! Adds the timings gathered by an OperatorProfiler to this query profiler
	DUCKDB_API void Flush(OperatorProfiler &profiler);
	//! Adds the top level query information to the global profiler.
	DUCKDB_API void SetInfo(const double &blocked_thread_time);

	DUCKDB_API void StartPhase(MetricsType phase_metric);
	DUCKDB_API void EndPhase();

	DUCKDB_API void Initialize(const PhysicalOperator &root);

	DUCKDB_API string QueryTreeToString() const;
	DUCKDB_API void QueryTreeToStream(std::ostream &str) const;
	DUCKDB_API void Print();

	//! return the printed as a string. Unlike ToString, which is always formatted as a string,
	//! the return value is formatted based on the current print format (see GetPrintFormat()).
	DUCKDB_API string ToString(ExplainFormat format = ExplainFormat::DEFAULT) const;
	DUCKDB_API string ToString(ProfilerPrintFormat format) const;

	static InsertionOrderPreservingMap<string> JSONSanitize(const InsertionOrderPreservingMap<string> &input);
	static string JSONSanitize(const string &text);
	static string DrawPadded(const string &str, idx_t width);
	DUCKDB_API string ToJSON() const;
	DUCKDB_API void WriteToFile(const char *path, string &info) const;

	idx_t OperatorSize() {
   
		return tree_map.size();
	}

	void Finalize(ProfilingNode &node);

	//! Return the root of the query tree
	optional_ptr<ProfilingNode> GetRoot() {
   
		return root.get();
	}

private:
	ClientContext &context;

	//! Whether or not the query profiler is running
	bool running;
	//! The lock used for accessing the global query profiler or flushing information to it from a thread
	mutable std::mutex lock;

	//! Whether or not the query requires profiling
	bool query_requires_profiling;

	//! The root of the query tree
	unique_ptr<ProfilingNode> root;

	//! Top level query information.
	QueryInfo query_info;
	//! The timer used to time the execution time of the entire query
	Profiler main_query;
	//! A map of a Physical Operator pointer to a tree node
	TreeMap tree_map;
	//! Whether or not we are running as part of a explain_analyze query
	bool is_explain_analyze;

public:
	const TreeMap &GetTreeMap() const {
   
		return tree_map;
	}

private:
	//! The timer used to time the individual phases of the planning process
	Profiler phase_profiler;
	//! A mapping of the phase names to the timings
	using PhaseTimingStorage = unordered_map<MetricsType, double, MetricsTypeHashFunction>;
	PhaseTimingStorage phase_timings;
	using PhaseTimingItem = PhaseTimingStorage::value_type;
	//! The stack of currently active phases
	vector<MetricsType> phase_stack;

private:
	void MoveOptimizerPhasesToRoot();

	//! Check whether or not an operator type requires query profiling. If none of the ops in a query require profiling
	//! no profiling information is output.
	bool OperatorRequiresProfiling(PhysicalOperatorType op_type);
	ExplainFormat GetExplainFormat(ProfilerPrintFormat format) const;
};

} // namespace duckdb

query_profiler.cpp

// query_profiler.cpp
#include "duckdb/main/query_profiler.hpp"

#include "duckdb/common/fstream.hpp"
#include "duckdb/common/limits.hpp"
#include "duckdb/common/numeric_utils.hpp"
#include "duckdb/common/printer.hpp"
#include "duckdb/common/string_util.hpp"
#include "duckdb/common/tree_renderer/text_tree_renderer.hpp"
#include "duckdb/execution/expression_executor.hpp"
#include "duckdb/execution/operator/helper/physical_execute.hpp"
#include "duckdb/execution/operator/scan/physical_table_scan.hpp"
#include "duckdb/execution/physical_operator.hpp"
#include "duckdb/main/client_config.hpp"
#include "duckdb/main/client_context.hpp"
#include "duckdb/main/client_data.hpp"
#include "duckdb/planner/expression/bound_function_expression.hpp"
#include "yyjson.hpp"

#include <algorithm>
#include <utility>

using namespace duckdb_yyjson; // NOLINT

namespace duckdb {
   

QueryProfiler::QueryProfiler(ClientContext &context_p)
    : context(context_p), running(false), query_requires_profiling(false), is_explain_analyze(false) {
   
}

bool QueryProfiler::IsEnabled()