explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AQF9

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 40,240.780 ↑ 5.0 3 1

Limit (cost=523,755.78..525,959.78 rows=15 width=174) (actual time=7,275.800..40,240.780 rows=3 loops=1)

2. 0.010 40,240.780 ↑ 2,712,528.0 3 1

Nested Loop (cost=523,755.78..1,196,209,373.24 rows=8,137,584 width=174) (actual time=7,275.800..40,240.780 rows=3 loops=1)

3. 0.015 40,240.740 ↑ 2,712,528.0 3 1

Nested Loop (cost=523,755.35..993,867,865.32 rows=8,137,584 width=90) (actual time=7,275.771..40,240.740 rows=3 loops=1)

  • Join Filter: (r.game_id = g.id)
  • Rows Removed by Join Filter: 216
4. 0.003 40,240.698 ↑ 2,712,528.0 3 1

Nested Loop (cost=523,755.35..984,957,208.93 rows=8,137,584 width=86) (actual time=7,275.755..40,240.698 rows=3 loops=1)

5. 0.004 40,240.674 ↑ 2,712,528.0 3 1

Nested Loop (cost=523,754.92..920,090,140.20 rows=8,137,584 width=78) (actual time=7,275.735..40,240.674 rows=3 loops=1)

6. 6,941.821 40,240.634 ↑ 2,722,240.7 3 1

Nested Loop Semi Join (cost=523,754.35..718,722,132.91 rows=8,166,722 width=70) (actual time=7,275.699..40,240.634 rows=3 loops=1)

  • Join Filter: (r.user_id = subordinates.id)
  • Rows Removed by Join Filter: 64,775,134
7. 33,298.813 33,298.813 ↑ 1.0 16,193,785 1

Index Scan Backward using game_scale_round_common_id_key on game_scale_round r (cost=0.57..25,320,965.49 rows=16,333,443 width=62) (actual time=344.601..33,298.813 rows=16,193,785 loops=1)

  • Filter: ((timestampclose IS NOT NULL) AND (timestampopen >= '2020-06-03 22:48:57'::timestamp without time zone) AND (timestampopen <= '2020-07-04 22:48:57'::timestamp without time zone))
  • Rows Removed by Filter: 87,735,116
8. 0.000 0.000 ↑ 707.0 4 16,193,785

Materialize (cost=523,753.78..536,522.43 rows=2,828 width=8) (actual time=0.000..0.000 rows=4 loops=16,193,785)

9. 412.492 412.492 ↑ 707.0 4 1

CTE Scan on subordinates (cost=523,753.78..536,480.01 rows=2,828 width=8) (actual time=11.400..412.492 rows=4 loops=1)

  • Filter: ((usertype)::text = 'USER'::text)
  • Rows Removed by Filter: 1,220
10.          

CTE subordinates

11. 0.159 412.236 ↑ 462.1 1,224 1

Recursive Union (cost=0.43..523,753.78 rows=565,610 width=21) (actual time=0.028..412.236 rows=1,224 loops=1)

12. 0.099 0.099 ↑ 1.1 217 1

Index Scan using user_user_operator_index on user_user (cost=0.43..613.78 rows=240 width=21) (actual time=0.027..0.099 rows=217 loops=1)

  • Index Cond: (operator_id = 1,159,555)
13. 202.412 411.978 ↑ 112.2 504 2

Hash Join (cost=54.30..51,182.78 rows=56,537 width=21) (actual time=110.095..205.989 rows=504 loops=2)

  • Hash Cond: (e.operator_id = subordinates_1.id)
14. 209.334 209.334 ↓ 1.0 1,280,340 2

Seq Scan on user_user e (cost=0.00..44,414.74 rows=1,229,674 width=21) (actual time=0.005..104.667 rows=1,280,340 loops=2)

15. 0.098 0.232 ↓ 25.3 608 2

Hash (cost=54.00..54.00 rows=24 width=8) (actual time=0.116..0.116 rows=608 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 48kB
16. 0.134 0.134 ↓ 25.3 608 2

WorkTable Scan on subordinates subordinates_1 (cost=0.00..54.00 rows=24 width=8) (actual time=0.003..0.067 rows=608 loops=2)

  • Filter: ((usertype)::text = ANY ('{GROUP,OPERATOR}'::text[]))
  • Rows Removed by Filter: 4
17. 0.036 0.036 ↑ 51.0 1 3

Index Scan using game_scale_action_round_index on game_scale_action a (cost=0.57..24.15 rows=51 width=16) (actual time=0.012..0.012 rows=1 loops=3)

  • Index Cond: (round_id = r.id)
  • Filter: ((actdescr)::text = 'BET'::text)
  • Rows Removed by Filter: 2
18. 0.021 0.021 ↑ 1.0 1 3

Index Scan using user_user_pkey on user_user u (cost=0.43..7.96 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=3)

  • Index Cond: (id = r.user_id)
19. 0.018 0.027 ↑ 1.0 73 3

Materialize (cost=0.00..2.09 rows=73 width=20) (actual time=0.004..0.009 rows=73 loops=3)

20. 0.009 0.009 ↑ 1.0 73 1

Seq Scan on fugaso_game g (cost=0.00..1.73 rows=73 width=20) (actual time=0.007..0.009 rows=73 loops=1)

21. 0.012 0.012 ↑ 1.0 1 3

Index Scan using user_user_pkey on user_user o (cost=0.43..7.96 rows=1 width=44) (actual time=0.003..0.004 rows=1 loops=3)

  • Index Cond: (id = u.operator_id)
22.          

SubPlan (for Nested Loop)

23. 0.009 0.018 ↑ 1.0 1 3

Aggregate (cost=16.87..16.89 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=3)

24. 0.009 0.009 ↑ 51.0 1 3

Index Scan using game_scale_action_round_index on game_scale_action w (cost=0.57..16.74 rows=51 width=8) (actual time=0.003..0.003 rows=1 loops=3)

  • Index Cond: (round_id = r.id)
  • Filter: ((actdescr)::text = ANY ('{COLLECT,FREE_COLLECT,HALF_COLLECT,CLOSE}'::text[]))
  • Rows Removed by Filter: 2
Planning time : 1.483 ms
Execution time : 40,240.927 ms