Advanced

CREATE DATABASE example_61;
-- => Creates new database for isolation
-- => Database "example_61" created successfully
\c example_61;
-- => Switches to newly created database
-- => Connection established to example_61

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    -- => Auto-incrementing ID column
    -- => Starts at 1, increments by 1 for each new row
    title VARCHAR(200),
    -- => Variable-length string up to 200 characters
    content TEXT,
    -- => Unlimited-length text field for article body
    tags TEXT[]
    -- => Array of text values for categorization
);
-- => Table created with 4 columns

INSERT INTO articles (title, content, tags)
-- => Inserts three rows in single statement
VALUES
    ('PostgreSQL Tutorial',
     'Learn PostgreSQL database fundamentals and advanced features',
     ARRAY['database', 'sql', 'tutorial']),
     -- => First article with 3 tags
    ('Docker Guide',
     'Complete guide to Docker containers and orchestration',
     ARRAY['docker', 'containers', 'devops']),
     -- => Second article with 3 tags
    ('PostgreSQL Performance',
     'Optimize PostgreSQL queries and indexes for production',
     ARRAY['database', 'postgresql', 'performance']);
     -- => Third article with 3 tags
-- => 3 rows inserted

CREATE INDEX idx_articles_tags ON articles USING GIN(tags);
-- => Creates GIN index on tags array column
-- => Enables fast array containment queries with @> operator
-- => Index type: GIN (Generalized Inverted Index)

EXPLAIN ANALYZE
SELECT title FROM articles WHERE tags @> ARRAY['database'];
-- => Shows query execution plan with actual timing
-- => @> operator checks if left array contains right array
-- => Expected: Index Scan using idx_articles_tags
-- => Returns: 'PostgreSQL Tutorial', 'PostgreSQL Performance'

ALTER TABLE articles ADD COLUMN content_tsv tsvector;
-- => Adds new column to store tokenized text
-- => tsvector type stores searchable text representation
-- => Column initially NULL for all rows

UPDATE articles
SET content_tsv = to_tsvector('english', title || ' ' || content);
-- => Concatenates title and content
-- => Converts to searchable tokens using English dictionary
-- => Removes stop words (the, a, an, etc.)
-- => Normalizes words to base form (databases → database)
-- => Updates all 3 rows

CREATE INDEX idx_articles_fts ON articles USING GIN(content_tsv);
-- => Creates GIN index on tsvector column
-- => Enables fast full-text search queries
-- => Index stores: token → list of rows containing token

SELECT title
FROM articles
WHERE content_tsv @@ to_tsquery('english', 'postgresql & database');
-- => @@ operator performs full-text match
-- => & operator requires BOTH terms present
-- => Query: rows containing "postgresql" AND "database"
-- => Returns: 'PostgreSQL Tutorial', 'PostgreSQL Performance'

SELECT
    title,
    ts_rank(content_tsv, to_tsquery('english', 'postgresql')) AS rank
    -- => Computes relevance score (0.0 to 1.0)
    -- => Higher rank = more occurrences of search term
    -- => Considers term frequency and document length
FROM articles
WHERE content_tsv @@ to_tsquery('english', 'postgresql')
-- => Filters to rows containing 'postgresql'
ORDER BY rank DESC;
-- => Sorts results by relevance (most relevant first)
-- => Returns rows with computed rank scores

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB
    -- => JSONB stores JSON with binary encoding
    -- => Supports indexing and efficient queries
);

INSERT INTO products (data)
VALUES
    ('{"name": "Laptop", "specs": {"cpu": "Intel i7", "ram": "16GB"}, "tags": ["electronics", "computers"]}'),
    -- => Nested JSON with object and array
    ('{"name": "Mouse", "specs": {"type": "wireless", "dpi": 1600}, "tags": ["electronics", "accessories"]}'),
    -- => Different structure (flexible schema)
    ('{"name": "Desk", "specs": {"material": "wood", "height": "adjustable"}, "tags": ["furniture"]}');
    -- => JSONB accepts varying field structures

CREATE INDEX idx_products_data ON products USING GIN(data);
-- => GIN index on entire JSONB column
-- => Enables fast queries on any JSON field
-- => Indexes all keys and values

SELECT data->>'name' AS name
-- => ->> operator extracts JSON field as text
-- => Returns string, not JSON
FROM products
WHERE data @> '{"tags": ["electronics"]}';
-- => @> checks if left JSON contains right JSON
-- => Checks if tags array includes "electronics"
-- => Returns: Laptop, Mouse

SELECT data->>'name' AS name
FROM products
WHERE data->'specs'->>'cpu' = 'Intel i7';
-- => -> operator extracts nested JSON object
-- => ->> extracts final value as text
-- => Navigates: data → specs → cpu
-- => Returns: Laptop

CREATE INDEX idx_products_tags ON products USING GIN((data->'tags'));
-- => GIN index on specific JSONB path
-- => Indexes only the tags array field
-- => Parentheses required for expression index
-- => More efficient than indexing entire JSONB

SELECT data->>'name' AS name
FROM products
WHERE data->'tags' @> '["furniture"]';
-- => Uses specific path index idx_products_tags
-- => Faster than full JSONB index for tag queries
-- => Returns: Desk

