advisor

package
v1.14.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Apr 11, 2026 License: Apache-2.0 Imports: 4 Imported by: 0

Documentation

Overview

Package advisor provides SQL query optimization analysis by walking parsed ASTs and applying configurable rules that detect common performance anti-patterns.

The central type is Optimizer, created with New() (all built-in rules) or NewWithRules(...Rule) for a custom rule set. Optimizer.AnalyzeSQL is a convenience method that parses a SQL string and returns an OptimizationResult containing a slice of Suggestion values, a query complexity classification (simple / moderate / complex), and an optimization score from 0 (worst) to 100 (no issues). Each Suggestion carries a rule ID, severity (info / warning / error), a human-readable message and detail, the source location, and where possible a suggested SQL rewrite.

Eight built-in rules are registered by DefaultRules: 

OPT-001  SELECT * Detection         - recommend listing columns explicitly
OPT-002  Missing WHERE Clause       - UPDATE/DELETE without WHERE affects all rows
OPT-003  Cartesian Product          - implicit cross join from multiple FROM tables
OPT-004  SELECT DISTINCT Overuse    - DISTINCT may mask incorrect join conditions
OPT-005  Subquery in WHERE          - suggest converting correlated subqueries to JOINs
OPT-006  OR in WHERE Clause         - OR on different columns may prevent index usage
OPT-007  Leading Wildcard in LIKE   - LIKE '%...' forces a full table scan
OPT-008  Function on Indexed Column - wrapping a column in a function defeats B-tree indexes

Custom rules implement the Rule interface (ID, Name, Description, Analyze) and are passed to NewWithRules. All built-in rules are stateless and safe for concurrent use.

Quick Start: 

opt := advisor.New()
result, err := opt.AnalyzeSQL("SELECT * FROM users")
if err != nil {
    log.Fatal(err)
}
for _, s := range result.Suggestions {
    fmt.Printf("[%s] %s: %s\n", s.Severity, s.RuleID, s.Message)
}

For fine-grained control, parse the SQL first and call Analyze directly: 

astNode, _ := gosqlx.Parse(sql)
result := opt.Analyze(astNode)

Index

Constants

View Source 
const (
	SeverityInfo    = "info"
	SeverityWarning = "warning"
	SeverityError   = "error"
)

Severity levels for optimization suggestions.

View Source 
const (
	ComplexitySimple   = "simple"
	ComplexityModerate = "moderate"
	ComplexityComplex  = "complex"
)

Complexity levels for query classification.

Variables

This section is empty.

Functions

func FormatResult 

func FormatResult(result *OptimizationResult) string

FormatResult produces a human-readable text report from an OptimizationResult.

Types

type CartesianProductRule 

type CartesianProductRule struct{}

CartesianProductRule detects implicit cross joins (multiple tables in FROM without JOIN conditions).

func (*CartesianProductRule) Analyze 

func (r *CartesianProductRule) Analyze(stmt ast.Statement) []Suggestion

func (*CartesianProductRule) Description 

func (r *CartesianProductRule) Description() string

func (*CartesianProductRule) ID 

func (r *CartesianProductRule) ID() string

func (*CartesianProductRule) Name 

func (r *CartesianProductRule) Name() string

type CorrelatedSubqueryInSelectRule added in v1.14.0 

type CorrelatedSubqueryInSelectRule struct{}

CorrelatedSubqueryInSelectRule detects subqueries in the SELECT column list. Each row in the outer query triggers one inner query — the classic N+1 problem.

func (*CorrelatedSubqueryInSelectRule) Analyze added in v1.14.0 

func (r *CorrelatedSubqueryInSelectRule) Analyze(stmt ast.Statement) []Suggestion

func (*CorrelatedSubqueryInSelectRule) Description added in v1.14.0 

func (r *CorrelatedSubqueryInSelectRule) Description() string

func (*CorrelatedSubqueryInSelectRule) ID added in v1.14.0 

func (r *CorrelatedSubqueryInSelectRule) ID() string

