explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2pKF

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 74,123.710 ↓ 0.0 0 1

Subquery Scan on model_input_agg (cost=2,353,737.95..2,364,199.63 rows=130,771 width=232) (actual time=74,123.710..74,123.710 rows=0 loops=1)

2.          

CTE sessions

3. 61.791 74,122.325 ↓ 0.0 0 1

Finalize GroupAggregate (cost=2,210,251.13..2,301,123.79 rows=1,753 width=48) (actual time=74,122.325..74,122.325 rows=0 loops=1)

  • Group Key: realtime_revenue_active.session_id
  • Filter: ((max(realtime_revenue_active.created_time) >= '2020-10-09 09:00:00'::timestamp without time zone) AND (max(realtime_revenue_active.created_time) <= '2020-10-09 10:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 101,091
4. 258.027 74,060.534 ↑ 1.9 189,366 1

Gather Merge (cost=2,210,251.13..2,291,480.37 rows=350,670 width=48) (actual time=66,390.398..74,060.534 rows=189,366 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 830.787 73,802.507 ↑ 3.7 94,683 2 / 2

Partial GroupAggregate (cost=2,209,251.12..2,251,029.98 rows=350,670 width=48) (actual time=66,323.473..73,802.507 rows=94,683 loops=2)

  • Group Key: realtime_revenue_active.session_id
6. 41,099.939 72,971.720 ↑ 1.3 2,859,828 2 / 2

Sort (cost=2,209,251.12..2,218,819.16 rows=3,827,216 width=40) (actual time=66,320.632..72,971.720 rows=2,859,828 loops=2)

  • Sort Key: realtime_revenue_active.session_id
  • Sort Method: external merge Disk: 163,304kB
  • Worker 0: Sort Method: external merge Disk: 161,336kB
7. 31,871.781 31,871.781 ↑ 1.3 2,859,828 2 / 2

Parallel Seq Scan on realtime_revenue_active (cost=0.00..1,668,191.83 rows=3,827,216 width=40) (actual time=0.343..31,871.781 rows=2,859,828 loops=2)

  • Filter: ((created_time >= '2020-10-08 09:00:00'::timestamp without time zone) AND (as_id ~ '^6da(p|q)2\d+'::text))
  • Rows Removed by Filter: 10,326,696
8. 0.871 74,123.706 ↓ 0.0 0 1

GroupAggregate (cost=52,614.15..59,152.70 rows=130,771 width=160) (actual time=74,123.705..74,123.706 rows=0 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))
9. 0.497 74,122.835 ↓ 0.0 0 1

Sort (cost=52,614.15..52,941.08 rows=130,771 width=149) (actual time=74,122.835..74,122.835 rows=0 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: quicksort Memory: 25kB
10. 0.001 74,122.338 ↓ 0.0 0 1

Nested Loop (cost=0.56..35,740.29 rows=130,771 width=149) (actual time=74,122.338..74,122.338 rows=0 loops=1)

11. 74,122.337 74,122.337 ↓ 0.0 0 1

CTE Scan on sessions (cost=0.00..35.06 rows=1,753 width=48) (actual time=74,122.337..74,122.337 rows=0 loops=1)

12. 0.000 0.000 ↓ 0.0 0

Index Scan using realtime_revenue_active_session_id_idx on realtime_revenue_active rra (cost=0.56..18.87 rows=75 width=89) (never executed)

  • Index Cond: (session_id = sessions.session_id)
Planning time : 13.297 ms
Execution time : 74,221.826 ms