explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2VnK

Settings
# exclusive inclusive rows x rows loops node
1. 11.052 220,577.863 ↓ 10.0 409 1

Sort (cost=8,020.20..8,020.30 rows=41 width=199) (actual time=220,573.451..220,577.863 rows=409 loops=1)

  • Sort Key: d.dealer_no, i."position", w."position
  • Sort Method: quicksort Memory: 133kB
  • Buffers: shared hit=1,316,286 read=57,915, temp read=6,341 written=6,342
2. 2,474.420 220,566.811 ↓ 10.0 409 1

GroupAggregate (cost=8,017.26..8,019.10 rows=41 width=199) (actual time=215,676.132..220,566.811 rows=409 loops=1)

  • Group Key: pb.playbook_id, d.dealer_no, i."position", i.title, i.created_at, pi.participation_id, w.wave_id, p.is_auto_sendout_active, mr.state
  • Buffers: shared hit=1,316,280 read=57,915, temp read=6,341 written=6,342
3. 7,175.363 218,092.391 ↓ 5,588.7 229,136 1

Sort (cost=8,017.26..8,017.36 rows=41 width=185) (actual time=215,676.010..218,092.391 rows=229,136 loops=1)

  • Sort Key: d.dealer_no, i."position", i.title, i.created_at, pi.participation_id, w.wave_id, p.is_auto_sendout_active, mr.state
  • Sort Method: external merge Disk: 50,728kB
  • Buffers: shared hit=1,316,280 read=57,915, temp read=6,341 written=6,342
4. 9,832.445 210,917.028 ↓ 5,588.7 229,136 1

Nested Loop Left Join (cost=4.20..8,016.16 rows=41 width=185) (actual time=74.412..210,917.028 rows=229,136 loops=1)

  • Buffers: shared hit=1,316,269 read=57,915
5. 5,808.729 49,625.687 ↓ 5,588.7 229,136 1

Nested Loop Left Join (cost=3.64..7,694.97 rows=41 width=184) (actual time=74.349..49,625.687 rows=229,136 loops=1)

  • Buffers: shared hit=213,094 read=15,880
6. 30.704 1,956.626 ↓ 409.0 409 1

