explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DE12

Settings
# exclusive inclusive rows x rows loops node
1. 0.082 1,221.225 ↓ 132.7 1,194 1

Limit (cost=24,335.71..24,335.73 rows=9 width=295) (actual time=1,221.088..1,221.225 rows=1,194 loops=1)

2. 2.381 1,221.143 ↓ 132.7 1,194 1

Sort (cost=24,335.71..24,335.73 rows=9 width=295) (actual time=1,221.087..1,221.143 rows=1,194 loops=1)

  • Sort Key: temp.name
  • Sort Method: quicksort Memory: 232kB
3. 0.192 1,218.762 ↓ 132.7 1,194 1

Subquery Scan on temp (cost=24,334.31..24,335.57 rows=9 width=295) (actual time=1,205.831..1,218.762 rows=1,194 loops=1)

4. 1,218.570 1,218.570 ↓ 132.7 1,194 1

CTE Scan on learnerlatestsession lls (cost=24,334.31..24,335.45 rows=9 width=307) (actual time=1,205.829..1,218.570 rows=1,194 loops=1)

5.          

CTE learnersongame

6. 0.123 542.920 ↓ 1,255.0 1,255 1

Subquery Scan on t (cost=17,118.77..17,120.46 rows=1 width=58) (actual time=541.775..542.920 rows=1,255 loops=1)

  • Filter: (t.temp_rank = 1)
7. 0.870 542.797 ↓ 27.9 1,255 1

WindowAgg (cost=17,118.77..17,119.90 rows=45 width=58) (actual time=541.775..542.797 rows=1,255 loops=1)

8. 0.732 541.927 ↓ 27.9 1,255 1

Sort (cost=17,118.77..17,118.88 rows=45 width=61) (actual time=541.766..541.927 rows=1,255 loops=1)

  • Sort Key: usg.""userId"
  • Sort Method: quicksort Memory: 225kB
9. 0.377 541.195 ↓ 27.9 1,255 1

Nested Loop (cost=0.99..17,117.53 rows=45 width=61) (actual time=0.029..541.195 rows=1,255 loops=1)

  • Join Filter: CASE WHEN (e.state <> 'archived'::text) THEN (usg.state <> 'deactivated'::text) ELSE true END
10. 0.007 0.007 ↑ 1.0 1 1

