Advanced

CREATE DATABASE example_61;
\c example_61;
-- => Statement execution completes
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    tags TEXT[]
);
-- => Statement execution completes
INSERT INTO articles (title, content, tags)
VALUES
    ('PostgreSQL Tutorial', 'Learn PostgreSQL database fundamentals and advanced features', ARRAY['database', 'sql', 'tutorial']),
    ('Docker Guide', 'Complete guide to Docker containers and orchestration', ARRAY['docker', 'containers', 'devops']),
    ('PostgreSQL Performance', 'Optimize PostgreSQL queries and indexes for production', ARRAY['database', 'postgresql', 'performance']);
    -- => Statement execution completes
-- GIN index for array containment
CREATE INDEX idx_articles_tags ON articles USING GIN(tags);
EXPLAIN ANALYZE
SELECT title FROM articles WHERE tags @> ARRAY['database'];
-- => Specifies source table for query
-- => Index Scan using idx_articles_tags (fast)
-- GIN index for full-text search
ALTER TABLE articles ADD COLUMN content_tsv tsvector;
UPDATE articles
SET content_tsv = to_tsvector('english', title || ' ' || content);
-- => Statement execution completes
-- => Converts text to tsvector (searchable tokens)
CREATE INDEX idx_articles_fts ON articles USING GIN(content_tsv);
-- Full-text search
SELECT title
FROM articles
-- => Specifies source table for query
WHERE content_tsv @@ to_tsquery('english', 'postgresql & database');
-- => Applies filter to rows
-- => Articles containing both 'postgresql' AND 'database'
-- => PostgreSQL Tutorial, PostgreSQL Performance
-- Rank results by relevance
SELECT
    title,
    ts_rank(content_tsv, to_tsquery('english', 'postgresql')) AS rank
    -- => Creates alias for column/table
FROM articles
-- => Specifies source table for query
WHERE content_tsv @@ to_tsquery('english', 'postgresql')
-- => Applies filter to rows
ORDER BY rank DESC;
-- => Sorts query results
-- => Orders by relevance score
-- GIN index for JSONB
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB
);
-- => Statement execution completes
INSERT INTO products (data)
VALUES
    ('{"name": "Laptop", "specs": {"cpu": "i7", "ram": 16}}'),
    ('{"name": "Phone", "specs": {"cpu": "A15", "ram": 6}}');
    -- => Statement execution completes
CREATE INDEX idx_products_data ON products USING GIN(data);
EXPLAIN ANALYZE
SELECT * FROM products WHERE data @> '{"specs": {"ram": 16}}';
-- => Specifies source table for query
-- => Index Scan using idx_products_data (fast JSONB containment)```

**Key Takeaway**: GIN indexes enable fast searches on arrays, JSONB, and full-text (tsvector). Use for containment checks (`@>`), JSONB queries, and full-text search. Much faster than sequential scans for these data types.

**Why It Matters**: GIN indexes make PostgreSQL competitive with document databases like MongoDB for JSON querying, enabling millisecond-speed containment queries on billions of JSONB documents without sacrificing ACID guarantees or relational query capabilities. Array containment queries with GIN indexes (@> operator) are 100-1000x faster than ANY() predicates on unindexed arrays, making GIN essential for tag-based filtering systems handling millions of tagged items. Full-text search with GIN indexes on tsvector columns enables Google-like search functionality directly in PostgreSQL, eliminating the need for external search engines like Elasticsearch for many use cases.

### Example 62: GiST Indexes for Geometric Data

GiST (Generalized Search Tree) indexes support geometric types, range types, and custom types. Essential for spatial queries and range overlap checks.

**Code**:

```sql
CREATE DATABASE example_62;
-- => Creates database 'example_62'
\c example_62;
-- => Statement execution completes
-- => Switches connection to example_62 database
-- Enable PostGIS extension for geometric types (if available)
-- CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE events (
    name VARCHAR(200),
    event_period DATERANGE
);
-- => Statement execution completes
INSERT INTO events (name, event_period)
-- => INSERT into events table begins
VALUES
-- => Row data values follow
    ('Conference', '[2025-12-20, 2025-12-23)'),
    -- => Row data inserted
    ('Webinar', '[2025-12-22, 2025-12-22)'),
    -- => Row data inserted
    ('Workshop', '[2025-12-25, 2025-12-27)');
    -- => Statement execution completes
