</>
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.

#subquery#exists
typescriptbeginner

Next.js Image Optimization Patterns

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

#images#optimization
sqlintermediate

Keyset Pagination vs Offset

Efficient keyset (cursor) pagination pattern compared to traditional OFFSET for large datasets in PostgreSQL.

#pagination#performance
sqladvanced

Materialized View with Auto-Refresh

Create and maintain materialized views for expensive aggregate queries with concurrent refresh support.

#materialized-view#performance