基于成本的查询优化

概念概述

基于成本的查询优化（Cost-Based Query Optimization）指的是：基于成本的优化器按估算成本比较多种执行计划，因此统计信息与选择率估算的准确性决定查询的快慢。

从更严格的定义看，A cost-based optimizer enumerates possible execution plans, estimates the cost of each using statistics about data distribution and available indexes, and chooses the cheapest one. 该概念在工程实践中的价值，在于它能帮助团队用统一的语言描述一个具体的质量属性或设计约束，从而在评审与协作中减少歧义。

为什么需要它

It governs whether a query uses an index, a sequential scan, or a particular join order, and its choices depend on the estimates it is given. 它与 Database Connection Multiplexing、Autovacuum and Dead Tuple Reclamation、LATERAL Joins 等概念经常一起出现，用于解决相近的工程问题。理解它的适用场景，有助于在多种备选方案之间做出有依据的取舍，而不是凭直觉选择技术方案。

常见误解

• the optimizer always chooses the objectively fastest plan
• an index is used whenever one exists

需要避免的反模式

• assuming statistics are current after a bulk load without analysing
• fighting the planner with hints before checking the estimates

实践要点

1. Cost is computed from row estimates, selectivity, and page costs, so a stale or unrepresentative statistic leads to a bad plan even when an index exists.
2. Correlated columns confuse the planner because it assumes independence, so it can badly underestimate the rows a combined predicate returns.
3. Diagnose with an explain plan that shows estimated versus actual rows and use that gap to decide whether to refresh statistics, add an index, or restructure the query.

参考来源

https://www.postgresql.org/docs/current/planner-optimizer.html

代码示例

package store

import (
	"context"
	"database/sql"
)

type User struct {
	ID    int64
	Email string
}

func ListActiveUsers(ctx context.Context, db *sql.DB) ([]User, error) {
	rows, err := db.QueryContext(ctx,
		"SELECT id, email FROM users WHERE active = $1 ORDER BY id", true)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var user User
		if err := rows.Scan(&user.ID, &user.Email); err != nil {
			return nil, err
		}
		users = append(users, user)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return users, nil
}

import polars as pl

events = pl.DataFrame({
    'user_id': [1, 2, 3, 4, 5, 6],
    'event': ['view', 'click', 'view', 'purchase', 'click', 'view'],
    'amount': [0, 0, 0, 120, 0, 0],
    'day': ['2026-10-21', '2026-10-21', '2026-10-22', '2026-10-22', '2026-10-22', '2026-10-22'],
})

lazy = events.lazy()

report = (
    lazy
    .filter(pl.col('event') != 'view')
    .group_by('day')
    .agg(
        active_users=pl.col('user_id').n_unique(),
        revenue=pl.col('amount').sum(),
        top_event=pl.col('event').mode().first(),
    )
    .sort('day')
    .collect(streaming=True)
)

print(report)

plan = (
    lazy.filter(pl.col('amount') > 0)
    .select(['day', 'amount'])
    .collect_optimized_plan()
)
print('optimized logical plan nodes:', len(str(plan).splitlines()))