How to Analyze Slow Queries in BigQuery

Galaxy Glossary

How do I find and speed up slow queries in BigQuery?

Use BigQuery’s INFORMATION_SCHEMA views to detect, inspect, and optimize long-running SQL jobs.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What makes a query "slow" in BigQuery?

BigQuery flags a query as slow when slot time, execution duration, or bytes processed exceed expected thresholds. Long runtimes usually stem from scanning un-filtered columns, shuffling large joins, or missing partition pruning.

How do I list the slowest queries in the last 24 hours?

Query INFORMATION_SCHEMA.JOBS or JOB_QUERY_STATS in your project’s region.Filter on job_type = 'QUERY', state = 'DONE', and execution_time > desired threshold.

Example — top 10 slow jobs

SELECT job_id, user_email, total_slot_ms/1000 AS slot_s, total_bytes_processed/1e9 AS gb, query FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR) AND job_type='QUERY' AND state='DONE' ORDER BY total_slot_ms DESC LIMIT 10;

How can I trace slowness to specific tables?

Use region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT combined with INFORMATION_SCHEMA.JOBS_TIMELINE to see per-stage bytes read.Cross-reference referenced_tables to detect hot tables such as Orders or OrderItems.

Which query patterns should I avoid?

Full table scans without partitions, cross joins, SELECT * on wide tables, and un-bounded ORDER BYs force BigQuery to scan or shuffle TBs, slowing results and costing more.

How do I optimize slow ecommerce queries?

Partition Orders by order_date, cluster by customer_id. Add WHERE clauses on created_at for Customers. Replace SELECT * with explicit columns.Use approximate aggregation functions like APPROX_COUNT_DISTINCT when accuracy tolerance exists.

When should I create materialized views?

If a report repeatedly aggregates historical sales, build a materialized view on Orders totals per day. BigQuery auto-refreshes the delta, letting dashboards read in milliseconds.

Best practices checklist

• Always filter on partition column
• Cluster large, frequently-filtered columns
• Limit SELECT columns
• Use WITH clauses to break complex logic
• Monitor BYTES_INPUT_SCANNED in Cloud Monitoring

Why How to Analyze Slow Queries in BigQuery is important

How to Analyze Slow Queries in BigQuery Example Usage


-- Top 5 longest ecommerce queries hitting Orders last week
WITH slow AS (
  SELECT
    job_id,
    total_slot_ms/1000 AS slot_s,
    query,
    creation_time
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()
    AND state='DONE'
    AND job_type='QUERY'
    AND REGEXP_CONTAINS(lower(query), r'from\s+`shop\.orders`')
)
SELECT job_id, slot_s, SUBSTR(query,1,120) AS preview, creation_time
FROM slow
ORDER BY slot_s DESC
LIMIT 5;

How to Analyze Slow Queries in BigQuery Syntax


-- Identify slow jobs (execution > 60 s) in last day
SELECT
  job_id,
  user_email,
  creation_time,
  total_slot_ms/1000 AS slot_seconds,
  total_bytes_processed/1e9 AS gb_scanned,
  REGEXP_EXTRACT(query, r'FROM\s+(\w+\.\w+)') AS main_table
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND total_slot_ms > 60000
ORDER BY slot_seconds DESC;

-- Example slow ecommerce query we want to tune
SELECT *
FROM `shop.Orders` o
JOIN `shop.OrderItems` i ON i.order_id = o.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Optimized version using partitions & explicit columns
SELECT o.id, o.customer_id, o.total_amount, i.product_id, i.quantity
FROM `shop.Orders` o
JOIN `shop.OrderItems` i ON i.order_id = o.id
WHERE o.order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31'
  AND o.customer_id IN (SELECT id FROM `shop.Customers` WHERE created_at > '2024-01-01');

Common Mistakes

Relying on the Query History UI only. It hides slot time and bytes scanned details. Always query INFORMATION_SCHEMA for full metrics.
Scanning full tables without partitions. Orders queried without a WHERE on order_date triggers terabytes of I/O. Partition to restrict scans.