explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lq0E : aurora_exec_plan

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5.688 2,841.459 ↓ 24.7 866 1

Nested Loop Left Join (cost=1,347.06..10,918.94 rows=35 width=378) (actual time=1.022..2,841.459 rows=866 loops=1)

  • Join Filter: (source.id = pool_1.source_id)
  • Rows Removed by Join Filter: 751
2. 0.982 84.489 ↓ 24.7 866 1

Nested Loop Left Join (cost=49.61..551.37 rows=35 width=299) (actual time=0.954..84.489 rows=866 loops=1)

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

Nested Loop Left Join (cost=49.61..544.36 rows=35 width=297) (actual time=0.940..83.507 rows=866 loops=1)

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

Nested Loop Left Join (cost=49.61..537.36 rows=35 width=293) (actual time=0.918..82.524 rows=866 loops=1)

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

Nested Loop (cost=48.76..155.71 rows=35 width=287) (actual time=0.839..9.578 rows=866 loops=1)

  • Join Filter: (meeting.sport_code_id = sport_code.id)
  • Rows Removed by Join Filter: 1732
6. 0.004 0.004 ↑ 1.0 3 1

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

7. 1.021 8.811 ↓ 24.7 866 3

Materialize (cost=48.76..153.19 rows=35 width=281) (actual time=0.279..2.937 rows=866 loops=3)

8. 0.477 7.790 ↓ 24.7 866 1

Hash Left Join (cost=48.76..153.01 rows=35 width=281) (actual time=0.833..7.790 rows=866 loops=1)

  • Hash Cond: ((source.id = book.source_id) AND (event.id = book.event_id))
  • Join Filter: (NOT (SubPlan 6))
9. 0.598 5.754 ↓ 24.7 866 1

Nested Loop Left Join (cost=41.63..129.22 rows=35 width=258) (actual time=0.762..5.754 rows=866 loops=1)

10. 0.520 1.385 ↓ 12.0 419 1

Hash Join (cost=41.35..70.76 rows=35 width=231) (actual time=0.747..1.385 rows=419 loops=1)

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

Seq Scan on source_event (cost=0.00..21.75 rows=975 width=26) (actual time=0.004..0.132 rows=975 loops=1)

12. 0.206 0.733 ↓ 4.5 421 1