CREATE DATABASE example_62;
-- => Creates database for GiST (Generalized Search Tree) index examples
\c example_62;
-- => Switches to example_62 database

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    -- => id: location identifier (auto-incrementing)
    name VARCHAR(100),
    -- => name: location name (Office A, Office B, Office C)
    coordinates POINT
    -- => coordinates: geometric point type (x, y) or (longitude, latitude)
    -- => POINT is built-in PostgreSQL geometric type
);
-- => Creates locations table with POINT column for spatial queries

INSERT INTO locations (name, coordinates)
-- => Inserts 3 US office locations
VALUES
    ('Office A', POINT(40.7128, -74.0060)),
    -- => Office A: New York City (latitude 40.7128, longitude -74.0060)
    ('Office B', POINT(34.0522, -118.2437)),
    -- => Office B: Los Angeles (latitude 34.0522, longitude -118.2437)
    ('Office C', POINT(41.8781, -87.6298));
    -- => Office C: Chicago (latitude 41.8781, longitude -87.6298)

CREATE INDEX idx_locations_coords ON locations USING GiST(coordinates);
-- => Creates GiST index on coordinates column
-- => USING GiST: specifies Generalized Search Tree index type
-- => GiST supports geometric operations: distance (<->), overlap (&&), containment (@>)
-- => Enables fast nearest-neighbor searches and spatial queries

SELECT name, coordinates
FROM locations
ORDER BY coordinates <-> POINT(40.7589, -73.9851)
-- => <-> operator: distance between two points
-- => Reference point: Times Square, NYC (40.7589, -73.9851)
-- => Calculates distance from each location to Times Square
-- => ORDER BY distance ascending (nearest first)
-- => Office A (NYC) closest, then Office C (Chicago), then Office B (LA)
LIMIT 3;
-- => Returns 3 nearest locations (all 3 in this case)
-- => Result: Office A (closest), Office C, Office B

EXPLAIN ANALYZE
SELECT name
FROM locations
WHERE coordinates <-> POINT(40.7589, -73.9851) < 5;
-- => Finds locations within 5 units of distance
-- => Query plan shows: Index Scan using idx_locations_coords
-- => Without GiST index: Sequential Scan (slow)

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    time_range TSRANGE
    -- => TSRANGE stores timestamp ranges
    -- => Represents periods: [start, end)
);

INSERT INTO events (name, time_range)
VALUES
    ('Conference', TSRANGE('2025-06-01 09:00', '2025-06-01 17:00')),
    -- => 8-hour event on June 1
    ('Workshop', TSRANGE('2025-06-01 14:00', '2025-06-01 16:00')),
    -- => 2-hour event overlapping with conference
    ('Dinner', TSRANGE('2025-06-01 19:00', '2025-06-01 21:00'));
    -- => 2-hour event after conference

CREATE INDEX idx_events_time ON events USING GiST(time_range);
-- => GiST index for range overlap queries
-- => Enables fast overlap detection

SELECT name
FROM events
WHERE time_range @> '2025-06-01 15:00'::TIMESTAMP;
-- => @> checks if range contains timestamp
-- => Returns events active at 3 PM
-- => Result: Conference, Workshop (both active at 15:00)

SELECT e1.name AS event1, e2.name AS event2
FROM events e1
JOIN events e2 ON e1.id < e2.id
-- => Self-join to find distinct pairs
-- => Avoids duplicates (A,B) and (B,A)
WHERE e1.time_range && e2.time_range;
-- => && operator checks range overlap
-- => Returns pairs with overlapping times
-- => Result: (Conference, Workshop) - they overlap

SELECT name,
       lower(time_range) AS start_time,
       -- => lower() extracts range start bound
       upper(time_range) AS end_time,
       -- => upper() extracts range end bound
       upper(time_range) - lower(time_range) AS duration
       -- => Computes interval between bounds
FROM events
WHERE time_range -|- TSRANGE('2025-06-01 17:00', '2025-06-01 19:00');
-- => -|- checks if ranges are adjacent (no gap)
-- => Finds events immediately before or after range
-- => Result: Conference (ends at 17:00), Dinner (starts at 19:00)

CREATE TABLE ip_ranges (
    id SERIAL PRIMARY KEY,
    network VARCHAR(50),
    ip_range INET
    -- => INET stores IPv4/IPv6 addresses with optional netmask
);

INSERT INTO ip_ranges (network, ip_range)
VALUES
    ('Office Network', '192.168.1.0/24'::INET),
    -- => /24 netmask = 256 addresses (192.168.1.0 - 192.168.1.255)
    ('Guest Network', '192.168.2.0/24'::INET),
    ('VPN Network', '10.0.0.0/16'::INET);
    -- => /16 netmask = 65,536 addresses

CREATE INDEX idx_ip_ranges ON ip_ranges USING GiST(ip_range);
-- => GiST index for IP address containment
-- => Enables fast network membership checks

SELECT network
FROM ip_ranges
WHERE ip_range >> '192.168.1.100'::INET;
-- => >> checks if network contains IP address
-- => Result: Office Network (192.168.1.0/24 contains 192.168.1.100)

