explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UhX2

Settings
# exclusive inclusive rows x rows loops node
1. 24.423 70,933.184 ↑ 1,059.7 29,708 1

GroupAggregate (cost=125,490,098.70..126,828,093.68 rows=31,482,235 width=159) (actual time=70,905.793..70,933.184 rows=29,708 loops=1)

  • Group Key: realtime_session_duration_report.session_date, alls.site_id, realtime_session_duration_report.flaf, flaf.channel, flaf.sub_channel, realtime_session_duration_report.device
2. 164.777 70,908.761 ↑ 520.7 60,458 1

Sort (cost=125,490,098.70..125,568,804.28 rows=31,482,235 width=103) (actual time=70,905.780..70,908.761 rows=60,458 loops=1)

  • Sort Key: realtime_session_duration_report.session_date, alls.site_id, realtime_session_duration_report.flaf, flaf.channel, flaf.sub_channel, realtime_session_duration_report.device
  • Sort Method: quicksort Memory: 7,398kB
3. 34.182 70,743.984 ↑ 520.7 60,458 1

Hash Left Join (cost=9,235,883.72..119,552,464.70 rows=31,482,235 width=103) (actual time=63,909.130..70,743.984 rows=60,458 loops=1)

  • Hash Cond: ((realtime_session_duration_report.flaf)::bigint = flaf.pid)
4. 2,391.911 70,709.116 ↑ 517.8 59,312 1

Nested Loop Left Join (cost=9,235,872.32..118,930,534.30 rows=30,709,665 width=88) (actual time=63,908.429..70,709.116 rows=59,312 loops=1)

  • Join Filter: (realtime_session_duration_report.site ~~* ('%'::text || alls.domain))
  • Rows Removed by Join Filter: 6,584,593
5. 45.081 67,902.441 ↑ 175.7 59,252 1

Finalize GroupAggregate (cost=9,235,872.32..11,342,588.16 rows=10,410,056 width=116) (actual time=63,908.292..67,902.441 rows=59,252 loops=1)

  • Group Key: realtime_session_duration_report.session_date, realtime_session_duration_report.site, realtime_session_duration_report.flaf, realtime_session_duration_report.device
6. 0.000 67,857.360 ↑ 126.4 82,379 1

Gather Merge (cost=9,235,872.32..11,082,336.76 rows=10,410,056 width=116) (actual time=63,908.275..67,857.360 rows=82,379 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
7. 1,841.860 67,906.288 ↑ 252.7 41,190 2 / 2

Partial GroupAggregate (cost=9,234,872.31..9,910,205.45 rows=10,410,056 width=116) (actual time=63,902.600..67,906.288 rows=41,190 loops=2)

  • Group Key: realtime_session_duration_report.session_date, realtime_session_duration_report.site, realtime_session_duration_report.flaf, realtime_session_duration_report.device
8. 6,124.380 66,064.428 ↑ 6.3 3,298,432 2 / 2

Sort (cost=9,234,872.31..9,286,802.54 rows=20,772,094 width=164) (actual time=63,902.586..66,064.428 rows=3,298,432 loops=2)

  • Sort Key: realtime_session_duration_report.session_date, realtime_session_duration_report.site, realtime_session_duration_report.flaf, realtime_session_duration_report.device
  • Sort Method: external merge Disk: 157,632kB
  • Worker 0: Sort Method: external merge Disk: 161,616kB
9. 59,940.048 59,940.048 ↑ 6.3 3,298,432 2 / 2

Parallel Seq Scan on realtime_session_duration_report (cost=0.00..4,714,148.51 rows=20,772,094 width=164) (actual time=20,496.399..59,940.048 rows=3,298,432 loops=2)

  • Filter: (session_date >= '2020-10-01'::date)
  • Rows Removed by Filter: 49,644,542
10. 414.724 414.764 ↑ 5.3 112 59,252

Materialize (cost=0.00..18.85 rows=590 width=20) (actual time=0.000..0.007 rows=112 loops=59,252)

11. 0.040 0.040 ↑ 5.3 112 1

Seq Scan on all_sites alls (cost=0.00..15.90 rows=590 width=20) (actual time=0.012..0.040 rows=112 loops=1)

12. 0.082 0.686 ↑ 1.0 326 1

Hash (cost=7.29..7.29 rows=329 width=23) (actual time=0.686..0.686 rows=326 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
13. 0.604 0.604 ↑ 1.0 329 1

Seq Scan on flaf (cost=0.00..7.29 rows=329 width=23) (actual time=0.401..0.604 rows=329 loops=1)

Planning time : 3.509 ms