SQL Expert Lab — Query Planner Internals (Expert)

🖥️

SQL Terminal

📚

Reference

🗂️

Schema

⭐⭐⭐⭐⭐ EXPERT OBJECTIVES

○EXPLAIN (ANALYZE,BUFFERS) — identify wrong plan choices

○Check pg_stats: stale statistics + skewed distributions

○Run ANALYZE with custom statistics targets (500)

○Diagnose correlation: check pg_stats.correlation

○Use CREATE STATISTICS for multi-column dependencies

○Set enable_nestloop/seqscan to diagnose planner

○Tune cost model: random_page_cost, effective_cache_size

○Achieve correct plan without permanent hints

0 / 8 complete

EXPERT SQL TERMINAL — QUERY PLANNER INTERNALS (EXPERT)

PostgreSQL 16 — Expert Lab Environment loaded ⭐ Query Planner Internals (Expert) — Expert Tier ⚠ No guidance provided. Mastery is assessed on correctness AND trade-off analysis. ⚠ Use -- comments or REPORT to document every design decision. ──────────────────────────────────────────

pg=#

KEY COMMANDS

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;
SELECT tablename,attname,n_distinct,correlation FROM pg_stats WHERE tablename=...
ANALYZE fact_events;
ALTER TABLE fact_events ALTER COLUMN region SET STATISTICS 500;
CREATE STATISTICS stat_region_date ON region,event_date FROM fact_events;
SET enable_nestloop=off; SET enable_seqscan=off;
SET random_page_cost=1.1; SET effective_cache_size='24GB';
SELECT * FROM pg_stat_user_tables WHERE relname='fact_events';

Expert tier: syntax starting points only. Your reasoning counts as much as the SQL.

SCHEMA BROWSER

Type \d or SHOW TABLES to list tables, SHOW SEARCH_PATH for active schema.

EXPERT BRIEFING

QUERY PLANNER — VERTEX ANALYTICS — EXPERT ⭐⭐⭐⭐⭐

⭐⭐⭐⭐⭐ Expert tier. No walkthrough. No syntax examples beyond the reference window. You are assessed on correctness, justification of trade-offs, and depth of reasoning.

SCENARIO

Vertex Analytics runs PostgreSQL 16 for real-time dashboards on a 200M-row fact table. The query planner is making catastrophically wrong decisions: choosing nested loop joins on 80M-row tables, ignoring partial indexes, and picking sequential scans despite excellent indexes. No hints allowed initially: diagnose WHY the planner is wrong (stale statistics, incorrect row estimates, correlation issues), fix the root cause, then use planner hints only as a last resort.

OBJECTIVES (8)

EXPLAIN (ANALYZE,BUFFERS) — identify wrong plan choices
Check pg_stats: stale statistics + skewed distributions
Run ANALYZE with custom statistics targets (500)
Diagnose correlation: check pg_stats.correlation
Use CREATE STATISTICS for multi-column dependencies
Set enable_nestloop/seqscan to diagnose planner
Tune cost model: random_page_cost, effective_cache_size
Achieve correct plan without permanent hints

EXPERT RULES

Every DDL decision must be justified with a -- comment or REPORT statement
Trade-off analysis is required for all design choices
All 8 objectives must be completed before the report is accepted
Reference window provides syntax starting points only

Display Mode