explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mLoW

Settings
# exclusive inclusive rows x rows loops node
1. 2,233.483 297,360.308 ↑ 1,218.6 29,708 1

GroupAggregate (cost=82,920,916.59..84,459,455.91 rows=36,200,925 width=103) (actual time=292,381.783..297,360.308 rows=29,708 loops=1)

  • Group Key: rtr.session_date, alls.site_id, rtr.flaf, flaf.channel, flaf.sub_channel, rtr.device
2. 9,384.475 295,126.825 ↑ 5.5 6,601,393 1

Sort (cost=82,920,916.59..83,011,418.91 rows=36,200,925 width=151) (actual time=292,381.768..295,126.825 rows=6,601,393 loops=1)

  • Sort Key: rtr.session_date, alls.site_id, rtr.flaf, flaf.channel, flaf.sub_channel, rtr.device
  • Sort Method: external merge Disk: 248,504kB
3. 2,317.673 285,742.350 ↑ 5.5 6,601,393 1

Hash Left Join (cost=11.40..75,187,181.26 rows=36,200,925 width=151) (actual time=21,538.915..285,742.350 rows=6,601,393 loops=1)

  • Hash Cond: ((rtr.flaf)::bigint = flaf.pid)
4. 189,111.449 283,424.589 ↑ 5.4 6,599,105 1

Nested Loop Left Join (cost=0.00..74,472,035.02 rows=35,312,559 width=136) (actual time=21,538.817..283,424.589 rows=6,599,105 loops=1)

  • Join Filter: (rtr.site ~~* ('%'::text || alls.domain))
  • Rows Removed by Join Filter: 732,261,923
5. 61,328.820 61,328.820 ↑ 5.4 6,596,864 1

Seq Scan on realtime_session_duration_report rtr (cost=0.00..5,259,415.97 rows=35,312,559 width=164) (actual time=21,538.779..61,328.820 rows=6,596,864 loops=1)

  • Filter: (session_date >= '2020-10-01'::date)
  • Rows Removed by Filter: 99,289,084
6. 32,984.294 32,984.320 ↑ 1.0 112 6,596,864

Materialize (cost=0.00..3.68 rows=112 width=20) (actual time=0.000..0.005 rows=112 loops=6,596,864)

7. 0.026 0.026 ↑ 1.0 112 1

Seq Scan on all_sites alls (cost=0.00..3.12 rows=112 width=20) (actual time=0.008..0.026 rows=112 loops=1)

8. 0.043 0.088 ↑ 1.0 326 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
9. 0.045 0.045 ↑ 1.0 329 1

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

Planning time : 0.181 ms