SELECT network
FROM ip_ranges
WHERE ip_range && '192.168.0.0/16'::INET;
-- => && checks if networks overlap
-- => 192.168.0.0/16 contains 192.168.1.0/24 and 192.168.2.0/24
-- => Result: Office Network, Guest Network

CREATE DATABASE example_63;
-- => Creates database for expression index examples
\c example_63;
-- => Switches to example_63

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100),
    -- => Stores email addresses with mixed case
    created_at TIMESTAMP
    -- => Stores account creation time
);

INSERT INTO users (email, created_at)
SELECT
    'user' || generate_series || '@example.com',
    -- => Generates emails: user1@example.com, user2@example.com, ...
    NOW() - (random() * 365 || ' days')::INTERVAL
    -- => Random timestamp within past year
    -- => random() generates 0.0 to 1.0
FROM generate_series(1, 10000);
-- => Creates 10,000 test rows
-- => 10,000 rows inserted

EXPLAIN ANALYZE
SELECT * FROM users WHERE LOWER(email) = 'user123@example.com';
-- => Query with function call on column
-- => Expected plan: Seq Scan on users
-- => Filter: LOWER(email) = 'user123@example.com'
-- => Execution time: ~50ms for 10,000 rows (no index)

CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- => Expression index on lowercased email
-- => Stores computed values: lower(email) → row ID
-- => Index type: B-tree (default)

EXPLAIN ANALYZE
SELECT * FROM users WHERE LOWER(email) = 'user123@example.com';
-- => Same query after index creation
-- => Expected plan: Index Scan using idx_users_email_lower
-- => Execution time: ~0.1ms (500x faster)
-- => Index lookup instead of full table scan

CREATE INDEX idx_users_created_year
ON users(EXTRACT(YEAR FROM created_at));
-- => Expression index extracting year from timestamp
-- => Enables fast queries filtering by year
-- => Stores: year → list of row IDs

EXPLAIN ANALYZE
SELECT COUNT(*) FROM users
WHERE EXTRACT(YEAR FROM created_at) = 2025;
-- => Counts users created in 2025
-- => Expected plan: Bitmap Index Scan using idx_users_created_year
-- => Bitmap Heap Scan for actual row retrieval
-- => Much faster than sequential scan

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    price DECIMAL(10, 2)
    -- => Stores price with 2 decimal places
);

INSERT INTO products (name, price)
SELECT
    'Product ' || generate_series,
    (random() * 1000)::DECIMAL(10, 2)
    -- => Random price between $0.00 and $1000.00
FROM generate_series(1, 10000);
-- => 10,000 products inserted

CREATE INDEX idx_products_price_rounded
ON products(ROUND(price / 100) * 100);
-- => Rounds price to nearest $100
-- => Example: $456.78 → $500.00
-- => Useful for price range filtering

EXPLAIN ANALYZE
SELECT name, price
FROM products
WHERE ROUND(price / 100) * 100 = 500;
-- => Finds products in $500 price bracket ($450-$549)
-- => Uses idx_products_price_rounded index
-- => Query expression MUST match index expression exactly

CREATE INDEX idx_users_email_domain
ON users(SUBSTRING(email FROM POSITION('@' IN email) + 1));
-- => Extracts domain from email
-- => POSITION('@' IN email) finds @ location
-- => SUBSTRING extracts from @ onwards
-- => Stores: domain → list of row IDs

SELECT COUNT(*)
FROM users
WHERE SUBSTRING(email FROM POSITION('@' IN email) + 1) = 'example.com';
-- => Counts users with @example.com domain
-- => Uses expression index if query matches exactly
-- => Returns: 10,000 (all test data uses example.com)

CREATE INDEX idx_users_created_month
ON users(date_trunc('month', created_at));
-- => Truncates timestamp to month start
-- => Example: 2025-06-15 14:30:00 → 2025-06-01 00:00:00
-- => Enables fast monthly aggregations

SELECT date_trunc('month', created_at) AS month,
       -- => Truncates to month for grouping
       COUNT(*) AS user_count
FROM users
WHERE created_at >= NOW() - INTERVAL '6 months'
-- => Filters to last 6 months
GROUP BY date_trunc('month', created_at)
-- => Groups by month
-- => Uses idx_users_created_month for filtering and grouping
ORDER BY month;
-- => Returns monthly user counts

CREATE DATABASE example_64;
-- => Creates database for covering index examples
\c example_64;
-- => Switches to example_64

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    -- => id: employee identifier (auto-increment)
    name VARCHAR(100),
    -- => name: employee name (generated as 'Employee N')
    email VARCHAR(100),
    -- => email: employee email address
    department VARCHAR(50),
    -- => department: one of 5 departments (Engineering, Sales, Marketing, HR, Operations)
    salary DECIMAL(10, 2)
    -- => salary: employee salary ($50k-$150k range)
);
-- => Creates employees table for covering index demonstration

