explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1Dkp : Optimization for: aurora_exec_plan; plan #lq0E

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2.918 248.411 ↓ 24.7 866 1

Hash Left Join (cost=2,348.92..3,548.75 rows=35 width=332) (actual time=105.281..248.411 rows=866 loops=1)

  • Hash Cond: ((event.id = pool_event_1.event_id) AND (source.id = pool_1.source_id))
2. 1.759 103.885 ↓ 24.7 866 1

Nested Loop Left Join (cost=48.10..530.60 rows=35 width=302) (actual time=2.522..103.885 rows=866 loops=1)

  • Join Filter: (phone_channel.source_id = source.id)
  • Rows Removed by Join Filter: 866
3. 1.766 101.260 ↓ 24.7 866 1

Nested Loop Left Join (cost=48.10..523.59 rows=35 width=300) (actual time=2.479..101.260 rows=866 loops=1)

  • Join Filter: (channel.source_id = source.id)
  • Rows Removed by Join Filter: 866
4. 1.922 98.628 ↓ 24.7 866 1

Nested Loop Left Join (cost=48.10..516.59 rows=35 width=296) (actual time=2.435..98.628 rows=866 loops=1)

  • Join Filter: (pool.source_id = source.id)
  • Rows Removed by Join Filter: 405
5. 2.402 13.570 ↓ 24.7 866 1

Nested Loop (cost=47.25..147.17 rows=35 width=290) (actual time=2.341..13.570 rows=866 loops=1)

  • Join Filter: (meeting.sport_code_id = sport_code.id)
  • Rows Removed by Join Filter: 1732
6. 0.926 10.302 ↓ 24.7 866 1

Hash Left Join (cost=47.25..144.55 rows=35 width=284) (actual time=2.324..10.302 rows=866 loops=1)

  • Hash Cond: ((source.id = book.source_id) AND (event.id = book.event_id))
  • Join Filter: (NOT (SubPlan 7))
7. 0.960 7.608 ↓ 24.7 866 1

Nested Loop Left Join (cost=38.00..118.63 rows=35 width=261) (actual time=2.027..7.608 rows=866 loops=1)

8. 0.941 3.296 ↓ 12.0 419 1

Hash Join (cost=37.72..65.13 rows=35 width=234) (actual time=1.995..3.296 rows=419 loops=1)

  • Hash Cond: ((source_event.source_id = source_meeting.source_id) AND (source_event.event_id = event.id))
9. 0.396 0.396 ↑ 1.0 975 1

Seq Scan on source_event (cost=0.00..19.75 rows=975 width=26) (actual time=0.009..0.396 rows=975 loops=1)

10. 0.485 1.959 ↓ 4.5 421 1