Index Scan using gameid_entities_normal on entities e (cost=0.42..8.44 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (""gameId"" = '1050403501267716928'::bigint)
11. 540.811 540.811 ↓ 13.9 1,255 1

Index Scan using gameid_user_series_game_normal on user_series_game usg (cost=0.57..17,107.74 rows=90 width=45) (actual time=0.021..540.811 rows=1,255 loops=1)

  • Index Cond: (""gameId"" = '1050403501267716928'::bigint)
  • Filter: ((""companyId"" = '909666665757230431'::bigint) AND (date_part('epoch'::text, ""invitedOn"") > ceil(date_part('epoch'::text, timezone('utc'::text, (now() - '6 mons'::interval))))))
  • Rows Removed by Filter: 19,944
12.          

CTE activelearnerinfo

13. 0.000 548.348 ↓ 1,194.0 1,194 1

Nested Loop (cost=0.43..8.48 rows=1 width=131) (actual time=541.804..548.348 rows=1,194 loops=1)

14. 543.473 543.473 ↓ 1,255.0 1,255 1

CTE Scan on learnersongame usg_1 (cost=0.00..0.02 rows=1 width=66) (actual time=541.777..543.473 rows=1,255 loops=1)

  • Filter: (""companyId"" = '909666665757230431'::bigint)
15. 5.020 5.020 ↑ 1.0 1 1,255

Index Scan using primary_index__learners on learners l (cost=0.43..8.45 rows=1 width=57) (actual time=0.004..0.004 rows=1 loops=1,255)

  • Index Cond: ((""companyId"" = '909666665757230431'::bigint) AND (""userId"" = usg_1.""userId""))
  • Filter: (state <> 'deactivated'::text)
  • Rows Removed by Filter: 0
16.          

CTE totaldraftcount

17. 3.533 104.232 ↓ 25.4 7,687 1

GroupAggregate (cost=0.57..1,093.41 rows=303 width=20) (actual time=0.469..104.232 rows=7,687 loops=1)

  • Group Key: ge.""userId"
18. 100.699 100.699 ↓ 26.0 7,893 1

Index Scan using primary_index_ge_summary_unique on ge_summary ge (cost=0.57..1,088.11 rows=303 width=16) (actual time=0.010..100.699 rows=7,893 loops=1)

  • Index Cond: ((""companyId"" = '909666665757230431'::bigint) AND (""gameId"" = '1050403501267716928'::bigint))
19.          

CTE learnersessionrecords

20. 1.934 117.858 ↓ 7,687.0 7,687 1

Hash Join (cost=11.60..59.30 rows=1 width=53) (actual time=110.923..117.858 rows=7,687 loops=1)

  • Hash Cond: ((ge_a.""userId"" = ge_b.""userId"") AND (ge_a.""reattemptNo"" = ge_b.""latestReattemptNo""))
21. 2.148 8.127 ↓ 111.2 7,893 1

Nested Loop (cost=0.99..43.00 rows=71 width=45) (actual time=3.094..8.127 rows=7,893 loops=1)

22. 0.019 0.019 ↓ 4.0 4 1

Index Scan using primary_index__entity_settings on entity_settings (cost=0.43..8.45 rows=1 width=21) (actual time=0.014..0.019 rows=4 loops=1)

  • Index Cond: ((""companyId"" = '909666665757230431'::bigint) AND (""gameId"" = '1050403501267716928'::bigint))
23. 5.960 5.960 ↓ 394.6 1,973 4

Index Scan using primary_index__ge_summary on ge_summary ge_a (cost=0.57..34.50 rows=5 width=60) (actual time=0.825..1.490 rows=1,973 loops=4)

  • Index Cond: ((""companyId"" = '909666665757230431'::bigint) AND (""gameId"" = '1050403501267716928'::bigint) AND (version = entity_settings.version))
24. 1.305 107.797 ↓ 25.4 7,687 1

Hash (cost=6.06..6.06 rows=303 width=20) (actual time=107.797..107.797 rows=7,687 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 453kB
25. 106.492 106.492 ↓ 25.4 7,687 1

CTE Scan on totaldraftcount ge_b (cost=0.00..6.06 rows=303 width=20) (actual time=0.472..106.492 rows=7,687 loops=1)

26.          

CTE totalassociatedreviewers

27. 12.872 537.745 ↓ 12.3 21,137 1

HashAggregate (cost=5,994.28..6,011.46 rows=1,718 width=16) (actual time=533.550..537.745 rows=21,137 loops=1)

  • Group Key: rlr.""userId"
28. 519.592 524.873 ↓ 15.2 26,196 1

Bitmap Heap Scan on coaching_analytics_rlr rlr (cost=62.26..5,981.33 rows=1,727 width=19) (actual time=5.983..524.873 rows=26,196 loops=1)

  • Recheck Cond: ((""companyId"" = '909666665757230431'::bigint) AND (""gameId"" = '1050403501267716928'::bigint))
  • Heap Blocks: exact=1,582
29. 5.281 5.281 ↓ 15.2 26,196 1

Bitmap Index Scan on seconday_index_coaching_analytics_rlr (cost=0.00..61.82 rows=1,727 width=0) (actual time=5.281..5.281 rows=26,196 loops=1)

  • Index Cond: ((""companyId"" = '909666665757230431'::bigint) AND (""gameId"" = '1050403501267716928'::bigint))
30.          

CTE learnerreviewers

31. 2.356 1,093.559 ↓ 132.7 1,194 1

Hash Right Join (cost=0.03..40.93 rows=9 width=194) (actual time=1,082.800..1,093.559 rows=1,194 loops=1)

  • Hash Cond: (tar.""userId"" = usg_2.""userId"")
32. 541.987 541.987 ↓ 12.3 21,137 1

CTE Scan on totalassociatedreviewers tar (cost=0.00..34.36 rows=1,718 width=16) (actual time=533.552..541.987 rows=21,137 loops=1)

33. 0.281 549.216 ↓ 1,194.0 1,194 1

Hash (cost=0.02..0.02 rows=1 width=186) (actual time=549.215..549.216 rows=1,194 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 154kB
34. 548.935 548.935 ↓ 1,194.0 1,194 1

CTE Scan on activelearnerinfo usg_2 (cost=0.00..0.02 rows=1 width=186) (actual time=541.805..548.935 rows=1,194 loops=1)

35.          

CTE learnerlatestsession

36. 0.400 1,217.507 ↓ 132.7 1,194 1

Hash Left Join (cost=0.03..0.28 rows=9 width=216) (actual time=1,205.822..1,217.507 rows=1,194 loops=1)

  • Hash Cond: (lr.""userId"" = lsi.""userId"")
37. 1,094.100 1,094.100 ↓ 132.7 1,194 1

CTE Scan on learnerreviewers lr (cost=0.00..0.18 rows=9 width=154) (actual time=1,082.802..1,094.100 rows=1,194 loops=1)

38. 1.673 123.007 ↓ 7,687.0 7,687 1

Hash (cost=0.02..0.02 rows=1 width=70) (actual time=123.007..123.007 rows=7,687 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 725kB
39. 121.334 121.334 ↓ 7,687.0 7,687 1

CTE Scan on learnersessionrecords lsi (cost=0.00..0.02 rows=1 width=70) (actual time=110.926..121.334 rows=7,687 loops=1)