INSERT INTO employees (name, email, department, salary)
-- => Generates 100,000 test employees
SELECT
    'Employee ' || generate_series,
    -- => Name: 'Employee 1', 'Employee 2', ..., 'Employee 100000'
    'emp' || generate_series || '@company.com',
    -- => Email: emp1@company.com, emp2@company.com, ...
    CASE (generate_series % 5)
        -- => Modulo 5 distributes evenly across departments
        WHEN 0 THEN 'Engineering'
        WHEN 1 THEN 'Sales'
        WHEN 2 THEN 'Marketing'
        WHEN 3 THEN 'HR'
        ELSE 'Operations'
    END,
    -- => Each department gets ~20,000 employees
    (random() * 100000 + 50000)::DECIMAL(10, 2)
    -- => Random salary: $50,000 to $150,000
FROM generate_series(1, 100000);
-- => Generates 100,000 rows
-- => INSERT completes with 100,000 test employees

CREATE INDEX idx_employees_dept ON employees(department);
-- => Regular B-tree index on department
-- => Stores only department values and row IDs

EXPLAIN ANALYZE
SELECT name, email
FROM employees
WHERE department = 'Engineering';
-- => Expected plan without covering index:
-- => Index Scan using idx_employees_dept
-- => Heap Fetches to retrieve name and email
-- => Two I/O operations: index lookup + heap access

DROP INDEX idx_employees_dept;
-- => Removes regular index to demonstrate covering index

CREATE INDEX idx_employees_dept_covering
ON employees(department) INCLUDE (name, email);
-- => Covering index stores department (indexed)
-- => INCLUDE adds name, email to index leaf nodes
-- => name and email not part of index key (not sortable)
-- => But stored in index for retrieval

EXPLAIN ANALYZE
SELECT name, email
FROM employees
WHERE department = 'Engineering';
-- => Expected plan with covering index:
-- => Index Only Scan using idx_employees_dept_covering
-- => All data retrieved from index (no heap access)
-- => Single I/O operation (up to 50% faster)

SELECT pg_size_pretty(pg_relation_size('employees')) AS table_size,
       -- => Human-readable table size
       pg_size_pretty(pg_relation_size('idx_employees_dept_covering')) AS index_size;
       -- => Human-readable index size
-- => Covering index larger than regular index
-- => Tradeoff: increased index size for reduced query time

CREATE INDEX idx_employees_salary_range
ON employees(salary) INCLUDE (name, department);
-- => Covering index for salary range queries
-- => salary is indexed (sortable, range-searchable)
-- => name, department stored in leaf nodes

EXPLAIN ANALYZE
SELECT name, department
FROM employees
WHERE salary BETWEEN 80000 AND 90000
-- => Range query on indexed column
ORDER BY salary;
-- => Index-Only Scan using idx_employees_salary_range
-- => No heap access needed (all data in index)
-- => Results already sorted by salary

CREATE INDEX idx_employees_email_unique_covering
ON employees(email) INCLUDE (name, department);
-- => Covering index on unique column
-- => email indexed for lookups
-- => name, department available without heap access

EXPLAIN ANALYZE
SELECT name, department
FROM employees
WHERE email = 'emp12345@company.com';
-- => Point lookup by email
-- => Index Only Scan using idx_employees_email_unique_covering
-- => Returns name and department without heap access
-- => Fastest possible lookup pattern

VACUUM ANALYZE employees;
-- => Updates visibility map for index-only scans
-- => Collects statistics for query planner
-- => Required for index-only scan optimization

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    total DECIMAL(10, 2),
    status VARCHAR(20)
);

INSERT INTO orders (customer_id, order_date, total, status)
SELECT
    (random() * 10000)::INTEGER,
    -- => Random customer ID
    CURRENT_DATE - (random() * 365)::INTEGER,
    -- => Random date within past year
    (random() * 1000)::DECIMAL(10, 2),
    -- => Random total amount
    CASE (random() * 3)::INTEGER
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'shipped'
        ELSE 'delivered'
    END
    -- => Random order status
FROM generate_series(1, 500000);
-- => 500,000 orders created

CREATE INDEX idx_orders_customer_covering
ON orders(customer_id, order_date DESC)
INCLUDE (total, status);
-- => Composite index on customer_id and order_date
-- => order_date sorted descending (newest first)
-- => total, status stored in leaf nodes
-- => Enables index-only scan for customer order history

EXPLAIN ANALYZE
SELECT order_date, total, status
FROM orders
WHERE customer_id = 5000
-- => Filters by customer
ORDER BY order_date DESC
-- => Sorts by date descending
LIMIT 10;
-- => Returns 10 most recent orders
-- => Index Only Scan using idx_orders_customer_covering
-- => All data from index, no heap access
-- => Optimal query pattern for covering index

CREATE DATABASE example_65;
-- => Creates database for index-only scan examples
\c example_65;
-- => Switches to example_65

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    -- => Auto-incrementing integer primary key
    sku VARCHAR(50),
    -- => Stock Keeping Unit (unique product code)
    category VARCHAR(50),
    -- => Product category (Electronics, Clothing, Food, Books)
    price DECIMAL(10, 2)
    -- => Price with 2 decimal places
);
-- => Creates products table with 4 columns

