vertica_query_optimization

What problem does it solve?

This Skill helps you reduce slow Vertica query runtimes by diagnosing execution plans and tuning physical design (projections, segmentation, encoding) based on what Vertica actually does.

Core Features & Use Cases

• EXPLAIN-driven diagnosis: Identify performance bottlenecks by interpreting Vertica EXPLAIN tokens (e.g., JOIN type, RESEGMENT, GROUPBY algorithm, sort activity) and by correlating plan profiles with projections.
• Projection and physical tuning: Design or adjust projections to match query patterns, including ORDER BY prefix for sort elimination, SEGMENTED BY keys for avoiding resegmentation, and ENCODING choices such as RLE for low-cardinality sorted columns.
• Performance tuning for DML and diagnostics: Improve INSERT-SELECT, and understand DELETE/UPDATE internals and operational costs; use Data Collector (dc_*) tables to find slow requests and quantify durations.
• Task fit: Works when you must optimize for joins, GROUP BY, ORDER BY, analytic functions, Top-K/LIMIT behavior, predicate selectivity, and when you need to verify changes by re-running EXPLAIN.

Quick Start

Use this skill to optimize a slow Vertica 11.1 query by running EXPLAIN on the SQL, interpreting the plan (especially JOIN/GROUPBY/RESEGMENT/SORT), then updating the relevant projection design and re-running EXPLAIN to confirm the improvement.