-- GiST index for range types
CREATE INDEX idx_events_period ON events USING GiST(event_period);
-- => Creates index idx_events_period for faster queries
-- Find overlapping events
EXPLAIN ANALYZE
SELECT name FROM events WHERE event_period && '[2025-12-22, 2025-12-24)'::DATERANGE;
-- => Specifies source table for query
-- => Query executes and returns result set
-- => Index Scan using idx_events_period (fast overlap check)
-- => Conference, Webinar (overlap with Dec 22-24)
-- GiST for geometric types
CREATE TABLE locations (
    name VARCHAR(100),
    coordinates POINT  -- => (x, y) coordinates
);
-- => Statement execution completes
INSERT INTO locations (name, coordinates)
-- => INSERT into locations table begins
VALUES
-- => Row data values follow
    ('Office A', POINT(10, 20)),
    -- => Row data inserted
    ('Office B', POINT(15, 25)),
    -- => Row data inserted
    ('Office C', POINT(50, 60));
    -- => Statement execution completes
CREATE INDEX idx_locations_coords ON locations USING GiST(coordinates);
-- => Creates index idx_locations_coords for faster queries
-- Find nearby points (requires distance calculation)
SELECT
    name,
    coordinates <-> POINT(12, 22) AS distance
    -- => Creates alias for column/table
FROM locations
-- => Specifies source table for query
ORDER BY distance
-- => Sorts query results
-- => Sorts result set
LIMIT 2;
-- => Restricts number of rows returned
-- => Office A (closest), Office B (second closest)
-- GiST for text search (alternative to GIN)
CREATE TABLE documents (
    content TEXT,
    content_tsv tsvector
);
-- => Statement execution completes
INSERT INTO documents (content)
-- => INSERT into documents table begins
VALUES
-- => Row data values follow
    ('PostgreSQL is a powerful database'),
    -- => Row data inserted
    ('Docker simplifies deployment'),
    -- => Row data inserted
    ('PostgreSQL supports advanced indexing');
    -- => Statement execution completes
UPDATE documents SET content_tsv = to_tsvector('english', content);
-- => Updates rows matching condition
CREATE INDEX idx_documents_gist ON documents USING GiST(content_tsv);
-- => Creates index idx_documents_gist for faster queries
SELECT content
FROM documents
-- => Specifies source table for query
WHERE content_tsv @@ to_tsquery('english', 'postgresql');
-- => Applies filter to rows
-- => Filter condition for query
-- => Uses GiST index (GIN usually faster for full-text)```

**Key Takeaway**: GiST indexes support range types, geometric types, and custom operators. Use for range overlap (`&&`), nearest-neighbor searches, and spatial queries. GIN is faster for full-text search; GiST is better for range and geometric queries.

**Why It Matters**: GiST indexes enable geospatial applications (ride-sharing, delivery optimization, real estate search) to perform nearest-neighbor queries finding the closest 10 drivers to a pickup location in milliseconds across millions of geographic points. Range overlap queries with GiST on DATERANGE columns detect reservation conflicts 100x faster than equivalent date comparison logic, making booking systems responsive even with billions of reservation records. The extensibility of GiST enables custom operator classes for domain-specific data types, making PostgreSQL suitable for specialized applications (chemical structure search, network topology queries) without requiring external specialized databases.

### Example 63: Expression Indexes

Expression indexes index computed values (functions, operators) instead of raw columns - speeds up queries filtering on expressions.

```mermaid
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
graph TD
    A["Raw Data<br/>(email: 'Alice@Example.com')"]
    B["Expression<br/>LOWER(email)"]
    C["Index Stores<br/>('alice@example.com' → row ID)"]
    D["Query<br/>WHERE LOWER(email) = 'alice@example.com'"]
    E["Fast Lookup<br/>(Uses index)"]

    A --> B
    B --> C
    D --> C
    C --> E

    style A fill:#0173B2,stroke:#000,color:#fff
    style B fill:#DE8F05,stroke:#000,color:#fff
    style C fill:#029E73,stroke:#000,color:#fff
    style D fill:#CC78BC,stroke:#000,color:#fff
    style E fill:#029E73,stroke:#000,color:#fff

