SQL Optimizer Examples

🚀 Optimized SQL query

-- Keep the same logic; rely on a functional index on upper(email)
SELECT
    user_id,
    username,
    full_name,
    email
FROM
    users
WHERE
    UPPER(email) = 'JOHN.DOE@EXAMPLE.COM';

💬 Explanation

🛠️ Database indexes (optional)

-- Primary: functional index that matches the predicate
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_upper_email
  ON users (UPPER(email));

-- Optional: allow index-only scans for this query shape
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_upper_email_cover
  ON users (UPPER(email)) INCLUDE (user_id, username, full_name, email);

-- Refresh stats so the planner recognizes index selectivity
ANALYZE users;

Alternative options (only if you can change semantics/implementation consistently across the app):

• Use LOWER(email) with a matching functional index on lower(email).
• Convert email to citext and create a plain B-tree index for simpler case-insensitive comparisons.

📝 Notes and considerations

• Build indexes CONCURRENTLY on large/active tables to avoid long write locks.
• Ensure autovacuum/analyze is healthy; stale stats can push the planner toward sequential scans.
• If you parameterize the value (recommended), still keep UPPER(email) = UPPER($1) to preserve identical semantics and enable the same functional index.

🚀 Optimized SQL query

SELECT
    p.product_id,
    p.product_name,
    p.price,
    lo.last_ordered_date
FROM products AS p
LEFT JOIN (
    -- Aggregate once: exact same semantics as the correlated MAX() per product
    SELECT
        oi.product_id,
        MAX(o.order_date) AS last_ordered_date
    FROM order_items AS oi
    JOIN orders AS o ON o.order_id = oi.order_id
    GROUP BY oi.product_id
) AS lo
  ON lo.product_id = p.product_id;

💬 Explanation

🛠️ Database indexes (optional)

-- Choose based on your join/scan direction; start with the first, add the second if needed:

-- Supports grouping by product_id and subsequent join to orders via order_id
CREATE INDEX IF NOT EXISTS idx_order_items_product_order ON order_items (product_id, order_id);

-- If you often join from orders -> order_items by order_id, this helps that direction
CREATE INDEX IF NOT EXISTS idx_order_items_order_product ON order_items (order_id, product_id);

-- Ensure orders has a PK on (order_id). To enable index-only access to order_date via that join:
-- INCLUDE keeps order_id as the key while storing order_date in the index payload.
CREATE INDEX IF NOT EXISTS idx_orders_orderid_incl_date ON orders (order_id) INCLUDE (order_date);

-- After creating indexes:
ANALYZE order_items;
ANALYZE orders;

📝 Notes and considerations

• If this query runs very frequently over large historical data, consider a summary object:
  • CREATE MATERIALIZED VIEW product_last_order AS SELECT oi.product_id, MAX(o.order_date) AS last_ordered_date FROM order_items oi JOIN orders o USING (order_id) GROUP BY oi.product_id;
  • REFRESH MATERIALIZED VIEW CONCURRENTLY product_last_order; and join to it instead of raw tables.
• Compare plans with EXPLAIN (ANALYZE, BUFFERS) before/after; keep only the indexes that measurably help.

🚀 Optimized SQL query

SELECT
    u.user_id,
    u.username,
    u.registration_date
FROM users AS u
WHERE EXISTS (  -- same semantics as IN (SELECT DISTINCT ...), but allows short-circuit and avoids materialization
    SELECT 1
    FROM order_items AS oi
    JOIN orders AS o ON o.order_id = oi.order_id
    WHERE
        oi.product_id = 12345
        AND o.user_id = u.user_id
);

💬 Explanation

🛠️ Database indexes (optional)

-- Drives the lookup by product_id and supplies order_id to join back to orders
CREATE INDEX IF NOT EXISTS order_items_product_id_order_id_idx
  ON order_items (product_id, order_id);

-- Usually orders.order_id is the PK; ensure it's present
-- CREATE UNIQUE INDEX IF NOT EXISTS orders_pkey ON orders(order_id); -- if not already PK

-- Optional: if you observe plans starting from users and probing orders by user_id
-- this can help (not always necessary if starting from order_items is selective)
CREATE INDEX IF NOT EXISTS orders_user_id_order_id_idx
  ON orders (user_id, order_id);

-- If product_id=12345 is a very common, repeated predicate,
-- a partial index can be even smaller/faster:
-- CREATE INDEX IF NOT EXISTS order_items_p12345_order_id_partial_idx
--   ON order_items(order_id)
--   WHERE product_id = 12345;

📝 Notes and considerations

• Run ANALYZE on orders and order_items after creating indexes to give the planner good stats.
• Validate with EXPLAIN (ANALYZE, BUFFERS) before/after to ensure the plan becomes a semi-join with index scans.
• If product_id’s data type differs from the literal’s type, keep the same literal form (to preserve semantics) but ensure your client binds it with the correct type to avoid implicit casts that could inhibit index use.

🚀 Optimized SQL query

