explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PoI

Settings
# exclusive inclusive rows x rows loops node
1. 908.688 24,026.165 ↓ 25.9 71,587 1

Unique (cost=1,047,041.05..1,533,047.72 rows=2,768 width=32) (actual time=17,663.152..24,026.165 rows=71,587 loops=1)

2.          

CTE hidden_external_event_ids

3. 1.910 57.206 ↑ 12.7 1,758 1

Nested Loop (cost=0.43..90,999.65 rows=22,239 width=4) (actual time=1.028..57.206 rows=1,758 loops=1)

4. 0.714 0.714 ↑ 1.0 2,481 1

Seq Scan on hidden_externals (cost=0.00..40.81 rows=2,481 width=8) (actual time=0.010..0.714 rows=2,481 loops=1)

5. 54.582 54.582 ↑ 54.0 1 2,481

Index Only Scan using events_persons_org_id_person_id_calendar_id_event_id_key on events_persons events_persons_1 (cost=0.43..36.12 rows=54 width=12) (actual time=0.020..0.022 rows=1 loops=2,481)

  • Index Cond: ((organization_id = hidden_externals.organization_id) AND (person_id = hidden_externals.external_id))
  • Heap Fetches: 1758
6.          

CTE last_events_persons

7. 461.765 5,930.830 ↓ 25.0 69,146 1

Unique (cost=305,475.61..310,745.26 rows=2,768 width=16) (actual time=4,868.199..5,930.830 rows=69,146 loops=1)

8. 2,247.764 5,469.065 ↓ 1.9 2,034,352 1

Sort (cost=305,475.61..308,110.43 rows=1,053,930 width=16) (actual time=4,868.198..5,469.065 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
9. 925.162 3,221.301 ↓ 1.9 2,034,352 1

Merge Anti Join (cost=2,050.96..200,043.89 rows=1,053,930 width=16) (actual time=59.047..3,221.301 rows=2,034,352 loops=1)

  • Merge Cond: (events_persons_2.event_id = hidden_external_event_ids.event_id)
10. 2,236.524 2,236.524 ↑ 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,861 width=16) (actual time=0.031..2,236.524 rows=2,081,265 loops=1)

  • Filter: (start_time <= now())
  • Rows Removed by Filter: 2273092
11. 1.285 59.615 ↑ 12.7 1,758 1

Sort (cost=2,050.53..2,106.12 rows=22,239 width=4) (actual time=59.013..59.615 rows=1,758 loops=1)

  • Sort Key: hidden_external_event_ids.event_id
  • Sort Method: quicksort Memory: 131kB
12. 58.330 58.330 ↑ 12.7 1,758 1

CTE Scan on hidden_external_event_ids (cost=0.00..444.78 rows=22,239 width=4) (actual time=1.031..58.330 rows=1,758 loops=1)

13.          

CTE next_events_persons

14. 501.433 6,311.278 ↑ 2.5 1,101 1

Unique (cost=313,676.23..319,292.46 rows=2,768 width=16) (actual time=5,127.338..6,311.278 rows=1,101 loops=1)

15. 2,471.982 5,809.845 ↓ 2.0 2,248,911 1

Sort (cost=313,676.23..316,484.34 rows=1,123,247 width=16) (actual time=5,127.337..5,809.845 rows=2,248,911 loops=1)

  • Sort Key: events_persons_3.person_id, events_persons_3.start_time
  • Sort Method: external merge Disk: 57144kB
16. 1,030.854 3,337.863 ↓ 2.0 2,248,911 1

Merge Anti Join (cost=2,050.96..200,794.15 rows=1,123,247 width=16) (actual time=1.469..3,337.863 rows=2,248,911 loops=1)

  • Merge Cond: (events_persons_3.event_id = hidden_external_event_ids_1.event_id)
17. 2,305.352 2,305.352 ↓ 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,494 width=16) (actual time=0.489..2,305.352 rows=2,273,092 loops=1)

  • Filter: (start_time > now())
  • Rows Removed by Filter: 2081265
18. 1.242 1.657 ↑ 12.7 1,757 1

Sort (cost=2,050.53..2,106.12 rows=22,239 width=4) (actual time=0.972..1.657 rows=1,757 loops=1)

  • Sort Key: hidden_external_event_ids_1.event_id
  • Sort Method: quicksort Memory: 131kB
19. 0.415 0.415 ↑ 12.7 1,758 1

CTE Scan on hidden_external_event_ids hidden_external_event_ids_1 (cost=0.00..444.78 rows=22,239 width=4) (actual time=0.001..0.415 rows=1,758 loops=1)

20. 3,066.921 23,117.477 ↑ 6.2 4,353,160 1

Merge Left Join (cost=326,003.68..744,067.34 rows=27,177,204 width=32) (actual time=17,663.150..23,117.477 rows=4,353,160 loops=1)

  • Merge Cond: (events_persons.person_id = last_events_persons.person_id)
21. 3,233.728 6,782.797 ↓ 2.2 4,353,160 1

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

  • Sort Key: events_persons.person_id
  • Sort Method: external merge Disk: 76552kB
22. 2,437.624 3,549.069 ↓ 2.2 4,353,160 1

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

  • Hash Cond: (events_persons.organization_id = organizations.id)
23. 1,110.141 1,110.141 ↓ 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,110.141 rows=4,354,357 loops=1)

24. 0.374 1.304 ↑ 1.0 1,329 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 63kB
25. 0.930 0.930 ↑ 1.0 1,329 1

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

  • Filter: (type = ANY ('{0,1}'::integer[]))
  • Rows Removed by Filter: 1618
26. 918.330 13,267.759 ↓ 112.7 4,319,021 1

Materialize (cost=427.23..1,111.48 rows=38,309 width=28) (actual time=12,297.891..13,267.759 rows=4,319,021 loops=1)

27. 32.964 12,349.429 ↓ 1.8 69,146 1

Merge Left Join (cost=427.23..1,015.71 rows=38,309 width=28) (actual time=12,297.886..12,349.429 rows=69,146 loops=1)

  • Merge Cond: (last_events_persons.person_id = next_events_persons.person_id)
28. 37.056 6,002.443 ↓ 25.0 69,146 1

Sort (cost=213.62..220.54 rows=2,768 width=16) (actual time=5,984.244..6,002.443 rows=69,146 loops=1)

  • Sort Key: last_events_persons.person_id
  • Sort Method: quicksort Memory: 8475kB
29. 5,965.387 5,965.387 ↓ 25.0 69,146 1

CTE Scan on last_events_persons (cost=0.00..55.36 rows=2,768 width=16) (actual time=4,868.203..5,965.387 rows=69,146 loops=1)

30. 2.004 6,314.022 ↑ 2.5 1,101 1

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

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

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