INSERT INTO products (sku, category, price)
-- => Inserts bulk data using SELECT
SELECT
    'SKU-' || LPAD(generate_series::TEXT, 8, '0'),
    -- => Generates SKUs: SKU-00000001, SKU-00000002, ...
    -- => LPAD pads left with zeros to 8 digits
    CASE (generate_series % 4)
        WHEN 0 THEN 'Electronics'
        -- => 25% of products
        WHEN 1 THEN 'Clothing'
        -- => 25% of products
        WHEN 2 THEN 'Food'
        -- => 25% of products
        ELSE 'Books'
        -- => Remaining 25%
    END,
    -- => Distributes evenly across 4 categories
    (random() * 500 + 10)::DECIMAL(10, 2)
    -- => Random price $10 to $510
FROM generate_series(1, 200000);
-- => 200,000 products created (50,000 per category)

CREATE INDEX idx_products_category ON products(category);
-- => Regular B-tree index on category
-- => Does not support index-only scans for SELECT *

EXPLAIN ANALYZE
-- => Shows execution plan with actual runtime statistics
SELECT category
-- => Requests only category column
FROM products
WHERE category = 'Electronics';
-- => Filters to Electronics category (~50,000 rows)
-- => Before VACUUM:
-- => Index Scan using idx_products_category
-- => Heap Fetches: ~50,000 (accesses table heap)
-- => Cannot use index-only scan (visibility map not updated)

VACUUM products;
-- => Updates visibility map for all-visible pages
-- => Marks pages where all rows are visible to all transactions
-- => Required for index-only scans
-- => Process completes in ~1 second for 200k rows

EXPLAIN ANALYZE
-- => Re-runs same query after VACUUM
SELECT category
FROM products
WHERE category = 'Electronics';
-- => Same query as before
-- => After VACUUM:
-- => Index Only Scan using idx_products_category
-- => Heap Fetches: 0 (no table access)
-- => All data retrieved from index
-- => 2-3x faster than regular index scan

SELECT
    schemaname,
    -- => Schema name (usually 'public')
    tablename,
    -- => Table name
    last_vacuum,
    -- => Last manual VACUUM timestamp
    last_autovacuum,
    -- => Last autovacuum timestamp
    n_tup_ins,
    -- => Number of rows inserted since last analyze
    n_tup_upd,
    -- => Number of rows updated
    n_tup_del
    -- => Number of rows deleted
FROM pg_stat_user_tables
-- => System view tracking table statistics
WHERE tablename = 'products';
-- => Filters to products table
-- => Shows vacuum statistics
-- => Helps determine when VACUUM needed
-- => last_vacuum should be recent for index-only scans

CREATE INDEX idx_products_sku_covering
ON products(sku) INCLUDE (category, price);
-- => Covering index stores sku (indexed) and category, price (included)
-- => Enables index-only scans for queries selecting sku, category, price

VACUUM products;
-- => Updates visibility map for new index

EXPLAIN ANALYZE
-- => Tests covering index performance
SELECT sku, category, price
-- => All three columns in covering index
FROM products
WHERE sku = 'SKU-00012345';
-- => Searches for specific SKU
-- => Index Only Scan using idx_products_sku_covering
-- => Heap Fetches: 0
-- => All three columns retrieved from index
-- => No table access required

UPDATE products
-- => Modifies existing rows
SET price = price * 1.1
-- => Increases all prices by 10%
WHERE category = 'Electronics';
-- => Filters to Electronics category
-- => Updates ~50,000 rows
-- => Creates new row versions (MVCC)
-- => Old versions marked as dead tuples

EXPLAIN ANALYZE
-- => Tests query after UPDATE
SELECT category
FROM products
WHERE category = 'Electronics';
-- => Same query as earlier
-- => After UPDATE:
-- => Index Scan using idx_products_category
-- => Heap Fetches: ~50,000 (back to heap access)
-- => Updated rows not in visibility map yet
-- => Index-only scan disabled until VACUUM

VACUUM products;
-- => Updates visibility map after UPDATE
-- => Marks old row versions as removable

EXPLAIN ANALYZE
-- => Re-tests after second VACUUM
SELECT category
FROM products
WHERE category = 'Electronics';
-- => Same query again
-- => After VACUUM:
-- => Index Only Scan using idx_products_category
-- => Heap Fetches: 0
-- => Index-only scan restored

SELECT
    pg_size_pretty(pg_total_relation_size('products')) AS total_size,
    -- => Total size including table, indexes, TOAST
    -- => Returns human-readable format (e.g., "45 MB")
    pg_size_pretty(pg_relation_size('products')) AS table_size,
    -- => Table heap size only
    -- => Excludes indexes and TOAST
    pg_size_pretty(pg_indexes_size('products')) AS indexes_size;
    -- => All indexes combined size
    -- => Helps monitor index overhead
-- => Shows storage breakdown
-- => Indexes typically 20-50% of table size

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    -- => Auto-incrementing 64-bit integer
    event_type VARCHAR(50),
    -- => Event name (page_view, click, etc.)
    timestamp TIMESTAMPTZ,
    -- => Timestamp with timezone
    user_id INTEGER
    -- => Foreign key to users table
);
-- => Creates events table for analytics

