Data AnalyticsAdvanced15 min readUpdated March 2025

Query Optimization for Analysts

Slow queries waste time and warehouse budget. Learn to read execution plans, write sargable filters, reduce scanned data and structure queries so dashboards refresh in seconds instead of minutes.

Why Queries Get Slow

Analytics queries scan large tables, so cost is usually driven by how much data is read and moved, not by how clever the SQL looks. The main culprits are scanning unneeded columns, filtering too late, and joining huge tables before reducing them.

Read the Execution Plan

`EXPLAIN` shows how the database will run your query. Look for full table scans, large row estimates, and expensive sorts or hash joins:

sql

EXPLAIN ANALYZE
SELECT c.segment, SUM(o.total_amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.segment;

-- Look for:
--   Seq Scan on orders       -> missing index or partition pruning
--   Rows Removed by Filter   -> filter applied too late
--   Sort / HashAggregate     -> memory-heavy steps

Write Sargable Filters

A filter is sargable when the database can use an index or partition to skip data. Wrapping a column in a function usually defeats this:

sql

-- Slow: function on the column prevents index use
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- Fast: range on the raw column
SELECT order_id, total_amount
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01';

-- Slow: leading wildcard cannot use a B-tree index
SELECT * FROM customers WHERE email LIKE '%@example.com';

Practical Optimization Checklist

Apply these in roughly this order:

Select only needed columns — avoid SELECT * on wide tables.
Filter early — reduce rows in a CTE or subquery before joining.
Index or partition on columns used in WHERE and JOIN.
Pre-aggregate — build summary tables for dashboards.
Avoid DISTINCT as a fix — find the join that duplicates rows instead.
Use EXISTS instead of IN for large correlated lookups.

Pre-Aggregating for Dashboards

A dashboard that scans billions of rows on every refresh is a design problem. Materialize the daily grain once:

sql

CREATE MATERIALIZED VIEW daily_sales AS
SELECT
    order_date::date  AS day,
    region,
    category,
    SUM(total_amount) AS revenue,
    COUNT(*)          AS orders
FROM orders
GROUP BY 1, 2, 3;

CREATE INDEX idx_daily_sales_day ON daily_sales (day);

-- Dashboards now query a few thousand rows
REFRESH MATERIALIZED VIEW daily_sales;

Key Takeaways

Cost is driven by data scanned and moved.
Use EXPLAIN ANALYZE to find scans and late filters.
Keep filters sargable: no functions on indexed columns.
Pre-aggregate heavy dashboards into summary tables.

Contact Us

Have a question or feedback? Fill out the form below or reach us directly at support@nvaitraining.com