explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3fMk

Settings
# exclusive inclusive rows x rows loops node
1. 1,269.443 7,608.002 ↓ 1.0 427,853 1

Hash Join (cost=40,984.94..3,934,851.50 rows=427,852 width=577) (actual time=719.577..7,608.002 rows=427,853 loops=1)

  • Hash Cond: (events.programs_id = programs.id)
  • Buffers: shared hit=3144703 dirtied=7, temp read=12354 written=12342
2. 351.294 5,482.229 ↓ 1.0 427,853 1

Hash Join (cost=40,916.55..311,411.48 rows=427,852 width=545) (actual time=718.921..5,482.229 rows=427,853 loops=1)

  • Hash Cond: (claims_1.staffing_users_id = staffing_users.id)
  • Buffers: shared hit=1433057 dirtied=7, temp read=12354 written=12342
3. 326.621 5,098.398 ↓ 1.0 427,853 1

Hash Join (cost=37,851.94..302,463.91 rows=427,852 width=531) (actual time=686.152..5,098.398 rows=427,853 loops=1)

  • Hash Cond: (claims_1.payroll_periods_id = payroll_periods.id)
  • Buffers: shared hit=1430940 dirtied=7, temp read=12354 written=12342
4. 833.912 4,771.695 ↓ 1.0 427,853 1

Hash Join (cost=37,846.24..296,575.24 rows=427,852 width=523) (actual time=686.052..4,771.695 rows=427,853 loops=1)

  • Hash Cond: (events_venues.venues_profiles_id = venues_profiles.id)
  • Buffers: shared hit=1430937 dirtied=7, temp read=12354 written=12342
5. 640.241 3,252.602 ↓ 1.0 427,853 1

Merge Join (cost=233.36..195,962.39 rows=427,852 width=507) (actual time=0.258..3,252.602 rows=427,853 loops=1)

  • Merge Cond: (claims_1.events_id = events_venues.events_id)
  • Buffers: shared hit=1416142 dirtied=7
6. 839.271 2,004.906 ↓ 1.0 427,853 1

Merge Join (cost=92.07..139,974.90 rows=427,852 width=507) (actual time=0.151..2,004.906 rows=427,853 loops=1)

  • Merge Cond: (claims_1.events_id = events.id)
  • Buffers: shared hit=988274 dirtied=7
7. 421.001 421.001 ↓ 1.0 427,853 1

Index Scan using claims_events_id_idx on claims claims_1 (cost=0.42..48,097.43 rows=427,852 width=452) (actual time=0.014..421.001 rows=427,853 loops=1)

  • Buffers: shared hit=380435 dirtied=7
8. 744.634 744.634 ↓ 1.2 1,267,506 1

Index Scan using events_pkey on events (cost=0.43..83,783.14 rows=1,099,416 width=55) (actual time=0.008..744.634 rows=1,267,506 loops=1)

  • Buffers: shared hit=607839
9. 607.455 607.455 ↓ 1.2 1,267,881 1

Index Scan using events_venues_events_id on events_venues (cost=0.43..48,050.70 rows=1,091,201 width=8) (actual time=0.008..607.455 rows=1,267,881 loops=1)

  • Buffers: shared hit=427868
10. 88.804 685.181 ↑ 1.0 191,961 1

Hash (cost=34,082.95..34,082.95 rows=192,235 width=20) (actual time=685.181..685.181 rows=191,961 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3065kB
  • Buffers: shared hit=14795, temp read=2217 written=2951
11. 311.981 596.377 ↑ 1.0 191,961 1

Hash Join (cost=6,754.29..34,082.95 rows=192,235 width=20) (actual time=125.097..596.377 rows=191,961 loops=1)

  • Hash Cond: (venues.id = venues_profiles.venues_id)
  • Buffers: shared hit=14795, temp read=2217 written=2211
12. 159.875 159.875 ↑ 1.0 446,786 1

Seq Scan on venues (cost=0.00..17,646.68 rows=452,968 width=20) (actual time=0.005..159.875 rows=446,786 loops=1)

  • Buffers: shared hit=13117
13. 67.768 124.521 ↑ 1.0 192,100 1

Hash (cost=3,600.35..3,600.35 rows=192,235 width=8) (actual time=124.521..124.521 rows=192,100 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2895kB
  • Buffers: shared hit=1678, temp written=491
14. 56.753 56.753 ↑ 1.0 192,100 1

Seq Scan on venues_profiles (cost=0.00..3,600.35 rows=192,235 width=8) (actual time=0.003..56.753 rows=192,100 loops=1)

  • Buffers: shared hit=1678
15. 0.039 0.082 ↓ 1.0 121 1

Hash (cost=4.20..4.20 rows=120 width=12) (actual time=0.082..0.082 rows=121 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=3
16. 0.043 0.043 ↓ 1.0 121 1

Seq Scan on payroll_periods (cost=0.00..4.20 rows=120 width=12) (actual time=0.004..0.043 rows=121 loops=1)

  • Buffers: shared hit=3
17. 16.035 32.537 ↑ 1.0 42,116 1

Hash (cost=2,538.16..2,538.16 rows=42,116 width=18) (actual time=32.537..32.537 rows=42,116 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2673kB
  • Buffers: shared hit=2117
18. 16.502 16.502 ↑ 1.0 42,116 1

Seq Scan on staffing_users (cost=0.00..2,538.16 rows=42,116 width=18) (actual time=0.003..16.502 rows=42,116 loops=1)

  • Buffers: shared hit=2117
19. 0.245 0.624 ↑ 1.0 683 1

Hash (cost=59.84..59.84 rows=684 width=36) (actual time=0.624..0.624 rows=683 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
  • Buffers: shared hit=53
20. 0.379 0.379 ↑ 1.0 683 1

Seq Scan on programs (cost=0.00..59.84 rows=684 width=36) (actual time=0.004..0.379 rows=683 loops=1)

  • Buffers: shared hit=53
21.          

SubPlan (forHash Join)

22. 855.706 855.706 ↑ 1.0 1 427,853

Index Scan using events_staffing_days_positions_pkey on events_staffing_days_positions (cost=0.43..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=427,853)

  • Index Cond: (id = claims_1.events_staffing_days_positions_id)
  • Buffers: shared hit=1711593