Advanced Parallel Execution Analysis

advanced

Analyze and optimize a query using parallel execution with worker coordination

-- Large aggregation query on 50M row table

EXPLAIN (ANALYZE, BUFFERS)

SELECT

DATE_TRUNC('day', order_date) as day,

COUNT(*) as order_count,

SUM(total_amount) as revenue,

AVG(total_amount) as avg_order_value

FROM orders

WHERE order_date >= '2024-01-01'

AND order_date < '2025-01-01'

GROUP BY DATE_TRUNC('day', order_date)

ORDER BY day;

-- EXECUTION PLAN (BEFORE tuning):

-- Finalize GroupAggregate (cost=850000..852000 rows=365 width=48)

-- (actual time=45000..45100 rows=365 loops=1)

-- Group Key: (date_trunc('day', order_date))

-- -> Gather Merge (cost=850000..851500 rows=730 width=48)

-- (actual time=44900..45050 rows=730 loops=1)

-- Workers Planned: 2

-- Workers Launched: 2

-- -> Sort (cost=849000..849100 rows=365 width=48)

-- (actual time=44800..44850 rows=243 loops=3)

-- Sort Key: (date_trunc('day', order_date))

-- Sort Method: quicksort Memory: 45kB

-- Worker 0: actual time=44750..44800 rows=245 loops=1

-- Worker 1: actual time=44780..44830 rows=248 loops=1

-- -> Partial HashAggregate (cost=847000..848000 rows=365 width=48)

-- (actual time=44500..44600 rows=243 loops=3)

-- Group Key: date_trunc('day', order_date)

-- Batches: 1 Memory Usage: 65kB

-- Worker 0: actual time=44450..44550 rows=245 loops=1

-- Worker 1: actual time=44480..44580 rows=248 loops=1

-- -> Parallel Seq Scan on orders (cost=0..750000 rows=16666667 width=16)

-- (actual time=0.5..35000 rows=16666667 loops=3)

-- Filter: (order_date >= '2024-01-01' AND order_date < '2025-01-01')

-- Rows Removed by Filter: 0

-- Worker 0: actual time=0.4..34900 rows=16650000 loops=1