SQL Optimizer Examples

SELECT 
    p.id,
    p.name,
    p.price,
    (SELECT COUNT(*) FROM order_items oi WHERE oi.product_id = p.id) AS order_count,
    (SELECT SUM(quantity) FROM order_items oi WHERE oi.product_id = p.id) AS total_sold,
    (SELECT AVG(rating) FROM reviews r WHERE r.product_id = p.id) AS avg_rating
FROM products p
WHERE p.category_id = 5;

SELECT 
    p.id,
    p.name,
    p.price,
    COALESCE(oi_stats.order_count, 0) AS order_count,
    COALESCE(oi_stats.total_sold, 0) AS total_sold,
    r_stats.avg_rating
FROM products p
LEFT JOIN (
    SELECT 
        product_id,
        COUNT(*) AS order_count,
        SUM(quantity) AS total_sold
    FROM order_items
    GROUP BY product_id
) oi_stats ON p.id = oi_stats.product_id
LEFT JOIN (
    SELECT 
        product_id,
        AVG(rating) AS avg_rating
    FROM reviews
    GROUP BY product_id
) r_stats ON p.id = r_stats.product_id
WHERE p.category_id = 5;

CREATE INDEX idx_order_items_product ON order_items(product_id);
CREATE INDEX idx_reviews_product ON reviews(product_id);
CREATE INDEX idx_products_category ON products(category_id);

SELECT * FROM users 
WHERE email = 'john@example.com' 
   OR phone = '555-1234'
   OR username = 'johndoe';

SELECT * FROM users WHERE email = 'john@example.com'
UNION
SELECT * FROM users WHERE phone = '555-1234'
UNION
SELECT * FROM users WHERE username = 'johndoe';

CREATE INDEX idx_users_search ON users(email, phone, username);

SELECT * FROM events
WHERE YEAR(event_date) = 2024
  AND MONTH(event_date) = 6;

SELECT * FROM events
WHERE event_date >= '2024-06-01'
  AND event_date < '2024-07-01';

CREATE INDEX idx_events_date ON events(event_date);

SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

SELECT id, title, created_at
FROM articles
WHERE 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_articles_created_id ON articles(created_at DESC, id DESC);

SELECT * FROM products
WHERE id NOT IN (
    SELECT product_id FROM discontinued_products
);

SELECT p.* FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM discontinued_products dp
    WHERE dp.product_id = p.id
);

SELECT p.* FROM products p
LEFT JOIN discontinued_products dp ON p.id = dp.product_id
WHERE dp.product_id IS NULL;

SELECT 
    (SELECT COUNT(*) FROM orders WHERE status = 'pending') AS pending_count,
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') AS completed_count,
    (SELECT COUNT(*) FROM orders WHERE status = 'cancelled') AS cancelled_count,
    (SELECT SUM(total) FROM orders WHERE status = 'completed') AS completed_revenue;

SELECT 
    COUNT(*) FILTER (WHERE status = 'pending') AS pending_count,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_count,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_count,
    SUM(total) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders;

SELECT 
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count,
    SUM(CASE WHEN status = 'completed' THEN total ELSE 0 END) AS completed_revenue
FROM orders;

SELECT c.* FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) > 0;

SELECT c.* FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

SELECT * FROM products
WHERE category_id = 5
ORDER BY name;

SELECT id, name, price, stock_quantity
FROM products
WHERE category_id = 5
ORDER BY name;

CREATE INDEX idx_products_category_name ON products(category_id, name) 
INCLUDE (id, price, stock_quantity);

SELECT c.name, COUNT(o.id) AS order_count
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE p.category_id = 5
GROUP BY c.name;

WITH category_products AS (
    SELECT id FROM products WHERE category_id = 5
),
relevant_items AS (
    SELECT oi.order_id
    FROM order_items oi
    WHERE oi.product_id IN (SELECT id FROM category_products)
)
SELECT c.name, COUNT(DISTINCT o.id) AS order_count
FROM relevant_items ri
JOIN orders o ON ri.order_id = o.id
JOIN customers c ON o.customer_id = c.id
GROUP BY c.name;

SELECT 
    t1.id,
    t1.amount,
    SUM(t2.amount) AS running_total
FROM transactions t1
JOIN transactions t2 
    ON t2.account_id = t1.account_id 
    AND t2.transaction_date <= t1.transaction_date
WHERE t1.account_id = 123
GROUP BY t1.id, t1.amount, t1.transaction_date
ORDER BY t1.transaction_date;

SELECT 
    id,
    amount,
    SUM(amount) OVER (
        ORDER BY transaction_date 
        ROWS UNBOUNDED PRECEDING
    ) AS running_total
FROM transactions
WHERE account_id = 123
ORDER BY transaction_date;

DELETE FROM logs WHERE created_at < '2023-01-01';

DO $$
DECLARE
    rows_deleted INT;
BEGIN
    LOOP
        DELETE FROM logs
        WHERE id IN (
            SELECT id FROM logs
            WHERE created_at < '2023-01-01'
            LIMIT 10000
        );
        
        GET DIAGNOSTICS rows_deleted = ROW_COUNT;
        
        EXIT WHEN rows_deleted = 0;
        
        COMMIT;
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

SELECT * FROM products WHERE name LIKE '%widget%';

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

SELECT * FROM products WHERE name LIKE '%widget%';

ALTER TABLE products ADD COLUMN search_vector tsvector;
UPDATE products SET search_vector = to_tsvector('english', name || ' ' || description);
CREATE INDEX idx_products_fts ON products USING GIN(search_vector);

SELECT * FROM products WHERE search_vector @@ to_tsquery('widget');

SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY customer_id;

SELECT id FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
      AND o.order_date >= '2024-01-01'
);

SELECT customer_id
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
ORDER BY customer_id;