CREATE DATABASE example_63;
\c example_63;
-- => Statement execution completes
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100),
    created_at TIMESTAMP
);
-- => Statement execution completes
INSERT INTO users (email, created_at)
SELECT
    'user' || generate_series || '@example.com',
    NOW() - (random() * 365 || ' days')::INTERVAL
FROM generate_series(1, 10000);
-- => Specifies source table for query
-- Query filtering on LOWER(email) - slow without index
EXPLAIN ANALYZE
SELECT * FROM users WHERE LOWER(email) = 'user123@example.com';
-- => Specifies source table for query
-- => Seq Scan with filter on LOWER(email)
-- Create expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
EXPLAIN ANALYZE
SELECT * FROM users WHERE LOWER(email) = 'user123@example.com';
-- => Specifies source table for query
-- => Index Scan using idx_users_email_lower (fast)
-- Expression index on date part
CREATE INDEX idx_users_created_year
ON users(EXTRACT(YEAR FROM created_at));
-- => Specifies source table for query
EXPLAIN ANALYZE
SELECT COUNT(*) FROM users WHERE EXTRACT(YEAR FROM created_at) = 2025;
-- => Specifies source table for query
-- => Bitmap Index Scan using idx_users_created_year
-- Expression index with multiple functions
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    price DECIMAL(10, 2)
);
-- => Statement execution completes
INSERT INTO products (name, price)
SELECT
    'Product ' || generate_series,
    (random() * 1000)::DECIMAL(10, 2)
FROM generate_series(1, 10000);
-- => Specifies source table for query
-- Index on price rounded to nearest 100
CREATE INDEX idx_products_price_rounded
ON products(ROUND(price / 100) * 100);
-- => Statement execution completes
EXPLAIN ANALYZE
SELECT name, price
FROM products
-- => Specifies source table for query
WHERE ROUND(price / 100) * 100 = 500;
-- => Applies filter to rows
-- => Index Scan using idx_products_price_rounded
-- Expression index on concatenation
CREATE INDEX idx_users_email_domain
ON users(SUBSTRING(email FROM POSITION('@' IN email) + 1));
-- => Specifies source table for query
SELECT COUNT(*)
-- => Aggregate function computes summary value
FROM users
-- => Specifies source table for query
WHERE SUBSTRING(email FROM POSITION('@' IN email) + 1) = 'example.com';
-- => Specifies source table for query
-- => Uses expression index if query matches index expression exactly```

**Key Takeaway**: Expression indexes speed up queries filtering on computed values - create indexes on LOWER(), EXTRACT(), ROUND(), or custom expressions. Query WHERE clause must match index expression exactly for index to be used.

**Why It Matters**: Expression indexes eliminate the need for computed columns when indexing derived values (LOWER(email) for case-insensitive searches, date_trunc('day', timestamp) for day-level aggregations), reducing storage overhead and preventing data synchronization issues between base and computed columns. Case-insensitive email lookups with LOWER() indexes are essential for user authentication systems where user@example.com and USER@EXAMPLE.COM must be treated identically without expensive sequential scans. However, expression indexes require exact query match (WHERE LOWER(email) = LOWER('user@example.com')), making them unsuitable for ad-hoc queries where expression varies, limiting their applicability to well-defined query patterns.

### Example 64: Covering Indexes (INCLUDE clause)

Covering indexes include extra columns in leaf nodes using INCLUDE - queries can retrieve all needed columns from index alone (index-only scans).

```mermaid
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
graph TD
    A["Regular Index<br/>(customer_id)"]
    B["Covering Index<br/>(customer_id INCLUDE name, email)"]
    C["Query needs<br/>customer_id, name, email"]
    D["Index Scan<br/>(Get customer_id)"]
    E["Table Lookup<br/>(Get name, email)"]
    F["Index-Only Scan<br/>(All data in index)"]
    G["Slower<br/>(2 operations)"]
    H["Faster<br/>(1 operation)"]

    A --> D
    D --> E
    E --> G

    B --> F
    F --> H

    C --> D
    C --> F

    style A fill:#CA9161,stroke:#000,color:#fff
    style B fill:#029E73,stroke:#000,color:#fff
    style C fill:#0173B2,stroke:#000,color:#fff
    style D fill:#DE8F05,stroke:#000,color:#fff
    style E fill:#DE8F05,stroke:#000,color:#fff
    style F fill:#029E73,stroke:#000,color:#fff
    style G fill:#CA9161,stroke:#000,color:#fff
    style H fill:#029E73,stroke:#000,color:#fff