Hash (cost=39.95..39.95 rows=93 width=217) (actual time=0.733..0.733 rows=421 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 77kB
13. 0.132 0.527 ↓ 4.5 421 1

Nested Loop (cost=7.63..39.95 rows=93 width=217) (actual time=0.075..0.527 rows=421 loops=1)

  • Join Filter: (meeting.id = event.meeting_id)
14. 0.013 0.119 ↓ 3.1 46 1

Nested Loop (cost=7.35..12.11 rows=15 width=99) (actual time=0.063..0.119 rows=46 loops=1)

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

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

  • Filter: (name = 'TabcorpNSW'::text)
  • Rows Removed by Filter: 1
16. 0.040 0.103 ↓ 3.1 93 1

Hash Join (cost=7.35..10.71 rows=30 width=84) (actual time=0.059..0.103 rows=93 loops=1)

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

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

18. 0.015 0.048 ↑ 1.0 58 1

Hash (cost=6.62..6.62 rows=58 width=61) (actual time=0.048..0.048 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
19. 0.033 0.033 ↑ 1.0 58 1

Seq Scan on meeting (cost=0.00..6.62 rows=58 width=61) (actual time=0.008..0.033 rows=58 loops=1)

  • Filter: (meeting_date = '2019-11-02'::date)
  • Rows Removed by Filter: 152
20. 0.276 0.276 ↑ 1.0 9 46

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

  • Index Cond: (meeting_id = source_meeting.meeting_id)
21. 3.771 3.771 ↓ 2.0 2 419

Index Scan using contestant_event_id_idx on contestant (cost=0.29..1.66 rows=1 width=31) (actual time=0.007..0.009 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
22. 0.018 0.063 ↑ 2.4 52 1

Hash (cost=5.25..5.25 rows=125 width=31) (actual time=0.063..0.063 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
23. 0.045 0.045 ↓ 1.0 130 1

Seq Scan on book (cost=0.00..5.25 rows=125 width=31) (actual time=0.004..0.045 rows=130 loops=1)

24.          

SubPlan (for Hash Left Join)

25. 0.068 1.496 ↓ 0.0 0 68

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

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

Initplan (for Bitmap Heap Scan)

27. 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
28. 0.068 0.068 ↓ 0.0 0 68

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

  • Index Cond: (channel_id = $6)
29. 2.292 71.878 ↑ 1.0 1 866

Nested Loop (cost=0.85..10.89 rows=1 width=14) (actual time=0.054..0.083 rows=1 loops=866)

30. 19.430 47.630 ↓ 2.6 13 866

Nested Loop (cost=0.57..8.76 rows=5 width=18) (actual time=0.029..0.055 rows=13 loops=866)

31. 8.660 8.660 ↑ 1.0 23 866

Index Scan using pool_event_event_id_idx on pool_event (cost=0.29..1.12 rows=23 width=8) (actual time=0.005..0.010 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.32 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=19,540)

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

Index Scan using pool_pkey on pool (cost=0.29..0.43 rows=1 width=8) (actual time=0.002..0.002 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.000 0.000 ↑ 1.0 2 866

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

35. 0.025 0.025 ↑ 1.0 2 1

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

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

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

37. 0.021 0.021 ↑ 1.0 2 1

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

  • Filter: (spectrum_id = 'NTAA'::text)
  • Rows Removed by Filter: 234
38. 474.968 2,738.292 ↓ 2.0 2 866

Hash Right Join (cost=1,297.45..1,546.49 rows=1 width=42) (actual time=1.092..3.162 rows=2 loops=866)

  • Hash Cond: (pool_dividends.pool_id = pool_1.id)
39. 701.434 2,236.478 ↑ 1.0 9,487 751

Subquery Scan on pool_dividends (cost=1,270.24..1,483.69 rows=9,487 width=36) (actual time=0.023..2.978 rows=9,487 loops=751)

40. 1,522.456 1,535.044 ↑ 1.0 9,487 751

HashAggregate (cost=1,270.24..1,365.11 rows=9,487 width=56) (actual time=0.023..2.044 rows=9,487 loops=751)

  • Group Key: pool_2.id
41. 1.854 12.588 ↓ 1.1 11,618 1

Hash Left Join (cost=571.30..1,105.57 rows=10,978 width=26) (actual time=4.359..12.588 rows=11,618 loops=1)

  • Hash Cond: (pool_2.id = dividend_1.pool_id)
42. 2.504 10.112 ↓ 1.0 11,056 1

Hash Join (cost=502.83..943.56 rows=10,978 width=20) (actual time=3.712..10.112 rows=11,056 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. 2.848 7.307 ↑ 1.0 11,056 1

Hash Join (cost=466.46..686.84 rows=11,056 width=17) (actual time=3.407..7.307 rows=11,056 loops=1)

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

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

45. 1.540 3.352 ↑ 1.0 9,487 1

Hash (cost=347.87..347.87 rows=9,487 width=13) (actual time=3.352..3.352 rows=9,487 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 549kB
46. 1.812 1.812 ↑ 1.0 9,487 1

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

47. 0.153 0.301 ↑ 1.0 975 1

Hash (cost=21.75..21.75 rows=975 width=15) (actual time=0.301..0.301 rows=975 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
48. 0.148 0.148 ↑ 1.0 975 1

Seq Scan on source_event source_event_1 (cost=0.00..21.75 rows=975 width=15) (actual time=0.003..0.148 rows=975 loops=1)

49. 0.322 0.622 ↓ 1.0 2,184 1

Hash (cost=41.54..41.54 rows=2,154 width=10) (actual time=0.622..0.622 rows=2,184 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 126kB
50. 0.300 0.300 ↓ 1.0 2,184 1

Seq Scan on dividend dividend_1 (cost=0.00..41.54 rows=2,154 width=10) (actual time=0.006..0.300 rows=2,184 loops=1)

51. 0.866 26.846 ↓ 2.0 2 866

Hash (cost=27.20..27.20 rows=1 width=14) (actual time=0.031..0.031 rows=2 loops=866)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
52. 3.808 25.980 ↓ 2.0 2 866

Nested Loop (cost=0.57..27.20 rows=1 width=14) (actual time=0.017..0.030 rows=2 loops=866)

53. 7.794 7.794 ↑ 1.0 2 866

Index Scan using pool_event_event_id_idx on pool_event pool_event_1 (cost=0.29..1.18 rows=2 width=8) (actual time=0.006..0.009 rows=2 loops=866)

  • Index Cond: (event.id = event_id)
  • Filter: (leg_number = 2)
  • Rows Removed by Filter: 20
54. 6.868 14.378 ↑ 1.0 1 2,054

Index Scan using pool_pkey on pool pool_1 (cost=0.29..13.00 rows=1 width=10) (actual time=0.007..0.007 rows=1 loops=2,054)

  • Index Cond: (id = pool_event_1.pool_id)
  • Filter: (wagering_product = 'RunningDouble'::text)
  • Rows Removed by Filter: 0
55.          

SubPlan (for Index Scan)

56. 6.008 6.008 ↓ 0.0 0 1,502

Index Only Scan using odds_pool_id_idx on odds (cost=0.42..165.82 rows=58 width=0) (actual time=0.004..0.004 rows=0 loops=1,502)

  • Index Cond: (pool_id = pool_1.id)
  • Heap Fetches: 706
57. 1.502 1.502 ↓ 0.0 0 1,502

Index Only Scan using dividend_pool_id_idx on dividend dividend_2 (cost=0.28..8.30 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1,502)

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

Seq Scan on dividend dividend_3 (cost=0.00..41.54 rows=2,154 width=4) (never executed)

59.          

SubPlan (for Nested Loop Left Join)

60. 5.236 12.990 ↑ 1.0 1 866

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

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

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

62. 1.732 4.330 ↑ 1.0 1 866

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
63. 2.598 2.598 ↑ 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.003..0.003 rows=1 loops=866)

  • Index Cond: (source_event_id = source_event.id)
Planning time : 8.422 ms
Execution time : 2,842.451 ms