explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9q2co

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 142,650.812 ↓ 0.0 0 1

Limit (cost=523,755.78..701,409.49 rows=15 width=202) (actual time=142,650.812..142,650.812 rows=0 loops=1)

2. 0.000 142,650.810 ↓ 0.0 0 1

Nested Loop Left Join (cost=523,755.78..1,993,585,189.51 rows=168,282 width=202) (actual time=142,650.810..142,650.810 rows=0 loops=1)

3. 0.001 142,650.810 ↓ 0.0 0 1

Nested Loop Left Join (cost=523,755.35..1,989,401,407.91 rows=168,282 width=118) (actual time=142,650.810..142,650.810 rows=0 loops=1)

4. 0.000 142,650.809 ↓ 0.0 0 1

Nested Loop Left Join (cost=523,754.78..1,985,230,731.23 rows=168,282 width=110) (actual time=142,650.809..142,650.809 rows=0 loops=1)

  • Join Filter: (r.game_id = g.id)
5. 0.000 142,650.809 ↓ 0.0 0 1

Nested Loop Left Join (cost=523,754.78..1,985,046,460.53 rows=168,282 width=106) (actual time=142,650.809..142,650.809 rows=0 loops=1)

6. 55,555.444 142,650.809 ↓ 0.0 0 1

Nested Loop Semi Join (cost=523,754.35..1,983,705,040.22 rows=168,282 width=62) (actual time=142,650.809..142,650.809 rows=0 loops=1)

  • Join Filter: (r.user_id = subordinates.id)
  • Rows Removed by Join Filter: 630,198,432
7. 56,718.153 56,718.153 ↓ 1.5 514,868 1

Index Scan Backward using game_scale_round_common_id_key on game_scale_round r (cost=0.57..25,244,789.47 rows=336,564 width=62) (actual time=399.761..56,718.153 rows=514,868 loops=1)

  • Filter: ((timestampclose IS NOT NULL) AND (timestampopen >= '2020-07-03 22:48:57'::timestamp without time zone) AND (timestampopen <= '2020-07-04 22:48:57'::timestamp without time zone))
  • Rows Removed by Filter: 103,199,245
8. 29,791.884 30,377.212 ↑ 462.1 1,224 514,868

Materialize (cost=523,753.78..545,760.13 rows=565,610 width=8) (actual time=0.000..0.059 rows=1,224 loops=514,868)

9. 585.328 585.328 ↑ 462.1 1,224 1

CTE Scan on subordinates (cost=523,753.78..535,065.98 rows=565,610 width=8) (actual time=0.028..585.328 rows=1,224 loops=1)

10.          

CTE subordinates

11. 0.221 585.012 ↑ 462.1 1,224 1

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

12. 0.109 0.109 ↑ 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.025..0.109 rows=217 loops=1)

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

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

  • Hash Cond: (e.operator_id = subordinates_1.id)
14. 287.830 287.830 ↓ 1.0 1,279,399 2

Seq Scan on user_user e (cost=0.00..44,414.74 rows=1,229,674 width=21) (actual time=0.006..143.915 rows=1,279,399 loops=2)

15. 0.112 0.264 ↓ 25.3 608 2

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

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

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

  • Filter: ((usertype)::text = ANY ('{GROUP,OPERATOR}'::text[]))
  • Rows Removed by Filter: 4
17. 0.000 0.000 ↓ 0.0 0

Index Scan using user_user_pkey on user_user u (cost=0.43..7.96 rows=1 width=52) (never executed)

  • Index Cond: (r.user_id = id)
18. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..2.09 rows=73 width=20) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Seq Scan on fugaso_game g (cost=0.00..1.73 rows=73 width=20) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Index Scan using game_scale_action_round_index on game_scale_action a (cost=0.57..24.28 rows=50 width=16) (never executed)

  • Index Cond: (round_id = r.id)
  • Filter: ((actdescr)::text = 'BET'::text)
21. 0.000 0.000 ↓ 0.0 0

Index Scan using user_user_pkey on user_user o (cost=0.43..7.96 rows=1 width=44) (never executed)

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

SubPlan (for Nested Loop Left Join)

23. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=16.87..16.88 rows=1 width=32) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Index Scan using game_scale_action_round_index on game_scale_action w (cost=0.57..16.74 rows=50 width=8) (never executed)

  • Index Cond: (round_id = r.id)
  • Filter: ((actdescr)::text = ANY ('{COLLECT,FREE_COLLECT,HALF_COLLECT,CLOSE}'::text[]))
Planning time : 4.065 ms
Execution time : 142,651.149 ms