CREATE DATABASE example_64;
\c example_64;
-- => Statement execution completes
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    total DECIMAL(10, 2),
    status VARCHAR(20)
);
-- => Statement execution completes
INSERT INTO orders (customer_id, order_date, total, status)
SELECT
    (random() * 100)::INTEGER + 1,
    CURRENT_DATE - (random() * 365)::INTEGER,
    (random() * 1000)::DECIMAL(10, 2),
    CASE (random() * 3)::INTEGER
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'completed'
        ELSE 'cancelled'
    END
FROM generate_series(1, 10000);
-- => Specifies source table for query
-- Regular index (only indexed columns)
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- Query needs to visit table for non-indexed columns
EXPLAIN ANALYZE
SELECT customer_id, total, status
FROM orders
-- => Specifies source table for query
WHERE customer_id = 42;
-- => Applies filter to rows
-- => Index Scan followed by table access for total, status
DROP INDEX idx_orders_customer;
-- Covering index with INCLUDE
CREATE INDEX idx_orders_customer_covering
ON orders(customer_id) INCLUDE (total, status);
-- => Statement execution completes
-- => Index includes total and status in leaf nodes
EXPLAIN ANALYZE
SELECT customer_id, total, status
FROM orders
-- => Specifies source table for query
WHERE customer_id = 42;
-- => Applies filter to rows
-- => Index Only Scan (no table access needed!)
-- Covering index for ORDER BY queries
CREATE INDEX idx_orders_date_covering
ON orders(order_date DESC) INCLUDE (customer_id, total);
-- => Statement execution completes
EXPLAIN ANALYZE
SELECT order_date, customer_id, total
FROM orders
-- => Specifies source table for query
ORDER BY order_date DESC
-- => Sorts query results
LIMIT 10;
-- => Restricts number of rows returned
-- => Index Only Scan using idx_orders_date_covering
-- Check index usage statistics
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
-- => Specifies source table for query
WHERE tablename = 'orders';
-- => Applies filter to rows
-- => idx_scan: number of index scans
-- => idx_tup_read: tuples read from index
-- => idx_tup_fetch: tuples fetched from table (0 for index-only scans)```

**Key Takeaway**: Covering indexes with INCLUDE enable index-only scans - query retrieves all needed columns from index without accessing table. Use for frequently queried column combinations to avoid table lookups.

**Why It Matters**: Covering indexes with INCLUDE clause eliminate table heap access for queries retrieving indexed + included columns, achieving 5-10x speedups on queries that would otherwise require index scan followed by heap fetch. The separation between index key columns and INCLUDE columns enables optimal index structure where frequently filtered columns remain in B-tree structure while display-only columns ride along for free without affecting index efficiency. INCLUDE allows adding frequently accessed columns to unique indexes without making them part of the uniqueness constraint, enabling covering index benefits on unique indexes that would otherwise be limited to key columns only.

### Example 65: Index-Only Scans

Index-only scans retrieve all needed columns from index - no table access required. Requires covering index or visibility map indicating no dead tuples.

**Code**:

```sql
CREATE DATABASE example_65;
\c example_65;
-- => Statement execution completes
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE,
    name VARCHAR(200),
    price DECIMAL(10, 2),
    stock INTEGER
);
-- => Statement execution completes
INSERT INTO products (sku, name, price, stock)
SELECT
    'SKU-' || LPAD(generate_series::TEXT, 8, '0'),
    'Product ' || generate_series,
    (random() * 1000)::DECIMAL(10, 2),
    (random() * 1000)::INTEGER