Hash (cost=36.32..36.32 rows=93 width=220) (actual time=1.959..1.959 rows=421 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
11. 0.629 1.474 ↓ 4.5 421 1

Nested Loop (cost=6.62..36.32 rows=93 width=220) (actual time=0.157..1.474 rows=421 loops=1)

  • Join Filter: (meeting.id = event.meeting_id)
12. 0.068 0.385 ↓ 3.1 46 1

Nested Loop (cost=6.34..11.51 rows=15 width=102) (actual time=0.143..0.385 rows=46 loops=1)

  • Join Filter: (source_meeting.source_id = source.id)
  • Rows Removed by Join Filter: 47
13. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on source (cost=0.00..1.02 rows=1 width=15) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (name = 'TabcorpNSW'::text)
  • Rows Removed by Filter: 1
14. 0.146 0.310 ↓ 3.1 93 1

Hash Join (cost=6.34..10.11 rows=30 width=87) (actual time=0.131..0.310 rows=93 loops=1)

  • Hash Cond: (source_meeting.meeting_id = meeting.id)
15. 0.051 0.051 ↑ 1.0 107 1

Seq Scan on source_meeting (cost=0.00..3.07 rows=107 width=23) (actual time=0.003..0.051 rows=107 loops=1)

16. 0.041 0.113 ↑ 1.0 58 1

Hash (cost=5.61..5.61 rows=58 width=64) (actual time=0.113..0.113 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
17. 0.072 0.072 ↑ 1.0 58 1

Seq Scan on meeting (cost=0.00..5.61 rows=58 width=64) (actual time=0.008..0.072 rows=58 loops=1)

  • Filter: (meeting_date = '2019-11-02'::date)
  • Rows Removed by Filter: 151
18. 0.460 0.460 ↑ 1.0 9 46

Index Scan using event_meeting_id_idx on event (cost=0.28..1.54 rows=9 width=126) (actual time=0.003..0.010 rows=9 loops=46)

  • Index Cond: (meeting_id = source_meeting.meeting_id)
19. 3.352 3.352 ↓ 2.0 2 419

Index Scan using contestant_event_id_idx on contestant (cost=0.29..1.52 rows=1 width=31) (actual time=0.005..0.008 rows=2 loops=419)

  • Index Cond: (event_id = event.id)
  • Filter: (((finishing_position IS NOT NULL) AND (finishing_position > 0)) OR (contestant_status = 'Scratched'::text) OR (contestant_status = 'LateScratched'::text))
  • Rows Removed by Filter: 10
20. 0.073 0.204 ↑ 2.5 52 1

Hash (cost=7.30..7.30 rows=130 width=31) (actual time=0.204..0.204 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
21. 0.131 0.131 ↑ 1.0 130 1

Seq Scan on book (cost=0.00..7.30 rows=130 width=31) (actual time=0.004..0.131 rows=130 loops=1)

22.          

SubPlan (for Hash Left Join)

23. 0.136 1.564 ↓ 0.0 0 68

Bitmap Heap Scan on excluded_channel_mapping (cost=10.76..21.30 rows=9 width=4) (actual time=0.023..0.023 rows=0 loops=68)

  • Recheck Cond: (channel_id = $6)
24.          

Initplan (for Bitmap Heap Scan)

25. 1.360 1.360 ↑ 1.0 1 68

Seq Scan on channel channel_1 (cost=0.00..6.54 rows=1 width=4) (actual time=0.014..0.020 rows=1 loops=68)

  • Filter: ((spectrum_id = 'NOTPA'::text) AND (source_id = source.id))
  • Rows Removed by Filter: 235
26. 0.068 0.068 ↓ 0.0 0 68

Bitmap Index Scan on excluded_channel_mapping_channel_id_idx (cost=0.00..4.22 rows=9 width=0) (actual time=0.001..0.001 rows=0 loops=68)

  • Index Cond: (channel_id = $6)
27. 0.861 0.866 ↑ 1.0 3 866

Materialize (cost=0.00..1.04 rows=3 width=14) (actual time=0.000..0.001 rows=3 loops=866)

28. 0.005 0.005 ↑ 1.0 3 1

Seq Scan on sport_code (cost=0.00..1.03 rows=3 width=14) (actual time=0.003..0.005 rows=3 loops=1)

29. 13.270 83.136 ↑ 1.0 1 866

Nested Loop (cost=0.85..10.54 rows=1 width=14) (actual time=0.057..0.096 rows=1 loops=866)

30. 28.090 58.888 ↓ 2.6 13 866

Nested Loop (cost=0.57..8.44 rows=5 width=18) (actual time=0.030..0.068 rows=13 loops=866)

31. 11.258 11.258 ↑ 1.0 23 866

Index Scan using pool_event_event_id_idx on pool_event (cost=0.29..1.04 rows=23 width=8) (actual time=0.002..0.013 rows=23 loops=866)

  • Index Cond: (event_id = event.id)
32. 19.540 19.540 ↑ 1.0 1 19,540

Index Scan using dividend_pool_id_idx on dividend (cost=0.28..0.31 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=19,540)

  • Index Cond: (pool_id = pool_event.pool_id)
33. 10.978 10.978 ↓ 0.0 0 10,978

Index Scan using pool_pkey on pool (cost=0.29..0.41 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=10,978)

  • Index Cond: (id = pool_event.pool_id)
  • Filter: ((wagering_product = 'Win'::text) AND (pool_status = 'Closed'::text))
  • Rows Removed by Filter: 1
34. 0.826 0.866 ↑ 1.0 2 866

Materialize (cost=0.00..5.96 rows=2 width=8) (actual time=0.000..0.001 rows=2 loops=866)

35. 0.040 0.040 ↑ 1.0 2 1

Seq Scan on channel (cost=0.00..5.95 rows=2 width=8) (actual time=0.013..0.040 rows=2 loops=1)

  • Filter: (spectrum_id = 'NOTPA'::text)
  • Rows Removed by Filter: 234
36. 0.822 0.866 ↑ 1.0 2 866

Materialize (cost=0.00..5.96 rows=2 width=6) (actual time=0.000..0.001 rows=2 loops=866)

37. 0.044 0.044 ↑ 1.0 2 1

Seq Scan on channel phone_channel (cost=0.00..5.95 rows=2 width=6) (actual time=0.011..0.044 rows=2 loops=1)

  • Filter: (spectrum_id = 'NTAA'::text)
  • Rows Removed by Filter: 234
38. 0.471 102.638 ↓ 8.1 868 1

Hash (cost=2,299.22..2,299.22 rows=107 width=42) (actual time=102.638..102.638 rows=868 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
39. 3.908 102.167 ↓ 8.1 868 1

Hash Right Join (cost=2,072.83..2,299.22 rows=107 width=42) (actual time=86.147..102.167 rows=868 loops=1)

  • Hash Cond: (pool_dividends.pool_id = pool_1.id)
40. 6.572 83.286 ↑ 1.0 9,487 1

Subquery Scan on pool_dividends (cost=1,503.42..1,693.16 rows=9,487 width=36) (actual time=71.131..83.286 rows=9,487 loops=1)

41. 18.722 76.714 ↑ 1.0 9,487 1

HashAggregate (cost=1,503.42..1,598.29 rows=9,487 width=26) (actual time=71.123..76.714 rows=9,487 loops=1)

  • Group Key: pool_2.id, pool_2.source_id
42. 11.867 57.992 ↓ 1.1 11,618 1

Hash Join (cost=642.82..1,311.30 rows=10,978 width=26) (actual time=24.283..57.992 rows=11,618 loops=1)

  • Hash Cond: ((pool_event_2.event_id = source_event_1.event_id) AND (pool_2.source_id = source_event_1.source_id))
43. 17.031 45.281 ↓ 1.1 11,618 1

Hash Join (cost=608.45..1,056.59 rows=11,056 width=23) (actual time=23.410..45.281 rows=11,618 loops=1)

  • Hash Cond: (pool_event_2.pool_id = pool_2.id)
44. 4.867 4.867 ↑ 1.0 11,056 1

Seq Scan on pool_event pool_event_2 (cost=0.00..185.56 rows=11,056 width=8) (actual time=0.004..4.867 rows=11,056 loops=1)

45. 6.049 23.383 ↓ 1.1 10,049 1

Hash (cost=489.86..489.86 rows=9,487 width=19) (actual time=23.383..23.383 rows=10,049 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 460kB
46. 10.173 17.334 ↓ 1.1 10,049 1

Hash Left Join (cost=70.14..489.86 rows=9,487 width=19) (actual time=1.851..17.334 rows=10,049 loops=1)

  • Hash Cond: (pool_2.id = dividend_1.pool_id)
47. 5.324 5.324 ↑ 1.0 9,487 1

Seq Scan on pool pool_2 (cost=0.00..314.87 rows=9,487 width=13) (actual time=0.004..5.324 rows=9,487 loops=1)

48. 0.909 1.837 ↑ 1.0 2,184 1

Hash (cost=42.84..42.84 rows=2,184 width=10) (actual time=1.837..1.837 rows=2,184 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 94kB
49. 0.928 0.928 ↑ 1.0 2,184 1

Seq Scan on dividend dividend_1 (cost=0.00..42.84 rows=2,184 width=10) (actual time=0.004..0.928 rows=2,184 loops=1)

50. 0.430 0.844 ↑ 1.0 975 1

Hash (cost=19.75..19.75 rows=975 width=15) (actual time=0.844..0.844 rows=975 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
51. 0.414 0.414 ↑ 1.0 975 1

Seq Scan on source_event source_event_1 (cost=0.00..19.75 rows=975 width=15) (actual time=0.007..0.414 rows=975 loops=1)

52. 0.514 14.973 ↓ 8.1 868 1

Hash (cost=568.08..568.08 rows=107 width=14) (actual time=14.973..14.973 rows=868 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
53. 1.049 14.459 ↓ 8.1 868 1

Hash Join (cost=349.44..568.08 rows=107 width=14) (actual time=11.494..14.459 rows=868 loops=1)

  • Hash Cond: (pool_event_1.pool_id = pool_1.id)
54. 1.945 1.945 ↑ 1.0 1,165 1

Seq Scan on pool_event pool_event_1 (cost=0.00..213.20 rows=1,165 width=8) (actual time=0.009..1.945 rows=1,165 loops=1)

  • Filter: (leg_number = 2)
  • Rows Removed by Filter: 9891
55. 0.629 11.465 ↑ 1.0 868 1

Hash (cost=338.59..338.59 rows=868 width=10) (actual time=11.465..11.465 rows=868 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
56. 5.246 10.836 ↑ 1.0 868 1

Seq Scan on pool pool_1 (cost=0.00..338.59 rows=868 width=10) (actual time=2.674..10.836 rows=868 loops=1)

  • Filter: (wagering_product = 'RunningDouble'::text)
  • Rows Removed by Filter: 8619
57.          

SubPlan (for Seq Scan)

58. 4.340 4.340 ↓ 0.0 0 868

Index Only Scan using odds_pool_id_idx on odds (cost=0.42..95.60 rows=57 width=0) (actual time=0.005..0.005 rows=0 loops=868)

  • Index Cond: (pool_id = pool_1.id)
  • Heap Fetches: 170
59. 0.000 0.000 ↓ 0.0 0

Seq Scan on odds odds_1 (cost=0.00..4,868.72 rows=266,672 width=4) (never executed)

60. 0.000 0.000 ↓ 0.0 0

Index Only Scan using dividend_pool_id_idx on dividend dividend_2 (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: (pool_id = pool_1.id)
  • Heap Fetches: 0
61. 1.250 1.250 ↑ 1.0 2,184 1

Seq Scan on dividend dividend_3 (cost=0.00..42.84 rows=2,184 width=4) (actual time=0.004..1.250 rows=2,184 loops=1)

62.          

SubPlan (for Hash Left Join)

63. 20.108 38.970 ↑ 1.0 1 866

Hash Join (cost=8.30..10.09 rows=1 width=8) (actual time=0.007..0.045 rows=1 loops=866)

  • Hash Cond: (broadcast_channel.id = broadcast_channel_mapping.broadcast_channel_id)
64. 16.264 16.264 ↑ 1.0 56 856

Seq Scan on broadcast_channel (cost=0.00..1.56 rows=56 width=12) (actual time=0.001..0.019 rows=56 loops=856)

65. 0.866 2.598 ↑ 1.0 1 866

Hash (cost=8.29..8.29 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=866)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
66. 1.732 1.732 ↑ 1.0 1 866

Index Scan using broadcast_channel_mapping_source_event_id_idx on broadcast_channel_mapping (cost=0.28..8.29 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=866)

  • Index Cond: (source_event_id = source_event.id)
Planning time : 8.167 ms
Execution time : 249.443 ms