explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KC0W

Settings
# exclusive inclusive rows x rows loops node
1. 912.589 28,814.803 ↓ 25.9 71,587 1

Unique (cost=1,047,041.16..1,076,839.86 rows=2,768 width=32) (actual time=18,129.807..28,814.803 rows=71,587 loops=1)

2.          

CTE hidden_external_event_ids

3. 1.822 7.404 ↑ 12.7 1,758 1

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

4. 0.620 0.620 ↑ 1.0 2,481 1

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

5. 4.962 4.962 ↑ 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.001..0.002 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. 475.712 6,131.157 ↓ 25.0 69,146 1

Unique (cost=305,476.07..310,745.74 rows=2,768 width=20) (actual time=4,956.499..6,131.157 rows=69,146 loops=1)

8. 2,439.430 5,655.445 ↓ 1.9 2,034,352 1

Sort (cost=305,476.07..308,110.90 rows=1,053,934 width=20) (actual time=4,956.496..5,655.445 rows=2,034,352 loops=1)

  • Sort Key: events_persons_2.person_id, events_persons_2.start_time DESC
  • Sort Method: external merge Disk: 67608kB
9. 983.697 3,216.015 ↓ 1.9 2,034,352 1

Merge Anti Join (cost=2,050.96..200,043.92 rows=1,053,934 width=20) (actual time=9.001..3,216.015 rows=2,034,352 loops=1)

  • Merge Cond: (events_persons_2.event_id = hidden_external_event_ids.event_id)
10. 2,222.700 2,222.700 ↑ 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,868 width=20) (actual time=0.030..2,222.700 rows=2,081,265 loops=1)

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

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

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

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

13.          

CTE next_events_persons

14. 504.663 6,488.908 ↑ 2.5 1,101 1

Unique (cost=313,675.87..319,292.09 rows=2,768 width=20) (actual time=5,186.136..6,488.908 rows=1,101 loops=1)

15. 2,635.201 5,984.245 ↓ 2.0 2,248,911 1

Sort (cost=313,675.87..316,483.98 rows=1,123,244 width=20) (actual time=5,186.133..5,984.245 rows=2,248,911 loops=1)

  • Sort Key: events_persons_3.person_id, events_persons_3.start_time
  • Sort Method: external merge Disk: 74736kB
16. 1,066.383 3,349.044 ↓ 2.0 2,248,911 1

Merge Anti Join (cost=2,050.96..200,794.12 rows=1,123,244 width=20) (actual time=1.463..3,349.044 rows=2,248,911 loops=1)

  • Merge Cond: (events_persons_3.event_id = hidden_external_event_ids_1.event_id)
17. 2,281.045 2,281.045 ↓ 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,487 width=20) (actual time=0.495..2,281.045 rows=2,273,092 loops=1)

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

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

  • Sort Key: hidden_external_event_ids_1.event_id
  • Sort Method: quicksort Memory: 131kB
19. 0.429 0.429 ↑ 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.429 rows=1,758 loops=1)

20. 3,131.098 27,902.214 ↓ 2.2 4,353,160 1

Merge Left Join (cost=326,003.68..350,893.20 rows=1,963,671 width=32) (actual time=18,129.806..27,902.214 rows=4,353,160 loops=1)

  • Merge Cond: ((events_persons.person_id = next_events_persons.person_id) AND (events_persons.organization_id = next_events_persons.organization_id))
21. 3,311.477 17,389.921 ↓ 2.2 4,353,160 1

Merge Left Join (cost=325,790.06..340,689.41 rows=1,963,671 width=20) (actual time=11,639.666..17,389.921 rows=4,353,160 loops=1)

  • Merge Cond: ((events_persons.person_id = last_events_persons.person_id) AND (events_persons.organization_id = last_events_persons.organization_id))
22. 3,503.297 6,956.395 ↓ 2.2 4,353,160 1

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

  • Sort Key: events_persons.person_id, events_persons.organization_id
  • Sort Method: external merge Disk: 76552kB
23. 2,391.066 3,453.098 ↓ 2.2 4,353,160 1

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

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

25. 0.391 1.284 ↑ 1.0 1,329 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 63kB
26. 0.893 0.893 ↑ 1.0 1,329 1

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

  • Filter: (type = ANY ('{0,1}'::integer[]))
  • Rows Removed by Filter: 1618
27. 956.198 7,122.049 ↓ 1,560.3 4,319,021 1

Sort (cost=213.62..220.54 rows=2,768 width=20) (actual time=6,186.531..7,122.049 rows=4,319,021 loops=1)

  • Sort Key: last_events_persons.person_id, last_events_persons.organization_id
  • Sort Method: quicksort Memory: 8475kB
28. 6,165.851 6,165.851 ↓ 25.0 69,146 1

CTE Scan on last_events_persons (cost=0.00..55.36 rows=2,768 width=20) (actual time=4,956.503..6,165.851 rows=69,146 loops=1)

29. 891.545 7,381.195 ↓ 1,450.6 4,015,294 1

Sort (cost=213.62..220.54 rows=2,768 width=20) (actual time=6,490.135..7,381.195 rows=4,015,294 loops=1)

  • Sort Key: next_events_persons.person_id, next_events_persons.organization_id
  • Sort Method: quicksort Memory: 135kB
30. 6,489.650 6,489.650 ↑ 2.5 1,101 1

CTE Scan on next_events_persons (cost=0.00..55.36 rows=2,768 width=20) (actual time=5,186.140..6,489.650 rows=1,101 loops=1)