func (*CorrelatedSubqueryInSelectRule) Name added in v1.14.0 

func (r *CorrelatedSubqueryInSelectRule) Name() string

type CountStarRule added in v1.14.0 

type CountStarRule struct{}

CountStarRule flags COUNT(DISTINCT col) in SELECT lists. COUNT(DISTINCT col) is significantly more expensive than COUNT(*) or COUNT(col) because it requires sorting or hashing the distinct values.

func (*CountStarRule) Analyze added in v1.14.0 

func (r *CountStarRule) Analyze(stmt ast.Statement) []Suggestion

func (*CountStarRule) Description added in v1.14.0 

func (r *CountStarRule) Description() string

func (*CountStarRule) ID added in v1.14.0 

func (r *CountStarRule) ID() string

func (*CountStarRule) Name added in v1.14.0 

func (r *CountStarRule) Name() string

type DeepSubqueryNestingRule added in v1.14.0 

type DeepSubqueryNestingRule struct{}

DeepSubqueryNestingRule flags queries with more than 3 levels of subquery nesting. Deeply nested subqueries are difficult to optimize and should be rewritten with CTEs.

func (*DeepSubqueryNestingRule) Analyze added in v1.14.0 

func (r *DeepSubqueryNestingRule) Analyze(stmt ast.Statement) []Suggestion

func (*DeepSubqueryNestingRule) Description added in v1.14.0 

func (r *DeepSubqueryNestingRule) Description() string

func (*DeepSubqueryNestingRule) ID added in v1.14.0 

func (r *DeepSubqueryNestingRule) ID() string

func (*DeepSubqueryNestingRule) Name added in v1.14.0 

func (r *DeepSubqueryNestingRule) Name() string

type DistinctOveruseRule 

type DistinctOveruseRule struct{}

DistinctOveruseRule warns when DISTINCT might indicate a JOIN issue.

func (*DistinctOveruseRule) Analyze 

func (r *DistinctOveruseRule) Analyze(stmt ast.Statement) []Suggestion

func (*DistinctOveruseRule) Description 

func (r *DistinctOveruseRule) Description() string

func (*DistinctOveruseRule) ID 

func (r *DistinctOveruseRule) ID() string

func (*DistinctOveruseRule) Name 

func (r *DistinctOveruseRule) Name() string

type ExplicitCrossJoinRule added in v1.14.0 

type ExplicitCrossJoinRule struct{}

ExplicitCrossJoinRule detects explicit CROSS JOIN clauses (without a join condition). CROSS JOIN produces a cartesian product which is usually unintentional.

func (*ExplicitCrossJoinRule) Analyze added in v1.14.0 

func (r *ExplicitCrossJoinRule) Analyze(stmt ast.Statement) []Suggestion

func (*ExplicitCrossJoinRule) Description added in v1.14.0 

func (r *ExplicitCrossJoinRule) Description() string

func (*ExplicitCrossJoinRule) ID added in v1.14.0 

func (r *ExplicitCrossJoinRule) ID() string

func (*ExplicitCrossJoinRule) Name added in v1.14.0 

func (r *ExplicitCrossJoinRule) Name() string

type FunctionOnColumnRule 

type FunctionOnColumnRule struct{}

FunctionOnColumnRule detects function calls wrapping columns in WHERE clauses.

func (*FunctionOnColumnRule) Analyze 

func (r *FunctionOnColumnRule) Analyze(stmt ast.Statement) []Suggestion

func (*FunctionOnColumnRule) Description 

func (r *FunctionOnColumnRule) Description() string

func (*FunctionOnColumnRule) ID 

func (r *FunctionOnColumnRule) ID() string

func (*FunctionOnColumnRule) Name 

func (r *FunctionOnColumnRule) Name() string

type HavingWithoutGroupByRule added in v1.14.0 

type HavingWithoutGroupByRule struct{}

HavingWithoutGroupByRule detects HAVING clauses without a corresponding GROUP BY. Most databases treat this as a full-table aggregate — rarely the intent.

