explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1q9W

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 9,179.099 ↓ 0.0 0 1

Nested Loop (cost=553,754.18..1,271,426.51 rows=438 width=114) (actual time=9,179.093..9,179.099 rows=0 loops=1)

2. 0.008 9,179.098 ↓ 0.0 0 1

Hash Join (cost=553,753.61..1,267,981.43 rows=438 width=107) (actual time=9,179.093..9,179.098 rows=0 loops=1)

  • Hash Cond: (x5.internal_draw_id = x2.internal_draw_id)
3. 0.034 9,178.093 ↓ 0.0 0 1

Hash Join (cost=553,646.68..1,267,873.34 rows=438 width=97) (actual time=9,178.089..9,178.093 rows=0 loops=1)

  • Hash Cond: (x3.prize_item_id = x5.prize_item_id)
4. 0.001 9,166.570 ↓ 0.0 0 1

Nested Loop (cost=552,674.76..1,266,900.27 rows=438 width=59) (actual time=9,166.567..9,166.570 rows=0 loops=1)

5. 121.920 9,166.569 ↓ 0.0 0 1

Nested Loop (cost=552,674.19..1,263,472.29 rows=438 width=51) (actual time=9,166.566..9,166.569 rows=0 loops=1)

6. 298.446 5,714.119 ↓ 12.4 475,790 1

GroupAggregate (cost=552,673.62..553,345.88 rows=38,415 width=12) (actual time=5,087.317..5,714.119 rows=475,790 loops=1)

  • Group Key: x7.award_id
7. 1,030.100 5,415.673 ↓ 49.5 1,900,617 1

Sort (cost=552,673.62..552,769.65 rows=38,415 width=12) (actual time=5,087.307..5,415.673 rows=1,900,617 loops=1)

  • Sort Key: x7.award_id
  • Sort Method: external merge Disk: 40,960kB
8. 317.181 4,385.573 ↓ 49.5 1,900,617 1

Nested Loop (cost=1.71..549,748.43 rows=38,415 width=12) (actual time=0.135..4,385.573 rows=1,900,617 loops=1)

9. 65.910 1,213.652 ↓ 19.3 475,790 1

Nested Loop (cost=1.14..423,425.34 rows=24,674 width=8) (actual time=0.093..1,213.652 rows=475,790 loops=1)

10. 0.009 0.089 ↓ 2.6 13 1

Nested Loop (cost=0.57..32.65 rows=5 width=4) (actual time=0.055..0.089 rows=13 loops=1)

11. 0.039 0.039 ↑ 1.0 1 1

Index Scan using draw_code_mapping_code_uk on draw_code_mapping x12 (cost=0.28..8.30 rows=1 width=4) (actual time=0.038..0.039 rows=1 loops=1)

  • Index Cond: ((draw_code)::text = 'P_ET_P202013'::text)
12. 0.041 0.041 ↓ 2.2 13 1

Index Scan using prize_definition_draw_award_algorithm_idx on prize_definition x11 (cost=0.29..24.29 rows=6 width=8) (actual time=0.014..0.041 rows=13 loops=1)

  • Index Cond: (internal_draw_id = x12.internal_draw_id)
  • Filter: ((payout_prize_type_id = 2) AND ((delayed_payout_date IS NULL) OR (delayed_payout_date <= now())))
  • Rows Removed by Filter: 2
13. 1,147.653 1,147.653 ↓ 1.2 36,599 13

Index Scan using awarded_prize_drawn_value_seq_idx on awarded_prize x10 (cost=0.57..84,378.19 rows=30,035 width=12) (actual time=0.042..88.281 rows=36,599 loops=13)

  • Index Cond: (prize_item_id = x11.prize_item_id)
14. 2,854.740 2,854.740 ↑ 9.0 4 475,790

Index Only Scan using awarded_prize_state_pk on awarded_prize_state x7 (cost=0.57..4.76 rows=36 width=12) (actual time=0.005..0.006 rows=4 loops=475,790)

  • Index Cond: (award_id = x10.award_id)
  • Heap Fetches: 0
15. 3,330.530 3,330.530 ↓ 0.0 0 475,790

Index Scan using awarded_prize_state_pk on awarded_prize_state x4 (cost=0.57..18.48 rows=1 width=47) (actual time=0.007..0.007 rows=0 loops=475,790)

  • Index Cond: ((award_id = x7.award_id) AND (version = (max(x7.version))))
  • Filter: ((fulfillment_order_id IS NULL) AND (resend_sequence_number IS NULL) AND (prize_status_id = 6))
  • Rows Removed by Filter: 1
16. 0.000 0.000 ↓ 0.0 0

Index Scan using awarded_prize_pk on awarded_prize x3 (cost=0.57..7.83 rows=1 width=32) (never executed)

  • Index Cond: (award_id = x4.award_id)
17. 5.432 11.489 ↑ 1.0 28,441 1

Hash (cost=616.41..616.41 rows=28,441 width=42) (actual time=11.489..11.489 rows=28,441 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,432kB
18. 6.057 6.057 ↑ 1.0 28,441 1

Seq Scan on prize_definition x5 (cost=0.00..616.41 rows=28,441 width=42) (actual time=0.017..6.057 rows=28,441 loops=1)

19. 0.454 0.997 ↑ 1.0 3,686 1

Hash (cost=60.86..60.86 rows=3,686 width=18) (actual time=0.996..0.997 rows=3,686 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 221kB
20. 0.543 0.543 ↑ 1.0 3,686 1

Seq Scan on draw_code_mapping x2 (cost=0.00..60.86 rows=3,686 width=18) (actual time=0.021..0.543 rows=3,686 loops=1)

21. 0.000 0.000 ↓ 0.0 0

Index Scan using winning_ticket_pk on winning_ticket x6 (cost=0.57..7.87 rows=1 width=27) (never executed)

  • Index Cond: ((internal_draw_id = x3.internal_draw_id) AND (order_id = x3.order_id) AND (order_item_id = x3.order_item_id))
Planning time : 2.944 ms
Execution time : 9,185.119 ms