explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9vlD : Window functions

Settings
# exclusive inclusive rows x rows loops node
1. 1,897.659 51,535.731 ↑ 1.0 9,998,751 1

Unique (cost=5,782,434.77..6,007,434.77 rows=10,000,000 width=48) (actual time=48,269.809..51,535.731 rows=9,998,751 loops=1)

  • Output: person_id, (max(end_date_time) OVER (?)), city_id, (max(end_date_time) OVER (?)), job_id, (max(end_date_time) OVER (?)), action_id, (max(end_date_time) OVER (?))
  • Buffers: shared hit=9,999,873 read=38,285, temp read=273,761 written=273,908
  • I/O Timings: read=422.626
2. 11,747.970 49,638.072 ↑ 1.0 10,000,000 1

Sort (cost=5,782,434.77..5,807,434.77 rows=10,000,000 width=48) (actual time=48,269.807..49,638.072 rows=10,000,000 loops=1)

  • Output: person_id, (max(end_date_time) OVER (?)), city_id, (max(end_date_time) OVER (?)), job_id, (max(end_date_time) OVER (?)), action_id, (max(end_date_time) OVER (?))
  • Sort Key: transactions.person_id, (max(transactions.end_date_time) OVER (?)), transactions.city_id, (max(transactions.end_date_time) OVER (?)), transactions.job_id, (max(transactions.end_date_time) OVER (?)), transactions.action_id, (max(transactions.end_date_time) OVER (?))
  • Sort Method: external merge Disk: 723,504kB
  • Buffers: shared hit=9,999,873 read=38,285, temp read=273,761 written=273,908
  • I/O Timings: read=422.626
3. 3,787.419 37,890.102 ↑ 1.0 10,000,000 1

WindowAgg (cost=4,348,977.93..4,443,977.93 rows=10,000,000 width=48) (actual time=32,844.753..37,890.102 rows=10,000,000 loops=1)

  • Output: person_id, max(end_date_time) OVER (?), city_id, (max(end_date_time) OVER (?)), job_id, (max(end_date_time) OVER (?)), action_id, (max(end_date_time) OVER (?))
  • Buffers: shared hit=9,999,873 read=38,285, temp read=183,323 written=183,419
  • I/O Timings: read=422.626
4. 5,528.058 34,102.683 ↑ 1.0 10,000,000 1

Sort (cost=4,348,977.93..4,373,977.93 rows=10,000,000 width=48) (actual time=32,842.599..34,102.683 rows=10,000,000 loops=1)

  • Output: person_id, city_id, job_id, action_id, end_date_time, (max(end_date_time) OVER (?)), (max(end_date_time) OVER (?)), (max(end_date_time) OVER (?))
  • Sort Key: transactions.person_id
  • Sort Method: external merge Disk: 567,008kB
  • Buffers: shared hit=9,999,873 read=38,285, temp read=183,323 written=183,419
  • I/O Timings: read=422.626
5. 3,746.900 28,574.625 ↑ 1.0 10,000,000 1

WindowAgg (cost=2,915,521.10..3,010,521.10 rows=10,000,000 width=48) (actual time=23,631.196..28,574.625 rows=10,000,000 loops=1)

  • Output: person_id, city_id, job_id, action_id, end_date_time, (max(end_date_time) OVER (?)), (max(end_date_time) OVER (?)), max(end_date_time) OVER (?)
  • Buffers: shared hit=9,999,873 read=38,285, temp read=112,447 written=112,511
  • I/O Timings: read=422.626
6. 5,374.099 24,827.725 ↑ 1.0 10,000,000 1

Sort (cost=2,915,521.10..2,940,521.10 rows=10,000,000 width=40) (actual time=23,620.625..24,827.725 rows=10,000,000 loops=1)

  • Output: person_id, city_id, job_id, action_id, end_date_time, (max(end_date_time) OVER (?)), (max(end_date_time) OVER (?))
  • Sort Key: transactions.city_id
  • Sort Method: external merge Disk: 488,784kB
  • Buffers: shared hit=9,999,873 read=38,285, temp read=112,447 written=112,511
  • I/O Timings: read=422.626
7. 3,685.502 19,453.626 ↑ 1.0 10,000,000 1

WindowAgg (cost=1,501,596.27..1,596,596.27 rows=10,000,000 width=40) (actual time=14,680.036..19,453.626 rows=10,000,000 loops=1)

  • Output: person_id, city_id, job_id, action_id, end_date_time, (max(end_date_time) OVER (?)), max(end_date_time) OVER (?)
  • Buffers: shared hit=9,999,873 read=38,285, temp read=51,349 written=51,381
  • I/O Timings: read=422.626
8. 4,779.773 15,768.124 ↑ 1.0 10,000,000 1

Sort (cost=1,501,596.27..1,526,596.27 rows=10,000,000 width=32) (actual time=14,641.964..15,768.124 rows=10,000,000 loops=1)

  • Output: person_id, city_id, job_id, action_id, end_date_time, (max(end_date_time) OVER (?))
  • Sort Key: transactions.job_id
  • Sort Method: external merge Disk: 410,792kB
  • Buffers: shared hit=9,999,873 read=38,285, temp read=51,349 written=51,381
  • I/O Timings: read=422.626
9. 3,874.321 10,988.351 ↑ 1.0 10,000,000 1

WindowAgg (cost=0.43..202,201.43 rows=10,000,000 width=32) (actual time=4.483..10,988.351 rows=10,000,000 loops=1)

  • Output: person_id, city_id, job_id, action_id, end_date_time, max(end_date_time) OVER (?)
  • Buffers: shared hit=9,999,873 read=38,285
  • I/O Timings: read=422.626
10. 7,114.030 7,114.030 ↑ 1.0 10,000,000 1

Index Scan using ix_action_id_end_date_time on stuff.transactions (cost=0.43..132,201.43 rows=10,000,000 width=24) (actual time=0.094..7,114.030 rows=10,000,000 loops=1)

  • Output: person_id, city_id, job_id, action_id, end_date_time
  • Buffers: shared hit=9,999,873 read=38,285
  • I/O Timings: read=422.626
Planning time : 0.483 ms
Execution time : 52,027.377 ms