</>
sqlintermediate

Covering Index (INCLUDE Columns)

Create covering indexes with INCLUDE columns to satisfy queries entirely from the index.

sql
-- Standard B-tree index
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Covering index — includes extra columns in leaf pages
CREATE INDEX idx_orders_covering ON orders (customer_id)
  INCLUDE (total, status, created_at);

-- This query is now an index-only scan:
SELECT total, status, created_at
FROM orders
WHERE customer_id = 42;

-- Partial covering index (only active orders)
CREATE INDEX idx_active_orders ON orders (customer_id)
  INCLUDE (total)
  WHERE status = 'active';

-- Verify with EXPLAIN
EXPLAIN (ANALYZE, BUFFERS)
SELECT total FROM orders
WHERE customer_id = 42 AND status = 'active';

Use Cases

  • Index-only scans
  • Frequently queried columns
  • Query tuning

Tags

#indexing#performance#covering-index#optimization

Related Snippets

Similar patterns you can reuse in the same workflow.

sqlbeginner

EXISTS vs IN Subquery Patterns

Choose between EXISTS and IN subqueries for optimal performance based on data distribution.

Best for: Filtering with related tables

#subquery#exists
sqladvanced

SQL Index Strategy Patterns

Create effective indexes including composite, partial, covering, and expression-based indexes.

Best for: Optimizing slow database queries

#sql#indexes
sqladvanced

Read EXPLAIN ANALYZE Output

Use EXPLAIN ANALYZE to understand and optimize query execution plans.

Best for: Query performance tuning

#sql#explain
typescriptbeginner

Next.js Image Optimization Patterns

Use next/image with responsive sizes, blur placeholders, and priority loading for optimal Core Web Vitals.

Best for: Hero images

#images#optimization