INSERT INTO events (event_type, timestamp, user_id)
-- => Bulk insert analytics events
SELECT
    CASE (random() * 5)::INTEGER
        WHEN 0 THEN 'page_view'
        -- => 20% page views
        WHEN 1 THEN 'click'
        -- => 20% clicks
        WHEN 2 THEN 'purchase'
        -- => 20% purchases
        WHEN 3 THEN 'signup'
        -- => 20% signups
        ELSE 'logout'
        -- => 20% logouts
    END,
    -- => Evenly distributed event types
    NOW() - (random() * 30 || ' days')::INTERVAL,
    -- => Random timestamp within past 30 days
    -- => Simulates historical event data
    (random() * 50000)::INTEGER
    -- => Random user ID (0 to 50,000)
FROM generate_series(1, 1000000);
-- => 1 million events created (200k per type)

CREATE INDEX idx_events_type_time
ON events(event_type, timestamp);
-- => Composite index for event analysis
-- => Supports queries filtering by type and time range

VACUUM ANALYZE events;
-- => Updates visibility map and statistics
-- => ANALYZE updates table statistics for query planner

EXPLAIN ANALYZE
-- => Analyzes query for recent purchase events
SELECT event_type, timestamp
-- => Both columns in composite index
FROM events
WHERE event_type = 'purchase'
-- => Filters to purchase events (~200k rows)
  AND timestamp >= NOW() - INTERVAL '7 days'
-- => Filters to last 7 days (~23k rows)
ORDER BY timestamp DESC
-- => Sorts newest first
LIMIT 100;
-- => Returns top 100 results
-- => Index Only Scan using idx_events_type_time
-- => Heap Fetches: 0
-- => All data from index, sorted by index order
-- => No table access needed

CREATE DATABASE example_66;
-- => Creates database for query analysis
\c example_66;
-- => Switches to example_66

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    -- => Foreign key to customers (not enforced here)
    product_id INTEGER,
    -- => Foreign key to products
    quantity INTEGER,
    order_date DATE,
    total DECIMAL(10, 2)
);

INSERT INTO orders (customer_id, product_id, quantity, order_date, total)
SELECT
    (random() * 10000)::INTEGER,
    -- => Random customer ID (1-10,000)
    (random() * 5000)::INTEGER,
    -- => Random product ID (1-5,000)
    (random() * 10 + 1)::INTEGER,
    -- => Random quantity (1-10)
    CURRENT_DATE - (random() * 730)::INTEGER,
    -- => Random date within past 2 years
    (random() * 1000 + 50)::DECIMAL(10, 2)
    -- => Random total ($50-$1,050)
FROM generate_series(1, 500000);
-- => 500,000 orders created

EXPLAIN
SELECT * FROM orders WHERE customer_id = 5000;
-- => Shows estimated query plan WITHOUT execution
-- => Output: Seq Scan on orders
-- => Filter: (customer_id = 5000)
-- => Estimated rows: ~50
-- => No actual timing (no execution)

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5000;
-- => Shows ACTUAL query plan WITH execution
-- => Seq Scan on orders (actual time=45.123..92.456 rows=48)
-- => Actual rows: 48 (close to estimate)
-- => Execution time: ~92ms
-- => Scanned all 500,000 rows

CREATE INDEX idx_orders_customer ON orders(customer_id);
-- => B-tree index on customer_id
-- => Enables fast lookups by customer

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5000;
-- => After index creation:
-- => Index Scan using idx_orders_customer
-- => Actual time: ~1.2ms (75x faster)
-- => Rows: 48
-- => Uses index to locate matching rows directly

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 5000;
-- => BUFFERS option shows I/O statistics
-- => Shared hit: 15 (15 buffer cache hits)
-- => Shared read: 0 (0 disk reads - data in cache)
-- => Index blocks read from buffer cache
-- => No disk I/O (optimal)

EXPLAIN (ANALYZE, VERBOSE)
SELECT * FROM orders WHERE customer_id = 5000;
-- => VERBOSE option shows full column output list
-- => Output: id, customer_id, product_id, quantity, order_date, total
-- => Helps identify unnecessary column fetches

EXPLAIN ANALYZE
SELECT customer_id, COUNT(*), SUM(total)
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
-- => Aggregation query
HAVING COUNT(*) > 5;
-- => Filters groups after aggregation
-- => Output plan:
-- => Seq Scan on orders (filter on order_date)
-- => HashAggregate (groups by customer_id)
-- => Filter: (count(*) > 5)
-- => Shows each pipeline stage

CREATE INDEX idx_orders_date ON orders(order_date);
-- => Index on order_date for date range queries

EXPLAIN ANALYZE
SELECT customer_id, COUNT(*), SUM(total)
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING COUNT(*) > 5;
-- => After date index:
-- => Index Scan using idx_orders_date
-- => Filters to recent orders using index
-- => HashAggregate remains (no index on customer_id for grouping)
-- => Faster filter phase, same aggregation cost

EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE customer_id = 5000;
-- => COSTS OFF hides cost estimates
-- => Shows only actual execution metrics
-- => Cleaner output focusing on real performance

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE customer_id = 5000;
-- => TIMING OFF disables per-node timing (reduces overhead)
-- => SUMMARY OFF hides total execution time
-- => Useful for very fast queries where timing overhead significant

