explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KYbl

Settings
# exclusive inclusive rows x rows loops node
1. 919.386 26,079.516 ↓ 25.9 71,587 1

Unique (cost=4,367,624.21..4,853,630.88 rows=2,768 width=32) (actual time=19,568.818..26,079.516 rows=71,587 loops=1)

2.          

CTE hidden_external_event_ids

3. 1,118.693 2,177.130 ↑ 2,181.7 1,758 1

Hash Join (cost=71.82..155,321.61 rows=3,835,357 width=4) (actual time=4.432..2,177.130 rows=1,758 loops=1)

  • Hash Cond: (events_persons_1.person_id = hidden_externals.external_id)
4. 1,057.061 1,057.061 ↓ 1.0 4,354,357 1

Seq Scan on events_persons events_persons_1 (cost=0.00..84,238.55 rows=4,354,355 width=8) (actual time=0.008..1,057.061 rows=4,354,357 loops=1)

5. 0.697 1.376 ↑ 1.0 2,481 1

Hash (cost=40.81..40.81 rows=2,481 width=4) (actual time=1.376..1.376 rows=2,481 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 120kB
6. 0.679 0.679 ↑ 1.0 2,481 1

Seq Scan on hidden_externals (cost=0.00..40.81 rows=2,481 width=4) (actual time=0.007..0.679 rows=2,481 loops=1)

7.          

CTE last_events_persons

8. 461.833 8,049.296 ↓ 25.0 69,146 1

Unique (cost=1,898,996.39..1,904,266.04 rows=2,768 width=16) (actual time=6,987.855..8,049.296 rows=69,146 loops=1)

9. 2,229.169 7,587.463 ↓ 1.9 2,034,352 1

Sort (cost=1,898,996.39..1,901,631.21 rows=1,053,929 width=16) (actual time=6,987.854..7,587.463 rows=2,034,352 loops=1)

  • Sort Key: events_persons_2.person_id, events_persons_2.start_time DESC
  • Sort Method: external merge Disk: 51696kB
10. 945.880 5,358.294 ↓ 1.9 2,034,352 1

Merge Anti Join (cost=533,576.68..1,793,564.78 rows=1,053,929 width=16) (actual time=2,179.113..5,358.294 rows=2,034,352 loops=1)

  • Merge Cond: (events_persons_2.event_id = hidden_external_event_ids.event_id)
11. 2,231.922 2,231.922 ↑ 1.0 2,081,265 1

Index Scan using events_persons_event_id_person_id_key on events_persons events_persons_2 (cost=0.43..186,585.82 rows=2,107,858 width=16) (actual time=0.030..2,231.922 rows=2,081,265 loops=1)

  • Filter: (start_time <= now())
  • Rows Removed by Filter: 2273092
12. 0.761 2,180.492 ↑ 2,181.7 1,758 1

Materialize (cost=533,576.25..552,753.04 rows=3,835,357 width=4) (actual time=2,179.080..2,180.492 rows=1,758 loops=1)

13. 1.426 2,179.731 ↑ 2,181.7 1,758 1

Sort (cost=533,576.25..543,164.64 rows=3,835,357 width=4) (actual time=2,179.078..2,179.731 rows=1,758 loops=1)

  • Sort Key: hidden_external_event_ids.event_id
  • Sort Method: quicksort Memory: 131kB
14. 2,178.305 2,178.305 ↑ 2,181.7 1,758 1

CTE Scan on hidden_external_event_ids (cost=0.00..76,707.14 rows=3,835,357 width=4) (actual time=4.433..2,178.305 rows=1,758 loops=1)

15.          

CTE next_events_persons

16. 493.819 6,269.138 ↑ 2.5 1,101 1

Unique (cost=1,976,416.65..1,982,032.89 rows=2,768 width=16) (actual time=5,104.204..6,269.138 rows=1,101 loops=1)

17. 2,438.703 5,775.319 ↓ 2.0 2,248,911 1

Sort (cost=1,976,416.65..1,979,224.77 rows=1,123,248 width=16) (actual time=5,104.202..5,775.319 rows=2,248,911 loops=1)

  • Sort Key: events_persons_3.person_id, events_persons_3.start_time
  • Sort Method: external merge Disk: 57144kB
18. 1,040.823 3,336.616 ↓ 2.0 2,248,911 1

Merge Anti Join (cost=533,576.68..1,863,534.46 rows=1,123,248 width=16) (actual time=1.468..3,336.616 rows=2,248,911 loops=1)

  • Merge Cond: (events_persons_3.event_id = hidden_external_event_ids_1.event_id)
19. 2,293.351 2,293.351 ↓ 1.0 2,273,092 1

Index Scan using events_persons_event_id_person_id_key on events_persons events_persons_3 (cost=0.43..186,585.82 rows=2,246,497 width=16) (actual time=0.485..2,293.351 rows=2,273,092 loops=1)

  • Filter: (start_time > now())
  • Rows Removed by Filter: 2081265
20. 0.747 2.442 ↑ 2,182.9 1,757 1

Materialize (cost=533,576.25..552,753.04 rows=3,835,357 width=4) (actual time=0.972..2.442 rows=1,757 loops=1)

21. 1.293 1.695 ↑ 2,182.9 1,757 1

Sort (cost=533,576.25..543,164.64 rows=3,835,357 width=4) (actual time=0.969..1.695 rows=1,757 loops=1)

  • Sort Key: hidden_external_event_ids_1.event_id
  • Sort Method: quicksort Memory: 131kB
22. 0.402 0.402 ↑ 2,181.7 1,758 1

CTE Scan on hidden_external_event_ids hidden_external_event_ids_1 (cost=0.00..76,707.14 rows=3,835,357 width=4) (actual time=0.001..0.402 rows=1,758 loops=1)

23. 3,160.028 25,160.130 ↑ 6.2 4,353,160 1

Merge Left Join (cost=326,003.68..744,067.34 rows=27,177,204 width=32) (actual time=19,568.816..25,160.130 rows=4,353,160 loops=1)

  • Merge Cond: (events_persons.person_id = last_events_persons.person_id)
24. 3,212.750 6,641.378 ↓ 2.2 4,353,160 1

Sort (cost=325,576.45..330,485.63 rows=1,963,671 width=8) (actual time=5,195.465..6,641.378 rows=4,353,160 loops=1)

  • Sort Key: events_persons.person_id
  • Sort Method: external merge Disk: 76552kB
25. 2,373.072 3,428.628 ↓ 2.2 4,353,160 1

Hash Join (cost=118.45..120,322.54 rows=1,963,671 width=8) (actual time=1.334..3,428.628 rows=4,353,160 loops=1)

  • Hash Cond: (events_persons.organization_id = organizations.id)
26. 1,054.238 1,054.238 ↓ 1.0 4,354,357 1

Seq Scan on events_persons (cost=0.00..84,238.55 rows=4,354,355 width=8) (actual time=0.007..1,054.238 rows=4,354,357 loops=1)

27. 0.381 1.318 ↑ 1.0 1,329 1

Hash (cost=101.84..101.84 rows=1,329 width=4) (actual time=1.318..1.318 rows=1,329 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 63kB
28. 0.937 0.937 ↑ 1.0 1,329 1

Seq Scan on organizations (cost=0.00..101.84 rows=1,329 width=4) (actual time=0.008..0.937 rows=1,329 loops=1)

  • Filter: (type = ANY ('{0,1}'::integer[]))
  • Rows Removed by Filter: 1618
29. 929.830 15,358.724 ↓ 112.7 4,319,021 1

Materialize (cost=427.23..1,111.48 rows=38,309 width=28) (actual time=14,373.346..15,358.724 rows=4,319,021 loops=1)

30. 35.850 14,428.894 ↓ 1.8 69,146 1

Merge Left Join (cost=427.23..1,015.71 rows=38,309 width=28) (actual time=14,373.340..14,428.894 rows=69,146 loops=1)

  • Merge Cond: (last_events_persons.person_id = next_events_persons.person_id)
31. 38.531 8,122.394 ↓ 25.0 69,146 1

Sort (cost=213.62..220.54 rows=2,768 width=16) (actual time=8,103.079..8,122.394 rows=69,146 loops=1)

  • Sort Key: last_events_persons.person_id
  • Sort Method: quicksort Memory: 8475kB
32. 8,083.863 8,083.863 ↓ 25.0 69,146 1

CTE Scan on last_events_persons (cost=0.00..55.36 rows=2,768 width=16) (actual time=6,987.860..8,083.863 rows=69,146 loops=1)

33. 0.819 6,270.650 ↑ 2.5 1,101 1

Sort (cost=213.62..220.54 rows=2,768 width=16) (actual time=6,270.254..6,270.650 rows=1,101 loops=1)

  • Sort Key: next_events_persons.person_id
  • Sort Method: quicksort Memory: 135kB
34. 6,269.831 6,269.831 ↑ 2.5 1,101 1

CTE Scan on next_events_persons (cost=0.00..55.36 rows=2,768 width=16) (actual time=5,104.207..6,269.831 rows=1,101 loops=1)