explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lpLI : group by

Settings
# exclusive inclusive rows x rows loops node
1. 116.600 5,215.058 ↑ 1.0 5,544 1

Gather (cost=1,000.43..238,570.63 rows=5,544 width=44) (actual time=82.000..5,215.058 rows=5,544 loops=1)

  • Output: ('action'::text), transactions.action_id, (max(transactions.end_date_time))
  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=19,996,676 read=144,526 written=1
  • I/O Timings: read=1,757.181 write=0.434
2. 0.276 5,098.458 ↑ 1.3 1,386 4 / 4

Parallel Append (cost=0.43..237,016.23 rows=1,788 width=44) (actual time=23.341..5,098.458 rows=1,386 loops=4)

  • Buffers: shared hit=19,996,676 read=144,526 written=1
  • I/O Timings: read=1,757.181 write=0.434
  • Worker 0: actual time=4.914..5104.868 rows=651 loops=1
  • Buffers: shared hit=4,997,925 read=38,317
  • I/O Timings: read=481.046
  • Worker 1: actual time=0.984..5061.192 rows=4,501 loops=1
  • Buffers: shared hit=5,005,793 read=29,575
  • I/O Timings: read=373.291
  • Worker 2: actual time=7.564..5103.062 rows=351 loops=1
  • Buffers: shared hit=4,997,417 read=38,317
  • I/O Timings: read=496.160
3. 391.220 1,265.137 ↑ 1.0 4,501 1 / 4

GroupAggregate (cost=0.43..118,515.44 rows=4,501 width=44) (actual time=0.982..5,060.547 rows=4,501 loops=1)

  • Output: 'action'::text, transactions.action_id, max(transactions.end_date_time)
  • Group Key: transactions.action_id
  • Buffers: shared hit=5,005,793 read=29,575
  • I/O Timings: read=373.291
  • Worker 1: actual time=0.982..5060.547 rows=4,501 loops=1
  • Buffers: shared hit=5,005,793 read=29,575
  • I/O Timings: read=373.291
4. 873.917 873.917 ↑ 1.0 10,000,000 1 / 4

Index Only Scan using ix_action_id_end_date_time on stuff.transactions (cost=0.43..68,506.43 rows=10,000,000 width=12) (actual time=0.322..3,495.666 rows=10,000,000 loops=1)

  • Output: transactions.action_id, transactions.end_date_time
  • Heap Fetches: 0
  • Buffers: shared hit=5,005,793 read=29,575
  • I/O Timings: read=373.291
  • Worker 1: actual time=0.322..3495.666 rows=10,000,000 loops=1
  • Buffers: shared hit=5,005,793 read=29,575
  • I/O Timings: read=373.291
5. 384.101 1,276.144 ↑ 1.0 651 1 / 4

GroupAggregate (cost=0.43..118,507.74 rows=651 width=44) (actual time=4.912..5,104.576 rows=651 loops=1)

  • Output: 'person'::text, transactions_1.person_id, max(transactions_1.end_date_time)
  • Group Key: transactions_1.person_id
  • Buffers: shared hit=4,997,925 read=38,317
  • I/O Timings: read=481.046
  • Worker 0: actual time=4.912..5104.576 rows=651 loops=1
  • Buffers: shared hit=4,997,925 read=38,317
  • I/O Timings: read=481.046
6. 892.043 892.043 ↑ 1.0 10,000,000 1 / 4

Index Only Scan using ix_person_id_end_date_time on stuff.transactions transactions_1 (cost=0.43..68,506.43 rows=10,000,000 width=12) (actual time=1.088..3,568.172 rows=10,000,000 loops=1)

  • Output: transactions_1.person_id, transactions_1.end_date_time
  • Heap Fetches: 0
  • Buffers: shared hit=4,997,925 read=38,317
  • I/O Timings: read=481.046
  • Worker 0: actual time=1.088..3568.172 rows=10,000,000 loops=1
  • Buffers: shared hit=4,997,925 read=38,317
  • I/O Timings: read=481.046
7. 385.528 1,275.726 ↑ 1.0 351 1 / 4

GroupAggregate (cost=0.43..118,507.14 rows=351 width=44) (actual time=7.563..5,102.905 rows=351 loops=1)

  • Output: 'city'::text, transactions_2.city_id, max(transactions_2.end_date_time)
  • Group Key: transactions_2.city_id
  • Buffers: shared hit=4,997,417 read=38,317
  • I/O Timings: read=496.160
  • Worker 2: actual time=7.563..5102.905 rows=351 loops=1
  • Buffers: shared hit=4,997,417 read=38,317
  • I/O Timings: read=496.160
8. 890.198 890.198 ↑ 1.0 10,000,000 1 / 4

Index Only Scan using ix_city_id_end_date_time on stuff.transactions transactions_2 (cost=0.43..68,506.43 rows=10,000,000 width=12) (actual time=0.605..3,560.791 rows=10,000,000 loops=1)

  • Output: transactions_2.city_id, transactions_2.end_date_time
  • Heap Fetches: 0
  • Buffers: shared hit=4,997,417 read=38,317
  • I/O Timings: read=496.160
  • Worker 2: actual time=0.605..3560.791 rows=10,000,000 loops=1
  • Buffers: shared hit=4,997,417 read=38,317
  • I/O Timings: read=496.160
9. 398.498 1,281.175 ↑ 1.0 41 1 / 4

GroupAggregate (cost=0.43..118,506.52 rows=41 width=44) (actual time=79.901..5,124.698 rows=41 loops=1)

  • Output: 'job'::text, transactions_3.job_id, max(transactions_3.end_date_time)
  • Group Key: transactions_3.job_id
  • Buffers: shared hit=4,995,541 read=38,317 written=1
  • I/O Timings: read=406.686 write=0.434
10. 882.677 882.677 ↑ 1.0 10,000,000 1 / 4

Index Only Scan using ix_job_id_end_date_time on stuff.transactions transactions_3 (cost=0.43..68,506.43 rows=10,000,000 width=12) (actual time=0.087..3,530.706 rows=10,000,000 loops=1)

  • Output: transactions_3.job_id, transactions_3.end_date_time
  • Heap Fetches: 0
  • Buffers: shared hit=4,995,541 read=38,317 written=1
  • I/O Timings: read=406.686 write=0.434
Planning time : 0.403 ms
Execution time : 5,215.407 ms