EXPLAIN ANALYZE
SELECT o.id, o.total, o.customer_id
FROM orders o
WHERE o.customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - 30
    -- => Subquery finds customers with recent orders
    GROUP BY customer_id
    HAVING COUNT(*) > 3
    -- => Filters to active customers (3+ orders)
);
-- => Nested query plan:
-- => Hash Semi Join (outer: orders, inner: subquery result)
-- => Subquery executed first (HashAggregate)
-- => Main query uses hash join to filter rows
-- => Shows subquery optimization strategy

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM orders
WHERE customer_id = 5000
  AND product_id = 123
  AND order_date >= '2024-01-01';
-- => Multi-column filter
-- => Planner chooses one index (likely idx_orders_customer)
-- => Other filters applied as heap filters
-- => Consider composite index for better performance

CREATE INDEX idx_orders_composite
ON orders(customer_id, product_id, order_date);
-- => Composite index covering all three filter columns
-- => Leftmost prefix: customer_id
-- => Can use for (customer_id), (customer_id, product_id), or all three

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM orders
WHERE customer_id = 5000
  AND product_id = 123
  AND order_date >= '2024-01-01';
-- => After composite index:
-- => Index Scan using idx_orders_composite
-- => All filters pushed to index
-- => No heap filters (optimal)
-- => significantly faster than single-column index

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 5000;
-- => FORMAT JSON outputs plan as JSON
-- => Machine-readable format for tools
-- => Includes all metrics (timing, buffers, rows)
-- => Useful for automated performance monitoring

CREATE DATABASE example_67;
-- => Creates database for join optimization examples
\c example_67;
-- => Switches to example_67

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    -- => Auto-incrementing customer ID
    name VARCHAR(100),
    -- => Customer name
    email VARCHAR(100)
    -- => Customer email
);
-- => Creates customers table (10k rows expected)

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    -- => Auto-incrementing order ID
    customer_id INTEGER,
    -- => Foreign key to customers.id
    order_date DATE,
    -- => Order placement date
    total DECIMAL(10, 2)
    -- => Order total amount
);
-- => Creates orders table (100k rows expected)

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    -- => Auto-incrementing item ID
    order_id INTEGER,
    -- => Foreign key to orders.id
    product_id INTEGER,
    -- => Product identifier
    quantity INTEGER,
    -- => Quantity ordered
    price DECIMAL(10, 2)
    -- => Item price
);
-- => Creates order_items table (500k rows expected)

INSERT INTO customers (name, email)
-- => Inserts customer data
SELECT
    'Customer ' || generate_series,
    -- => Names: Customer 1, Customer 2, ...
    'customer' || generate_series || '@email.com'
    -- => Emails: customer1@email.com, customer2@email.com, ...
FROM generate_series(1, 10000);
-- => 10,000 customers created

INSERT INTO orders (customer_id, order_date, total)
-- => Inserts order data
SELECT
    (random() * 10000 + 1)::INTEGER,
    -- => Random customer ID (1-10,000)
    -- => Each customer gets ~10 orders on average
    CURRENT_DATE - (random() * 365)::INTEGER,
    -- => Random date within past year
    -- => Simulates historical orders
    (random() * 1000)::DECIMAL(10, 2)
    -- => Random total $0-$1000
FROM generate_series(1, 100000);
-- => 100,000 orders created (avg 10 orders per customer)

INSERT INTO order_items (order_id, product_id, quantity, price)
-- => Inserts order line items
SELECT
    (random() * 100000 + 1)::INTEGER,
    -- => Random order ID (1-100,000)
    -- => Each order gets ~5 items on average
    (random() * 5000 + 1)::INTEGER,
    -- => Random product ID (1-5000)
    -- => Simulates product catalog
    (random() * 5 + 1)::INTEGER,
    -- => Random quantity (1-5 units)
    (random() * 100 + 10)::DECIMAL(10, 2)
    -- => Random price ($10-$110)
FROM generate_series(1, 500000);
-- => 500,000 order items created (avg 5 items per order)

CREATE INDEX idx_orders_customer ON orders(customer_id);
-- => Index on orders.customer_id for JOIN optimization
CREATE INDEX idx_order_items_order ON order_items(order_id);
-- => Index on order_items.order_id for JOIN optimization
-- => Both indexes critical for multi-table joins

ANALYZE customers;
-- => Updates statistics for customers table
ANALYZE orders;
-- => Updates statistics for orders table
ANALYZE order_items;
-- => Updates statistics for order_items table
-- => Collects row counts, value distributions, null counts
-- => Required for accurate join order selection
-- => Query planner uses this for cost estimates

EXPLAIN ANALYZE
-- => Analyzes two-table join execution
SELECT c.name, o.total
-- => Retrieves customer name and order total
FROM customers c
JOIN orders o ON c.id = o.customer_id
-- => Joins customers to orders
-- => Links via customer ID foreign key
WHERE c.id = 5000;
-- => Filters to single customer first
-- => Reduces join cardinality
-- => Plan: Nested Loop
-- =>   Index Scan on customers (filter: id = 5000)
-- =>   Index Scan on orders (customer_id = 5000)
-- => Nested loop chosen because filtering reduces outer rows