FROM generate_series(1, 10000);
-- => Specifies source table for query
-- Analyze to update statistics and visibility map
VACUUM ANALYZE products;
-- => Statement execution completes
-- Query using only indexed columns
CREATE INDEX idx_products_sku ON products(sku);
EXPLAIN ANALYZE
SELECT sku FROM products WHERE sku = 'SKU-00001234';
-- => Specifies source table for query
-- => Index Only Scan using idx_products_sku
-- Query needing non-indexed column (table access required)
EXPLAIN ANALYZE
SELECT sku, name FROM products WHERE sku = 'SKU-00001234';
-- => Specifies source table for query
-- => Index Scan (must access table for name)
-- Create covering index
CREATE INDEX idx_products_sku_name ON products(sku) INCLUDE (name);
EXPLAIN ANALYZE
SELECT sku, name FROM products WHERE sku = 'SKU-00001234';
-- => Specifies source table for query
-- => Index Only Scan using idx_products_sku_name
-- Visibility map impact
-- After many UPDATE/DELETE, index-only scans may become slower
UPDATE products SET stock = stock + 1 WHERE id % 2 = 0;
-- => Applies filter to rows
-- => Creates dead tuples
EXPLAIN ANALYZE
SELECT sku FROM products WHERE sku = 'SKU-00001234';
-- => Specifies source table for query
-- => May revert to Index Scan if visibility map indicates dead tuples
-- VACUUM clears dead tuples and updates visibility map
VACUUM products;
-- => Statement execution completes
EXPLAIN ANALYZE
SELECT sku FROM products WHERE sku = 'SKU-00001234';
-- => Specifies source table for query
-- => Index Only Scan restored
-- Check visibility map
SELECT
    pg_size_pretty(pg_relation_size('products')) AS table_size,
    -- => Creates alias for column/table
    pg_size_pretty(pg_relation_size('idx_products_sku')) AS index_size;
    -- => Creates alias for column/table
-- Force index-only scan check
SET enable_seqscan = off;
-- => Statement execution completes
SET enable_indexscan = off;
-- => Statement execution completes
SET enable_indexonlyscan = on;
-- => Statement execution completes
EXPLAIN ANALYZE
SELECT sku FROM products WHERE sku LIKE 'SKU-0001%';
-- => Specifies source table for query
-- => Forces index-only scan if possible
-- Reset
RESET enable_seqscan;
-- => Statement execution completes
RESET enable_indexscan;
-- => Statement execution completes
RESET enable_indexonlyscan;
-- => Statement execution completes```

**Key Takeaway**: Index-only scans avoid table access when all needed columns are in index and visibility map confirms no dead tuples. VACUUM updates visibility map. Covering indexes enable index-only scans for more queries.

**Why It Matters**: Index-only scans reduce query I/O by 80-95% compared to regular index scans that must access heap tuples for visibility checking, making frequently-queried covering indexes essential for high-throughput read-heavy workloads. The visibility map dependency means index-only scan benefits degrade on heavily-updated tables until VACUUM runs, creating a performance cliff where query speed varies by 5-10x depending on last VACUUM timing. Autovacuum's automatic visibility map maintenance makes index-only scans reliable in production, but manual VACUUM scheduling is critical after bulk UPDATE/DELETE operations to restore index-only scan performance immediately.

## Group 2: Query Optimization

### Example 66: Analyzing Query Plans with EXPLAIN ANALYZE

EXPLAIN ANALYZE reveals actual execution metrics - use it to identify bottlenecks, verify index usage, and measure performance improvements.

```mermaid
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
graph TD
    A["SQL Query"]
    B["EXPLAIN<br/>(Plan Only - Fast)"]
    C["EXPLAIN ANALYZE<br/>(Execute + Measure)"]
    D["Estimated Plan<br/>(No execution)"]
    E["Actual Plan<br/>(Real timing)"]
    F["Estimated Rows<br/>Estimated Cost"]
    G["Actual Rows<br/>Actual Time<br/>Buffers Used"]
    H["Find Slow Operations"]

    A --> B
    A --> C
    B --> D
    C --> E
    D --> F
    E --> G
    G --> H

    style A fill:#0173B2,stroke:#000,color:#fff
    style B fill:#DE8F05,stroke:#000,color:#fff
    style C fill:#029E73,stroke:#000,color:#fff
    style D fill:#CC78BC,stroke:#000,color:#fff
    style E fill:#029E73,stroke:#000,color:#fff
    style F fill:#CA9161,stroke:#000,color:#fff
    style G fill:#029E73,stroke:#000,color:#fff
    style H fill:#029E73,stroke:#000,color:#fff

