SQL Performance Tuning Examples

SELECT * FROM customers 
WHERE LOWER(email) LIKE '%john%' 
   OR LOWER(name) LIKE '%john%';

EXPLAIN ANALYZE SELECT * FROM customers WHERE LOWER(email) LIKE '%john%' OR LOWER(name) LIKE '%john%';

Seq Scan on customers (cost=0.00..25000.00 rows=1000 width=200)
  Filter: ((lower(email) ~~ '%john%') OR (lower(name) ~~ '%john%'))
  Rows Removed by Filter: 999000

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_customers_email_trgm ON customers USING GIN (email gin_trgm_ops);
CREATE INDEX idx_customers_name_trgm ON customers USING GIN (name gin_trgm_ops);

SELECT * FROM customers 
WHERE email ILIKE '%john%' 
   OR name ILIKE '%john%';

SELECT o.id, o.order_date, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2024-01-01';

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Hash Join (actual time=45123.456..45123.456 rows=500000)
  -> Seq Scan on orders (actual time=0.01..30000.00 rows=10000000)
       Filter: (order_date >= '2024-01-01')
       Rows Removed by Filter: 9500000
  -> Hash (actual time=5000.00 rows=1000000)
       -> Seq Scan on customers (actual time=0.01..3000.00 rows=1000000)

CREATE INDEX idx_orders_date ON orders(order_date);

CREATE INDEX idx_orders_customer ON orders(customer_id);

CREATE INDEX idx_orders_date_covering ON orders(order_date) 
INCLUDE (customer_id, id);

SELECT * FROM orders WHERE customer_id = 1;
SELECT * FROM orders WHERE customer_id = 2;
SELECT * FROM orders WHERE customer_id = 3;
... (1000 queries)

SELECT * FROM orders 
WHERE customer_id IN (1, 2, 3, ..., 1000)
ORDER BY customer_id;

SELECT c.id, c.name, o.*
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.id IN (1, 2, 3, ..., 1000);

DELETE FROM customers WHERE id = 12345;

EXPLAIN ANALYZE DELETE FROM customers WHERE id = 12345;

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

SELECT * FROM products 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 100000;

SELECT * FROM products 
WHERE created_at < '2024-01-15 10:30:00'
  AND (created_at < '2024-01-15 10:30:00' OR 
       (created_at = '2024-01-15 10:30:00' AND id < 50000))
ORDER BY created_at DESC, id DESC
LIMIT 20;

CREATE INDEX idx_products_created_id ON products(created_at DESC, id DESC);

SELECT 
    pg_size_pretty(pg_total_relation_size('table_name')) AS total,
    pg_size_pretty(pg_relation_size('table_name')) AS table,
    pg_size_pretty(pg_indexes_size('table_name')) AS indexes;

SELECT 
    relname,
    n_live_tup,
    n_dead_tup,
    round(100 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'table_name';

VACUUM FULL table_name;
REINDEX TABLE table_name;

ALTER TABLE table_name SET (
    autovacuum_vacuum_threshold = 100,
    autovacuum_vacuum_scale_factor = 0.05
);

SELECT * FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'pending';

ANALYZE orders;
ANALYZE order_items;

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

SELECT 
    pg_blocking_pids(pid) AS blocked_by,
    query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

WITH batch AS (
    SELECT id FROM queue_table
    WHERE status = 'pending'
    LIMIT 100
    FOR UPDATE SKIP LOCKED
)
UPDATE queue_table
SET status = 'processing', processor_id = 'worker-1'
WHERE id IN (SELECT id FROM batch);

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

SELECT 
    DATE(created_at) AS date,
    COUNT(*) AS count,
    SUM(amount) AS total
FROM transactions
WHERE created_at >= '2024-01-01'
GROUP BY DATE(created_at)
ORDER BY date;

CREATE INDEX idx_transactions_2024 ON transactions(created_at)
WHERE created_at >= '2024-01-01';

CREATE MATERIALIZED VIEW daily_transaction_summary AS
SELECT 
    DATE(created_at) AS date,
    COUNT(*) AS count,
    SUM(amount) AS total
FROM transactions
GROUP BY DATE(created_at);

CREATE INDEX idx_summary_date ON daily_transaction_summary(date);

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_transaction_summary;

SELECT cron.schedule('refresh-daily', '0 1 * * *', 
    'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_transaction_summary');

SELECT * FROM articles
WHERE title LIKE '%machine learning%'
   OR content LIKE '%machine learning%';

ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector = 
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(content, '')), 'B');

CREATE INDEX idx_articles_fts ON articles USING GIN(search_vector);

CREATE FUNCTION articles_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.content, '')), 'B');
    RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER tsvector_update BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE FUNCTION articles_search_trigger();

SELECT *, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'machine & learning') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

SELECT 
    round(total_time::numeric, 2) AS total_time_ms,
    calls,
    round((total_time / calls)::numeric, 2) AS avg_time_ms,
    query
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

WITH constants AS (
    SELECT current_setting('block_size')::numeric AS bs
),
table_stats AS (
    SELECT
        schemaname,
        tablename,
        reltuples::bigint AS row_count,
        relpages::bigint AS page_count,
        (SELECT bs FROM constants) * relpages AS table_bytes
    FROM pg_class c
    JOIN pg_stat_user_tables t ON c.relname = t.relname
    WHERE c.relkind = 'r'
)
SELECT
    schemaname,
    tablename,
    pg_size_pretty(table_bytes) AS size,
    row_count,
    page_count,
    CASE WHEN row_count > 0 
        THEN round(table_bytes / row_count, 2) 
        ELSE 0 
    END AS bytes_per_row
FROM table_stats
ORDER BY table_bytes DESC;

SELECT
    indexrelname AS index,
    relname AS table,
    idx_scan AS scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    CASE 
        WHEN idx_scan = 0 THEN 'UNUSED'
        WHEN idx_tup_read / nullif(idx_scan, 0) > 1000 THEN 'LOW SELECTIVITY'
        ELSE 'OK'
    END AS status
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;