Nested Loop (cost=3.22..945.99 rows=1 width=183) (actual time=24.009..1,956.626 rows=409 loops=1)

  • Join Filter: (((m.domain #>> '{domain_id,wave_id}'::text[]))::uuid = w.parent_wave_id)
  • Rows Removed by Join Filter: 409
  • Buffers: shared hit=13,242 read=274
7. 18.943 1,123.055 ↓ 409.0 409 1

Nested Loop (cost=2.79..930.97 rows=1 width=347) (actual time=23.890..1,123.055 rows=409 loops=1)

  • Buffers: shared hit=11,685 read=166
8. 13.885 1,090.206 ↓ 409.0 409 1

Nested Loop (cost=2.52..922.66 rows=1 width=297) (actual time=23.815..1,090.206 rows=409 loops=1)

  • Buffers: shared hit=10,458 read=166
9. 7.715 187.723 ↓ 202.0 202 1

Nested Loop (cost=0.70..16.73 rows=1 width=56) (actual time=14.024..187.723 rows=202 loops=1)

  • Buffers: shared hit=802 read=2
10. 4.268 4.268 ↓ 202.0 202 1

Index Scan using uq_participations__playbook_scope on participations p (cost=0.41..8.43 rows=1 width=39) (actual time=0.088..4.268 rows=202 loops=1)

  • Index Cond: (playbook_id = '593fc9ba-14c4-4060-9675-3434c8a2d83a'::uuid)
  • Buffers: shared hit=198
11. 175.740 175.740 ↑ 1.0 1 202

Index Scan using dealers_dealer_id on dealers d (cost=0.29..8.30 rows=1 width=17) (actual time=0.870..0.870 rows=1 loops=202)

  • Index Cond: ((dealer_id)::text = (p.scope)::text)
  • Buffers: shared hit=604 read=2
12. 25.452 888.598 ↑ 6.0 2 202

Nested Loop (cost=1.82..905.81 rows=12 width=275) (actual time=3.349..4.399 rows=2 loops=202)

  • Buffers: shared hit=9,656 read=164
13. 436.118 436.118 ↑ 6.0 2 202

Index Only Scan using participation_iterations_pkey on participation_iterations pi (cost=0.42..8.63 rows=12 width=32) (actual time=2.133..2.159 rows=2 loops=202)

  • Index Cond: (participation_id = p.participation_id)
  • Heap Fetches: 118
  • Buffers: shared hit=575 read=91
14. 29.088 427.028 ↑ 1.0 1 404

Nested Loop (cost=1.40..74.75 rows=1 width=243) (actual time=0.907..1.057 rows=1 loops=404)

  • Buffers: shared hit=9,081 read=73
15. 289.264 289.264 ↑ 1.0 1 404

Index Scan using iterations_pkey on iterations i (cost=0.42..7.68 rows=1 width=78) (actual time=0.704..0.716 rows=1 loops=404)

  • Index Cond: (iteration_id = pi.iteration_id)
  • Buffers: shared hit=1,574 read=42
16. 40.804 108.676 ↑ 1.0 1 404

Nested Loop (cost=0.97..67.06 rows=1 width=197) (actual time=0.154..0.269 rows=1 loops=404)

  • Buffers: shared hit=7,507 read=31
17. 33.128 33.128 ↓ 2.0 2 404

Index Scan using milestones_milestone_index on milestones m (cost=0.42..62.15 rows=1 width=199) (actual time=0.040..0.082 rows=2 loops=404)

  • Index Cond: (iteration_id = i.parent_iteration_id)
  • Filter: ((domain ->> 'type'::text) = 'Mehrkanal\Dmc\Scheduling\Model\Domain\AddressApprove'::text)
  • Rows Removed by Filter: 11
  • Buffers: shared hit=3,636
18. 34.744 34.744 ↑ 1.0 1 808

Index Only Scan using milestone_results_milestone_scope_unq on milestone_results mr (cost=0.56..4.90 rows=1 width=30) (actual time=0.035..0.043 rows=1 loops=808)

  • Index Cond: ((milestone_id = m.milestone_id) AND (scope = (d.dealer_id)::text))
  • Heap Fetches: 406
  • Buffers: shared hit=3,871 read=31
19. 13.906 13.906 ↑ 1.0 1 409

Index Scan using playbooks_pkey on playbooks pb (cost=0.28..8.29 rows=1 width=66) (actual time=0.022..0.034 rows=1 loops=409)

  • Index Cond: (playbook_id = '593fc9ba-14c4-4060-9675-3434c8a2d83a'::uuid)
  • Buffers: shared hit=1,227
20. 802.867 802.867 ↑ 1.5 2 409

Index Scan using waves_iteration_id on waves w (cost=0.42..14.96 rows=3 width=67) (actual time=1.894..1.963 rows=2 loops=409)

  • Index Cond: (iteration_id = i.iteration_id)
  • Buffers: shared hit=1,557 read=108
21. 5,724.849 41,860.332 ↑ 3.2 560 409

Append (cost=0.42..6,730.83 rows=1,815 width=33) (actual time=9.204..102.348 rows=560 loops=409)

  • Buffers: shared hit=199,852 read=15,606
22. 710.060 710.060 ↓ 2.5 290 20

Index Scan using contact_states_0_pkey on contact_states_0 cs (cost=0.42..422.39 rows=114 width=33) (actual time=8.311..35.503 rows=290 loops=20)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=5,844 read=150
23. 2,258.760 2,258.760 ↓ 7.0 782 21

Index Scan using contact_states_1_pkey on contact_states_1 cs_1 (cost=0.42..415.10 rows=112 width=33) (actual time=12.462..107.560 rows=782 loops=21)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=13,273 read=1,294
24. 1,244.402 1,904.361 ↓ 4.3 513 31

Bitmap Heap Scan on contact_states_2 cs_2 (cost=8.58..432.42 rows=118 width=33) (actual time=34.091..61.431 rows=513 loops=31)

  • Recheck Cond: (target_list_id = i.target_list_id)
  • Heap Blocks: exact=7,969
  • Buffers: shared hit=6,766 read=1,719
25. 659.959 659.959 ↓ 4.4 524 31

Bitmap Index Scan on contact_states_2_pkey (cost=0.00..8.55 rows=118 width=0) (actual time=21.288..21.289 rows=524 loops=31)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=282 read=140
26. 2,382.702 2,382.702 ↓ 3.4 389 14

Index Scan using contact_states_3_pkey on contact_states_3 cs_3 (cost=0.42..422.36 rows=114 width=33) (actual time=4.749..170.193 rows=389 loops=14)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=5,006 read=638
27. 686.412 686.412 ↓ 4.6 514 18

Index Scan using contact_states_4_pkey on contact_states_4 cs_4 (cost=0.42..411.58 rows=111 width=33) (actual time=3.278..38.134 rows=514 loops=18)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=8,935 read=587
28. 3,384.381 3,384.381 ↓ 7.4 848 33

Index Scan using contact_states_5_pkey on contact_states_5 cs_5 (cost=0.42..426.09 rows=115 width=33) (actual time=7.100..102.557 rows=848 loops=33)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=26,419 read=1,217
29. 3,605.856 3,605.856 ↓ 6.3 717 32

Index Scan using contact_states_6_pkey on contact_states_6 cs_6 (cost=0.42..418.84 rows=113 width=33) (actual time=4.529..112.683 rows=717 loops=32)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=22,575 read=803
30. 2,723.616 2,723.616 ↓ 6.2 712 28

Index Scan using contact_states_7_pkey on contact_states_7 cs_7 (cost=0.42..422.46 rows=114 width=33) (actual time=4.123..97.272 rows=712 loops=28)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=17,962 read=1,440
31. 1,499.370 1,499.370 ↓ 2.4 261 23

Index Scan using contact_states_8_pkey on contact_states_8 cs_8 (cost=0.42..411.54 rows=111 width=33) (actual time=24.279..65.190 rows=261 loops=23)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=4,921 read=643
32. 2,451.132 2,451.132 ↓ 3.5 393 36

Index Scan using contact_states_9_pkey on contact_states_9 cs_9 (cost=0.42..411.48 rows=111 width=33) (actual time=6.759..68.087 rows=393 loops=36)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=10,398 read=1,397
33. 3,839.040 3,839.040 ↓ 3.8 452 31

Index Scan using contact_states_a_pkey on contact_states_a cs_10 (cost=0.42..440.68 rows=119 width=33) (actual time=7.461..123.840 rows=452 loops=31)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=14,047 read=416
34. 3,649.500 3,649.500 ↓ 5.1 564 25

Index Scan using contact_states_b_pkey on contact_states_b cs_11 (cost=0.42..411.50 rows=111 width=33) (actual time=7.731..145.980 rows=564 loops=25)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=13,178 read=1,273
35. 158.560 158.560 ↓ 2.9 324 16

Index Scan using contact_states_c_pkey on contact_states_c cs_12 (cost=0.42..415.21 rows=112 width=33) (actual time=0.059..9.910 rows=324 loops=16)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=3,933 read=1,399
36. 3,283.656 3,283.656 ↓ 5.4 599 24

Index Scan using contact_states_d_pkey on contact_states_d cs_13 (cost=0.42..407.92 rows=110 width=33) (actual time=1.503..136.819 rows=599 loops=24)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=12,769 read=1,493
37. 2,108.001 2,108.001 ↓ 6.9 824 21

Index Scan using contact_states_e_pkey on contact_states_e cs_14 (cost=0.42..440.60 rows=119 width=33) (actual time=6.980..100.381 rows=824 loops=21)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=17,488 read=381
38. 1,490.076 1,490.076 ↓ 5.1 561 36

Index Scan using contact_states_f_pkey on contact_states_f cs_15 (cost=0.42..411.58 rows=111 width=33) (actual time=6.567..41.391 rows=561 loops=36)

  • Index Cond: (target_list_id = i.target_list_id)
  • Buffers: shared hit=16,338 read=756
39. 151,458.896 151,458.896 ↑ 1.0 1 229,136

Index Scan using contacts_pkey on contacts c (cost=0.56..7.83 rows=1 width=17) (actual time=0.661..0.661 rows=1 loops=229,136)

  • Index Cond: (contact_id = cs.contact_id)
  • Buffers: shared hit=1,103,175 read=42,035