CREATE DATABASE example_66;
\c example_66;
-- => Statement execution completes
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    product_id INTEGER,
    order_date DATE,
    total DECIMAL(10, 2)
);
-- => Statement execution completes
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    country VARCHAR(50)
);
-- => Statement execution completes
INSERT INTO customers (name, country)
SELECT
    'Customer ' || generate_series,
    CASE (generate_series % 3)
        WHEN 0 THEN 'USA'
        WHEN 1 THEN 'UK'
        ELSE 'Canada'
    END
FROM generate_series(1, 1000);
-- => Specifies source table for query
INSERT INTO orders (customer_id, product_id, order_date, total)
SELECT
    (random() * 1000)::INTEGER + 1,
    (random() * 100)::INTEGER + 1,
    CURRENT_DATE - (random() * 365)::INTEGER,
    (random() * 1000)::DECIMAL(10, 2)
FROM generate_series(1, 100000);
-- => Specifies source table for query
-- EXPLAIN shows estimated costs (no execution)
EXPLAIN
SELECT * FROM orders WHERE customer_id = 42;
-- => Specifies source table for query
-- => Shows: Seq Scan on orders
-- => Cost estimate: 0.00..2000.00 (startup..total)
-- => Rows estimate: ~100
-- EXPLAIN ANALYZE executes and shows actual metrics
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- => Specifies source table for query
-- => Shows: actual time, actual rows, loops
-- => Planning Time: X ms, Execution Time: Y ms
-- EXPLAIN with BUFFERS (cache hit rate)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;
-- => Specifies source table for query
-- => Shows: shared hit (cache), read (disk), written
-- Complex query analysis
EXPLAIN ANALYZE
SELECT c.name, COUNT(*) AS order_count, SUM(o.total) AS total_spent
-- => Creates alias for column/table
FROM customers c
-- => Specifies source table for query
INNER JOIN orders o ON c.id = o.customer_id
WHERE c.country = 'USA'
-- => Applies filter to rows
GROUP BY c.id, c.name
-- => Aggregates rows by specified columns
ORDER BY total_spent DESC
-- => Sorts query results
LIMIT 10;
-- => Restricts number of rows returned
-- => Shows: Hash Join or Nested Loop, HashAggregate, Sort
-- => Look for high-cost nodes
-- Create indexes and compare
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_customers_country ON customers(country);
VACUUM ANALYZE orders;
-- => Statement execution completes
VACUUM ANALYZE customers;
-- => Statement execution completes
EXPLAIN ANALYZE
SELECT c.name, COUNT(*) AS order_count
-- => Creates alias for column/table
FROM customers c
-- => Specifies source table for query
INNER JOIN orders o ON c.id = o.customer_id
WHERE c.country = 'USA'
-- => Applies filter to rows
GROUP BY c.id, c.name;
-- => Aggregates rows by specified columns
-- => Now uses Index Scan instead of Seq Scan
-- => Compare execution time
-- JSON output for programmatic analysis
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 42;
-- => Specifies source table for query
-- => Returns JSON with detailed metrics
-- Check planner statistics
SHOW default_statistics_target;
-- => Statement execution completes
-- => Default: 100 (higher = more accurate estimates, slower ANALYZE)
-- Update statistics for better estimates
ANALYZE customers;
-- => Statement execution completes
ANALYZE orders;
-- => Statement execution completes```

**Key Takeaway**: EXPLAIN ANALYZE reveals actual performance - look for Seq Scan on large tables (add indexes), high costs, slow actual times. Use BUFFERS to check cache hit rates. VACUUM ANALYZE updates statistics for accurate query planning.

**Why It Matters**: EXPLAIN ANALYZE reveals the gap between estimated and actual row counts that causes query planner mistakes leading to wrong index choices or suboptimal join strategies, making it essential for diagnosing performance issues in production queries. The BUFFERS option exposes cache hit ratios showing whether queries run from memory (shared buffers hit) or disk (read), guiding decisions on shared_buffers sizing and query optimization priorities. Companies like GitLab mandate EXPLAIN ANALYZE for all database migrations to catch performance regressions before deployment, preventing incidents where poorly-optimized queries degrade user experience for millions of users.

### Example 67: Join Order Optimization

PostgreSQL's query planner chooses join order based on statistics - smaller tables typically joined first. Use ANALYZE to update statistics for optimal join order.

```mermaid
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
graph TD
    A["Small Table<br/>(1,000 rows)"]
    B["Medium Table<br/>(10,000 rows)"]
    C["Large Table<br/>(100,000 rows)"]
    D["Join A + B<br/>(Smaller First)"]
    E["Join Result + C"]
    F["Final Result"]

    A --> D
    B --> D
    D --> E
    C --> E
    E --> F

    style A fill:#0173B2,stroke:#000,color:#fff
    style B fill:#DE8F05,stroke:#000,color:#fff
    style C fill:#029E73,stroke:#000,color:#fff
    style D fill:#CC78BC,stroke:#000,color:#fff
    style E fill:#CC78BC,stroke:#000,color:#fff
    style F fill:#CA9161,stroke:#000,color:#fff

