explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZTijQ

Settings
# exclusive inclusive rows x rows loops node
1. 403.634 187,115.498 ↑ 1.8 82,135 1

Subquery Scan on model_input_agg (cost=2,506,944.07..2,518,615.83 rows=145,897 width=232) (actual time=185,484.599..187,115.498 rows=82,135 loops=1)

2. 375.937 186,711.864 ↑ 1.8 82,135 1

GroupAggregate (cost=2,506,944.07..2,514,238.92 rows=145,897 width=160) (actual time=185,482.134..186,711.864 rows=82,135 loops=1)

  • Group Key: rra.session_id, (rtrim(rra.as_id, '"'::text)), (CASE WHEN (rra.advertiser_id = '-1'::text) THEN '4759680453'::text ELSE rra.advertiser_id END), (split_part(btrim(rra.ad_unit_id, '"'::text), '/'::text, 3))
3. 2,436.369 186,335.927 ↓ 2.6 380,689 1

Sort (cost=2,506,944.07..2,507,308.81 rows=145,897 width=149) (actual time=185,481.035..186,335.927 rows=380,689 loops=1)

  • Sort Key: rra.session_id, (rtrim(rra.as_id, '"'::text)), (CASE WHEN (rra.advertiser_id = '-1'::text) THEN '4759680453'::text ELSE rra.advertiser_id END), (split_part(btrim(rra.ad_unit_id, '"'::text), '/'::text, 3))
  • Sort Method: external merge Disk: 43,472kB
4. 894.270 183,899.558 ↓ 2.6 380,689 1

Nested Loop (cost=2,354,469.03..2,488,003.30 rows=145,897 width=149) (actual time=88,572.877..183,899.558 rows=380,689 loops=1)

5. 200.935 98,528.273 ↓ 3.1 6,541 1

Finalize GroupAggregate (cost=2,354,468.47..2,453,144.40 rows=2,078 width=48) (actual time=88,541.073..98,528.273 rows=6,541 loops=1)

  • Group Key: realtime_revenue_active.session_id
  • Filter: ((max(realtime_revenue_active.created_time) >= '2020-10-12 09:00:00'::timestamp without time zone) AND (max(realtime_revenue_active.created_time) <= '2020-10-12 10:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 97,743
6. 509.074 98,327.338 ↑ 2.2 189,819 1

Gather Merge (cost=2,354,468.47..2,441,715.37 rows=415,601 width=48) (actual time=88,530.298..98,327.338 rows=189,819 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
7. 1,073.573 97,818.264 ↑ 4.4 94,910 2 / 2

Partial GroupAggregate (cost=2,353,468.46..2,393,960.25 rows=415,601 width=48) (actual time=88,349.298..97,818.264 rows=94,910 loops=2)

  • Group Key: realtime_revenue_active.session_id
8. 54,842.323 96,744.691 ↑ 1.2 2,943,978 2 / 2

Sort (cost=2,353,468.46..2,362,552.40 rows=3,633,578 width=40) (actual time=88,346.541..96,744.691 rows=2,943,978 loops=2)

  • Sort Key: realtime_revenue_active.session_id
  • Sort Method: external merge Disk: 167,576kB
  • Worker 0: Sort Method: external merge Disk: 166,624kB
9. 41,902.368 41,902.368 ↑ 1.2 2,943,978 2 / 2

Parallel Seq Scan on realtime_revenue_active (cost=0.00..1,841,145.57 rows=3,633,578 width=40) (actual time=0.115..41,902.368 rows=2,943,978 loops=2)

  • Filter: ((created_time >= '2020-10-11 09:00:00'::timestamp without time zone) AND (as_id ~ '^6da(p|q)2\d+'::text))
  • Rows Removed by Filter: 11,693,476
10. 84,477.015 84,477.015 ↑ 1.2 58 6,541

Index Scan using realtime_revenue_active_session_id_idx on realtime_revenue_active rra (cost=0.56..15.36 rows=70 width=89) (actual time=0.902..12.915 rows=58 loops=6,541)

  • Index Cond: (session_id = realtime_revenue_active.session_id)
Planning time : 48.693 ms
Execution time : 187,154.050 ms