func (*HavingWithoutGroupByRule) Analyze added in v1.14.0 

func (r *HavingWithoutGroupByRule) Analyze(stmt ast.Statement) []Suggestion

func (*HavingWithoutGroupByRule) Description added in v1.14.0 

func (r *HavingWithoutGroupByRule) Description() string

func (*HavingWithoutGroupByRule) ID added in v1.14.0 

func (r *HavingWithoutGroupByRule) ID() string

func (*HavingWithoutGroupByRule) Name added in v1.14.0 

func (r *HavingWithoutGroupByRule) Name() string

type ImplicitTypeConversionRule added in v1.14.0 

type ImplicitTypeConversionRule struct{}

ImplicitTypeConversionRule detects CAST expressions wrapping a column in WHERE conditions. Wrapping a column in CAST/CONVERT prevents the database from using an index on that column.

func (*ImplicitTypeConversionRule) Analyze added in v1.14.0 

func (r *ImplicitTypeConversionRule) Analyze(stmt ast.Statement) []Suggestion

func (*ImplicitTypeConversionRule) Description added in v1.14.0 

func (r *ImplicitTypeConversionRule) Description() string

func (*ImplicitTypeConversionRule) ID added in v1.14.0 

func (r *ImplicitTypeConversionRule) ID() string

func (*ImplicitTypeConversionRule) Name added in v1.14.0 

func (r *ImplicitTypeConversionRule) Name() string

type LeadingWildcardLikeRule 

type LeadingWildcardLikeRule struct{}

LeadingWildcardLikeRule detects LIKE patterns starting with % which prevent index usage.

func (*LeadingWildcardLikeRule) Analyze 

func (r *LeadingWildcardLikeRule) Analyze(stmt ast.Statement) []Suggestion

func (*LeadingWildcardLikeRule) Description 

func (r *LeadingWildcardLikeRule) Description() string

func (*LeadingWildcardLikeRule) ID 

func (r *LeadingWildcardLikeRule) ID() string

func (*LeadingWildcardLikeRule) Name 

func (r *LeadingWildcardLikeRule) Name() string

type MissingLimitRule added in v1.14.0 

type MissingLimitRule struct{}

MissingLimitRule flags SELECT queries with ORDER BY but no LIMIT/FETCH/TOP. Without a LIMIT, the database must sort the entire result set.

func (*MissingLimitRule) Analyze added in v1.14.0 

func (r *MissingLimitRule) Analyze(stmt ast.Statement) []Suggestion

func (*MissingLimitRule) Description added in v1.14.0 

func (r *MissingLimitRule) Description() string

func (*MissingLimitRule) ID added in v1.14.0 

func (r *MissingLimitRule) ID() string

func (*MissingLimitRule) Name added in v1.14.0 

func (r *MissingLimitRule) Name() string

type MissingWhereRule 

type MissingWhereRule struct{}

MissingWhereRule detects UPDATE/DELETE statements without a WHERE clause.

func (*MissingWhereRule) Analyze 

func (r *MissingWhereRule) Analyze(stmt ast.Statement) []Suggestion

func (*MissingWhereRule) Description 

func (r *MissingWhereRule) Description() string

func (*MissingWhereRule) ID 

func (r *MissingWhereRule) ID() string

func (*MissingWhereRule) Name 

func (r *MissingWhereRule) Name() string

type NotInSubqueryNullRule added in v1.14.0 

type NotInSubqueryNullRule struct{}

NotInSubqueryNullRule flags NOT IN (subquery) patterns. If the subquery returns any NULL, the entire NOT IN evaluates to an empty result set.

func (*NotInSubqueryNullRule) Analyze added in v1.14.0 

func (r *NotInSubqueryNullRule) Analyze(stmt ast.Statement) []Suggestion

func (*NotInSubqueryNullRule) Description added in v1.14.0 

func (r *NotInSubqueryNullRule) Description() string