EXPLAIN ANALYZE
-- => Analyzes three-table join
SELECT c.name, o.total, oi.quantity
-- => Retrieves data from all three tables
FROM customers c
JOIN orders o ON c.id = o.customer_id
-- => First join: customers to orders
JOIN order_items oi ON o.id = oi.order_id
-- => Second join: orders to order_items
-- => Three-table join chain
WHERE c.id = 5000;
-- => Filters to single customer
-- => Planner evaluates join order:
-- => Option 1: (customers ⋈ orders) ⋈ order_items
-- => Option 2: (customers ⋈ order_items) ⋈ orders
-- => Chooses based on estimated result sizes
-- => Likely: customers → orders → order_items (following FK chain)

EXPLAIN ANALYZE
-- => Analyzes aggregation with join
SELECT c.name, COUNT(*) AS order_count
-- => Counts orders per customer
FROM customers c
JOIN orders o ON c.id = o.customer_id
-- => Joins all customers with all orders
GROUP BY c.id, c.name;
-- => Groups by customer
-- => Join all customers with all orders
-- => No filter reduces dataset (100k orders × 10k customers)
-- => Plan: Hash Join
-- =>   Seq Scan on customers (builds hash table)
-- =>   Seq Scan on orders (probes hash table)
-- => Hash join chosen for large dataset join
-- => More efficient than nested loop for full table joins

EXPLAIN ANALYZE
-- => Tests LEFT JOIN with filter
SELECT c.name, o.total
-- => Retrieves customer and order data
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
-- => LEFT JOIN includes customers with no orders
-- => Returns NULL for o.* when no match
WHERE o.order_date >= '2025-01-01';
-- => Filter on orders table
-- => Filters out NULL rows (customers with no orders)
-- => Converts LEFT JOIN to INNER JOIN (optimizer transformation)
-- => o.order_date >= '2025-01-01' excludes NULL rows from LEFT JOIN
-- => Plan will show INNER JOIN, not LEFT JOIN

EXPLAIN ANALYZE
-- => Tests LEFT JOIN with NULL-preserving filter
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
-- => LEFT JOIN returns all customers
WHERE o.order_date >= '2025-01-01'
   OR o.order_date IS NULL;
   -- => OR clause includes NULL dates
   -- => Preserves LEFT JOIN semantics
   -- => Includes customers with no orders (NULL dates)
   -- => Plan: Hash Right Join or Merge Join
   -- => Cannot convert to INNER JOIN
   -- => Keeps customers without orders in result

EXPLAIN ANALYZE
-- => Tests old-style comma join syntax
SELECT c.name, o.total
FROM customers c, orders o
-- => Comma syntax (implicit CROSS JOIN)
-- => Legacy SQL-89 join syntax
WHERE c.id = o.customer_id
  AND c.id = 5000;
  -- => Join condition in WHERE clause (not ON clause)
  -- => Filters to single customer
  -- => Optimizer converts to explicit JOIN
  -- => Same plan as explicit JOIN syntax
  -- => Prefer explicit JOIN for clarity and maintainability

SET join_collapse_limit = 1;
-- => Limits join reordering optimization
-- => Forces planner to respect written join order
-- => Default: 8 (reorders up to 8 tables)
-- => Useful for debugging query plans

EXPLAIN ANALYZE
-- => Tests join with reordering disabled
SELECT c.name, o.total, oi.quantity
-- => Retrieves data from three tables
FROM customers c
JOIN orders o ON c.id = o.customer_id
-- => First join as written
JOIN order_items oi ON o.id = oi.order_id
-- => Second join as written
WHERE c.id = 5000;
-- => Filters to single customer
-- => With join_collapse_limit = 1:
-- => Joins executed in written order
-- => No reordering optimization
-- => May produce suboptimal plan
-- => Useful for controlling join order manually

RESET join_collapse_limit;
-- => Restores default (8)
-- => Re-enables automatic join reordering
-- => Planner can optimize join order again

EXPLAIN (ANALYZE, BUFFERS)
-- => Analyzes with buffer I/O statistics
SELECT c.name, COUNT(*)
-- => Counts orders per customer
FROM customers c
JOIN orders o ON c.id = o.customer_id
-- => Joins customers to orders
WHERE o.order_date >= '2025-01-01'
-- => Filters to recent orders
GROUP BY c.id, c.name;
-- => Groups by customer
-- => BUFFERS option shows I/O statistics
-- => Hash Join buffer usage:
-- =>   Shared hit: X (buffer cache hits - memory reads)
-- =>   Shared read: Y (disk reads - slower)
-- =>   Temp read/written: Z (spills to disk if memory exceeded)
-- => Helps identify I/O bottlenecks in joins

EXPLAIN ANALYZE
-- => Analyzes multi-filter join query
SELECT *
-- => Retrieves all columns from all tables
FROM customers c
JOIN orders o ON c.id = o.customer_id
-- => First join
JOIN order_items oi ON o.id = oi.order_id
-- => Second join
WHERE c.email LIKE '%@email.com'
  -- => Filter on customers (low selectivity - most match)
  AND o.total > 500
  -- => Filter on orders (medium selectivity)
  AND oi.quantity > 2;
  -- => Filter on order_items (high selectivity)
  -- => Multiple filter conditions across tables
  -- => Planner estimates selectivity of each filter
  -- => Applies most selective filter first
  -- => Join order: most selective → least selective
  -- => Reduces intermediate result sizes