-- Preserves the original Cartesian-product semantics without materializing it
WITH log_stats AS (
  SELECT
    COUNT(*)        AS total_logs_rows,   -- used only to detect empty logs table
    COUNT(l.log_id) AS nonnull_logids     -- matches COUNT(l.log_id) in the original
  FROM logs l
)
SELECT
  u.username,
  COUNT(*) * ls.nonnull_logids AS log_count
FROM users u
CROSS JOIN log_stats ls
WHERE
  u.role = 'Admin'
  AND ls.total_logs_rows > 0   -- important: return no rows if logs is empty (exactly like the original)
GROUP BY
  u.username, ls.nonnull_logids;

💬 Explanation

🛠️ Database indexes (optional)

-- Helps filter admins and aggregate by username efficiently
CREATE INDEX IF NOT EXISTS users_admin_username_idx
  ON users (username)
  WHERE role = 'Admin';

-- If log_id is not already a PRIMARY KEY or indexed, consider:
-- This can enable an index-only scan for COUNT(log_id) when pages are all-visible
CREATE INDEX IF NOT EXISTS logs_log_id_idx ON logs (log_id);

📝 Notes and considerations

• Important: Introducing a join between users and logs (e.g., ON l.user_id = u.user_id) would change the result set and does not preserve the original query’s semantics. If your intent was to count logs per admin user, use an explicit JOIN on the appropriate key and GROUP BY user_id, username instead.

🚀 Optimized SQL query

SELECT
    product_id,
    product_name,
    stock_quantity
FROM
    products
WHERE
    -- Same case-insensitive substring matches, preserved 1:1
    product_name ILIKE ANY (ARRAY[
        '%obsolete%',
        '%discontinued%',
        '%legacy%',
        '%deprecated%',
        '%oldmodel%',
        '%v1_deprecated%' -- note: "_" is a single-character wildcard in ILIKE (same as original)
    ]);

💬 Explanation

🛠️ Database indexes (optional)

-- 1) Enable pg_trgm once per database
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 2) Trigram GIN index to accelerate ILIKE/LIKE/regex substring search
CREATE INDEX IF NOT EXISTS idx_products_product_name_trgm
  ON products
  USING GIN (product_name gin_trgm_ops);

-- Refresh stats after creating the index (optional but recommended)
ANALYZE products;

📝 Notes and considerations

• The query’s filtering logic is unchanged, including that "_" in '%v1_deprecated%' matches any single character (not a literal underscore). If you intended a literal underscore, escape it as '%v1_deprecated%' ESCAPE ''.
• A GIN pg_trgm index cannot be “covering” for the selected columns; the executor will still visit the heap for product_id, product_name, and stock_quantity. This is normal for trigram indexes.
• You can also test a regex alternative for readability: product_name ~* '(obsolete|discontinued|legacy|deprecated|oldmodel|v1.deprecated)'. It is semantically equivalent to the original (since '%deprecated%' already covers '%v1_deprecated%'), but keep in mind the underscore nuance if you rely on it elsewhere. Use EXPLAIN ANALYZE to compare.

🚀 Optimized SQL query

WITH order_summary AS (
  SELECT
    oi.order_id,  -- keep native type to allow index usage on joins
    -- Single scan with conditional aggregation
    SUM(oi.quantity * oi.unit_price) FILTER (WHERE (oi.quantity * oi.unit_price) >= 1000) AS high_total,
    SUM(oi.quantity * oi.unit_price) FILTER (WHERE (oi.quantity * oi.unit_price) <  1000) AS low_total
  FROM order_items AS oi
  GROUP BY oi.order_id
)
SELECT
  o.order_date,
  o.user_id,
  v.category,
  v.total
FROM orders AS o
JOIN order_summary AS os
  ON o.order_id = os.order_id
CROSS JOIN LATERAL (
  VALUES
    ('high_value'::text, os.high_total),
    ('low_value'::text, os.low_total)
) AS v(category, total)
WHERE v.total IS NOT NULL;  -- preserve original behavior: only emit categories that exist

💬 Explanation

🛠️ Database indexes (optional)

-- For the join/grouping
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items(order_id);

-- If you frequently re-use (quantity * unit_price) elsewhere, consider:
-- 1) A generated column for line_total (Postgres 12+):
ALTER TABLE order_items
  ADD COLUMN line_total numeric GENERATED ALWAYS AS (quantity * unit_price) STORED;

-- 2) Indexes on the generated column (useful if you ever filter by thresholds in other queries):
CREATE INDEX IF NOT EXISTS idx_order_items_line_total ON order_items(line_total);
CREATE INDEX IF NOT EXISTS idx_order_items_line_total_high ON order_items(line_total) WHERE line_total >= 1000;
CREATE INDEX IF NOT EXISTS idx_order_items_line_total_low  ON order_items(line_total) WHERE line_total < 1000;

-- After schema/index changes:
ANALYZE order_items;

📝 Notes and considerations

• The optimized query returns exactly the same rows and totals as the original: one row per (order, category) only when the order has at least one line in that category; totals are identical.
• If the workload runs this aggregation often and data is large but relatively stable, consider a materialized view of per-order high/low totals, refreshed on schedule or after ETL. Use indexes on the MV for fast joins.