func (*NotInSubqueryNullRule) ID added in v1.14.0 

func (r *NotInSubqueryNullRule) ID() string

func (*NotInSubqueryNullRule) Name added in v1.14.0 

func (r *NotInSubqueryNullRule) Name() string

type OptimizationResult 

type OptimizationResult struct {
	Suggestions     []Suggestion // All suggestions from all rules
	QueryComplexity string       // One of ComplexitySimple, ComplexityModerate, ComplexityComplex
	Score           int          // 0-100 where 100 is optimal (no suggestions)
}

OptimizationResult contains the full output of an optimization analysis.

type Optimizer 

type Optimizer struct {
	// contains filtered or unexported fields
}

Optimizer analyzes parsed SQL ASTs and produces optimization suggestions.

An Optimizer is configured with a set of Rules. The default set includes all built-in rules (OPT-001 through OPT-008). The Optimizer is safe for concurrent use after creation.

func New 

func New() *Optimizer

New creates a new Optimizer with all built-in optimization rules enabled.

func NewWithRules 

func NewWithRules(rules ...Rule) *Optimizer

NewWithRules creates a new Optimizer with the specified rules.

func (*Optimizer) Analyze 

func (o *Optimizer) Analyze(tree *ast.AST) *OptimizationResult

Analyze inspects all statements in the given AST and returns an OptimizationResult.

Each configured rule is applied to every statement in the AST. The result includes all suggestions, a complexity classification, and an optimization score.

func (*Optimizer) AnalyzeSQL 

func (o *Optimizer) AnalyzeSQL(sql string) (*OptimizationResult, error)

AnalyzeSQL is a convenience method that parses the SQL string and analyzes it.

This method uses gosqlx.Parse internally for tokenization and parsing. If parsing fails, the error is returned and no analysis is performed.

func (*Optimizer) Rules 

func (o *Optimizer) Rules() []Rule

Rules returns the list of rules configured for this optimizer.

type OrInWhereRule 

type OrInWhereRule struct{}

OrInWhereRule warns about OR conditions that may prevent index usage.

func (*OrInWhereRule) Analyze 

func (r *OrInWhereRule) Analyze(stmt ast.Statement) []Suggestion

func (*OrInWhereRule) Description 

func (r *OrInWhereRule) Description() string

func (*OrInWhereRule) ID 

func (r *OrInWhereRule) ID() string

func (*OrInWhereRule) Name 

func (r *OrInWhereRule) Name() string

type OrToInConversionRule added in v1.14.0 

type OrToInConversionRule struct{}

OrToInConversionRule detects three or more OR equality conditions on the same column. col = A OR col = B OR col = C is more readable and sometimes faster as col IN (A, B, C).

func (*OrToInConversionRule) Analyze added in v1.14.0 

func (r *OrToInConversionRule) Analyze(stmt ast.Statement) []Suggestion

func (*OrToInConversionRule) Description added in v1.14.0 

func (r *OrToInConversionRule) Description() string

func (*OrToInConversionRule) ID added in v1.14.0 

func (r *OrToInConversionRule) ID() string

func (*OrToInConversionRule) Name added in v1.14.0 

func (r *OrToInConversionRule) Name() string

type RedundantOrderByInCTERule added in v1.14.0 

type RedundantOrderByInCTERule struct{}

RedundantOrderByInCTERule detects ORDER BY inside a CTE definition without LIMIT. In most databases (PostgreSQL, MySQL, SQL Server, ClickHouse), ORDER BY inside a CTE is ignored unless paired with LIMIT/TOP/FETCH.

func (*RedundantOrderByInCTERule) Analyze added in v1.14.0 

func (r *RedundantOrderByInCTERule) Analyze(stmt ast.Statement) []Suggestion

func (*RedundantOrderByInCTERule) Description added in v1.14.0 

func (r *RedundantOrderByInCTERule) Description() string

func (*RedundantOrderByInCTERule) ID added in v1.14.0 

func (r *RedundantOrderByInCTERule) ID() string