CREATE DATABASE example_67;
\c example_67;
-- => Statement execution completes
CREATE TABLE small_table (
    id SERIAL PRIMARY KEY,
    value VARCHAR(100)
);
-- => Statement execution completes
CREATE TABLE medium_table (
    id SERIAL PRIMARY KEY,
    small_id INTEGER,
    value VARCHAR(100)
);
-- => Statement execution completes
CREATE TABLE large_table (
    id SERIAL PRIMARY KEY,
    medium_id INTEGER,
    value VARCHAR(100)
);
-- => Statement execution completes
-- Insert different sizes
INSERT INTO small_table (value)
SELECT 'Value ' || generate_series FROM generate_series(1, 100);
-- => Specifies source table for query
INSERT INTO medium_table (small_id, value)
SELECT
    (random() * 100)::INTEGER + 1,
    'Value ' || generate_series
FROM generate_series(1, 10000);
-- => Specifies source table for query
INSERT INTO large_table (medium_id, value)
SELECT
    (random() * 10000)::INTEGER + 1,
    'Value ' || generate_series
FROM generate_series(1, 100000);
-- => Specifies source table for query
-- Update statistics
ANALYZE small_table;
-- => Statement execution completes
ANALYZE medium_table;
-- => Statement execution completes
ANALYZE large_table;
-- => Statement execution completes
-- Three-way join - planner chooses optimal order
EXPLAIN ANALYZE
SELECT s.value AS small_val, m.value AS medium_val, l.value AS large_val
-- => Creates alias for column/table
FROM small_table s
-- => Specifies source table for query
INNER JOIN medium_table m ON s.id = m.small_id
INNER JOIN large_table l ON m.id = l.medium_id
LIMIT 100;
-- => Restricts number of rows returned
-- => Planner likely joins small + medium first, then large
-- Force suboptimal join order (for comparison)
SET join_collapse_limit = 1;  -- => Disables join reordering
EXPLAIN ANALYZE
SELECT s.value, m.value, l.value
FROM large_table l
-- => Specifies source table for query
INNER JOIN medium_table m ON l.medium_id = m.id
INNER JOIN small_table s ON m.small_id = s.id
LIMIT 100;
-- => Restricts number of rows returned
-- => Forced to join large + medium first (slower)
RESET join_collapse_limit;
-- => Statement execution completes
-- Optimal join order (planner decides)
EXPLAIN ANALYZE
SELECT s.value, m.value, l.value
FROM large_table l
-- => Specifies source table for query
INNER JOIN medium_table m ON l.medium_id = m.id
INNER JOIN small_table s ON m.small_id = s.id
LIMIT 100;
-- => Restricts number of rows returned
-- => Planner reorders to join small + medium first
-- Create indexes to enable faster join strategies
CREATE INDEX idx_medium_small ON medium_table(small_id);
CREATE INDEX idx_large_medium ON large_table(medium_id);
EXPLAIN ANALYZE
SELECT s.value, m.value, l.value
FROM small_table s
-- => Specifies source table for query
INNER JOIN medium_table m ON s.id = m.small_id
INNER JOIN large_table l ON m.id = l.medium_id
LIMIT 100;
-- => Restricts number of rows returned
-- => May use Hash Join or Nested Loop with indexes```

**Key Takeaway**: PostgreSQL planner reorders joins based on table statistics - smaller tables joined first for efficiency. Run ANALYZE after data changes. Indexes enable faster join strategies (Hash Join, Merge Join, Nested Loop with index).

**Why It Matters**: Automatic join reordering optimizes queries where developers write joins in suboptimal order, enabling consistent performance regardless of FROM clause ordering and reducing the expertise gap between novice and expert SQL developers. Stale statistics cause the planner to choose wrong join orders (joining 10M rows instead of 1K), creating performance cliffs where queries slow by 100-1000x after data distribution changes until ANALYZE runs. The choice between Hash Join (build hashtable on smaller relation), Merge Join (presorted inputs), and Nested Loop (small outer, indexed inner) determines whether joins complete in milliseconds or minutes, making index design critical for join performance.

### Example 68: Subquery vs JOIN Performance

Subqueries can be rewritten as JOINs - JOINs are often faster because planner can optimize join order and strategies. Use EXPLAIN ANALYZE to compare.

```mermaid
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
graph TD
    A["Correlated Subquery<br/>(Row-by-row execution)"]
    B["JOIN Approach<br/>(Set-based execution)"]
    C["Outer row 1"]
    D["Execute subquery"]
    E["Outer row 2"]
    F["Execute subquery"]
    G["Slow: O(n × m)<br/>(n rows × m subquery executions)"]
    H["Hash Join or<br/>Merge Join"]
    I["Single scan of both tables"]
    J["Fast: O(n + m)<br/>(One pass per table)"]

    A --> C
    C --> D
    A --> E
    E --> F
    D --> G
    F --> G

    B --> H
    H --> I
    I --> J

    style A fill:#CA9161,stroke:#000,color:#fff
    style B fill:#029E73,stroke:#000,color:#fff
    style C fill:#0173B2,stroke:#000,color:#fff
    style D fill:#0173B2,stroke:#000,color:#fff
    style E fill:#0173B2,stroke:#000,color:#fff
    style F fill:#0173B2,stroke:#000,color:#fff
    style G fill:#CA9161,stroke:#000,color:#fff
    style H fill:#DE8F05,stroke:#000,color:#fff
    style I fill:#DE8F05,stroke:#000,color:#fff
    style J fill:#029E73,stroke:#000,color:#fff