func (*RedundantOrderByInCTERule) Name added in v1.14.0 

func (r *RedundantOrderByInCTERule) Name() string

type Rule 

type Rule interface {
	// ID returns the unique rule identifier (e.g., "OPT-001").
	ID() string

	// Name returns a short human-readable rule name.
	Name() string

	// Description returns a detailed description of what the rule checks.
	Description() string

	// Analyze inspects a single statement and returns suggestions.
	Analyze(stmt ast.Statement) []Suggestion
}

Rule defines the interface that all optimization rules must implement.

Each rule inspects a single AST statement and returns zero or more Suggestions. Rules should be stateless and safe for concurrent use.

func DefaultRules 

func DefaultRules() []Rule

DefaultRules returns the default set of all built-in optimization rules.

type SelectStarRule 

type SelectStarRule struct{}

SelectStarRule detects SELECT * and recommends listing columns explicitly.

func (*SelectStarRule) Analyze 

func (r *SelectStarRule) Analyze(stmt ast.Statement) []Suggestion

func (*SelectStarRule) Description 

func (r *SelectStarRule) Description() string

func (*SelectStarRule) ID 

func (r *SelectStarRule) ID() string

func (*SelectStarRule) Name 

func (r *SelectStarRule) Name() string

type SubqueryInWhereRule 

type SubqueryInWhereRule struct{}

SubqueryInWhereRule suggests converting subqueries in WHERE to JOINs.

func (*SubqueryInWhereRule) Analyze 

func (r *SubqueryInWhereRule) Analyze(stmt ast.Statement) []Suggestion

func (*SubqueryInWhereRule) Description 

func (r *SubqueryInWhereRule) Description() string

func (*SubqueryInWhereRule) ID 

func (r *SubqueryInWhereRule) ID() string

func (*SubqueryInWhereRule) Name 

func (r *SubqueryInWhereRule) Name() string

type Suggestion 

type Suggestion struct {
	RuleID       string // Unique rule identifier (e.g., "OPT-001")
	Severity     string // One of SeverityInfo, SeverityWarning, SeverityError
	Message      string // Short description of the issue
	Detail       string // Detailed explanation and rationale
	Line         int    // Source line (1-based, 0 if unknown)
	Column       int    // Source column (1-based, 0 if unknown)
	OriginalSQL  string // The problematic SQL fragment (if available)
	SuggestedSQL string // Suggested rewrite (if available)
}

Suggestion represents a single optimization recommendation produced by a Rule.

type UnionDeduplicationRule added in v1.14.0 

type UnionDeduplicationRule struct{}

UnionDeduplicationRule warns when UNION (without ALL) is used. UNION deduplicates results by performing an internal sort or hash; UNION ALL avoids this.

func (*UnionDeduplicationRule) Analyze added in v1.14.0 

func (r *UnionDeduplicationRule) Analyze(stmt ast.Statement) []Suggestion

func (*UnionDeduplicationRule) Description added in v1.14.0 

func (r *UnionDeduplicationRule) Description() string

func (*UnionDeduplicationRule) ID added in v1.14.0 

func (r *UnionDeduplicationRule) ID() string

func (*UnionDeduplicationRule) Name added in v1.14.0 

func (r *UnionDeduplicationRule) Name() string

type UnusedAliasRule added in v1.14.0 

type UnusedAliasRule struct{}

UnusedAliasRule detects column aliases defined in the SELECT list that are never referenced in ORDER BY or HAVING clauses.

func (*UnusedAliasRule) Analyze added in v1.14.0 

func (r *UnusedAliasRule) Analyze(stmt ast.Statement) []Suggestion

func (*UnusedAliasRule) Description added in v1.14.0 

func (r *UnusedAliasRule) Description() string

func (*UnusedAliasRule) ID added in v1.14.0 

func (r *UnusedAliasRule) ID() string

func (*UnusedAliasRule) Name added in v1.14.0 

func (r *UnusedAliasRule) Name() string

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.