explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6QXO : v8

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

Unique (cost=1,046,985.75..1,076,784.44 rows=2,768 width=32) (actual time=18,088.548..28,691.605 rows=71,587 loops=1)

2.          

CTE hidden_external_event_ids

3. 2.695 8.372 ↑ 12.7 1,758 1

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

4. 0.715 0.715 ↑ 1.0 2,481 1

Seq Scan on hidden_externals (cost=0.00..40.81 rows=2,481 width=8) (actual time=0.008..0.715 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_3 (cost=0.43..36.12 rows=54 width=12) (actual time=0.002..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. 3,100.700 27,783.252 ↓ 2.2 4,353,160 1

Merge Left Join (cost=955,986.10..980,875.61 rows=1,963,671 width=32) (actual time=18,088.546..27,783.252 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))
7. 3,274.455 17,327.729 ↓ 2.2 4,353,160 1

Merge Left Join (cost=636,522.40..651,421.75 rows=1,963,671 width=20) (actual time=11,621.096..17,327.729 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))
8. 3,460.318 6,865.991 ↓ 2.2 4,353,160 1

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

  • Sort Key: events_persons.person_id, events_persons.organization_id
  • Sort Method: external merge Disk: 76552kB
9. 2,354.585 3,405.673 ↓ 2.2 4,353,160 1

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

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

11. 0.369 1.279 ↑ 1.0 1,329 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 63kB
12. 0.910 0.910 ↑ 1.0 1,329 1

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

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

Sort (cost=310,945.95..310,952.87 rows=2,768 width=20) (actual time=6,246.292..7,187.283 rows=4,319,021 loops=1)

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

Subquery Scan on last_events_persons (cost=305,489.77..310,787.70 rows=2,768 width=20) (actual time=4,997.857..6,225.506 rows=69,146 loops=1)

15. 479.402 6,194.917 ↓ 25.0 69,146 1

Unique (cost=305,489.77..310,760.02 rows=2,768 width=20) (actual time=4,997.854..6,194.917 rows=69,146 loops=1)

16. 2,481.567 5,715.515 ↓ 1.9 2,034,370 1

Sort (cost=305,489.77..308,124.89 rows=1,054,050 width=20) (actual time=4,997.852..5,715.515 rows=2,034,370 loops=1)

  • Sort Key: events_persons_1.person_id, events_persons_1.start_time DESC
  • Sort Method: external merge Disk: 67608kB
17. 974.183 3,233.948 ↓ 1.9 2,034,370 1

Merge Anti Join (cost=2,050.96..200,045.18 rows=1,054,050 width=20) (actual time=10.152..3,233.948 rows=2,034,370 loops=1)

  • Merge Cond: (events_persons_1.event_id = hidden_external_event_ids.event_id)
18. 2,249.037 2,249.037 ↑ 1.0 2,081,283 1

Index Scan using events_persons_event_id_person_id_key on events_persons events_persons_1 (cost=0.43..186,585.82 rows=2,108,100 width=20) (actual time=0.031..2,249.037 rows=2,081,283 loops=1)

  • Filter: (start_time <= now())
  • Rows Removed by Filter: 2273074
19. 1.346 10.728 ↑ 12.7 1,758 1

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

  • Sort Key: hidden_external_event_ids.event_id
  • Sort Method: quicksort Memory: 131kB
20. 9.382 9.382 ↑ 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..9.382 rows=1,758 loops=1)

21. 887.825 7,354.823 ↓ 1,450.6 4,015,294 1

Sort (cost=319,463.70..319,470.62 rows=2,768 width=20) (actual time=6,467.446..7,354.823 rows=4,015,294 loops=1)

  • Sort Key: next_events_persons.person_id, next_events_persons.organization_id
  • Sort Method: quicksort Memory: 135kB
22. 0.575 6,466.998 ↑ 2.5 1,101 1

Subquery Scan on next_events_persons (cost=313,662.12..319,305.44 rows=2,768 width=20) (actual time=5,173.546..6,466.998 rows=1,101 loops=1)

23. 503.605 6,466.423 ↑ 2.5 1,101 1

Unique (cost=313,662.12..319,277.76 rows=2,768 width=20) (actual time=5,173.544..6,466.423 rows=1,101 loops=1)

24. 2,629.654 5,962.818 ↓ 2.0 2,248,893 1

Sort (cost=313,662.12..316,469.94 rows=1,123,128 width=20) (actual time=5,173.542..5,962.818 rows=2,248,893 loops=1)

  • Sort Key: events_persons_2.person_id, events_persons_2.start_time
  • Sort Method: external merge Disk: 74736kB
25. 1,066.330 3,333.164 ↓ 2.0 2,248,893 1

Merge Anti Join (cost=2,050.96..200,792.86 rows=1,123,128 width=20) (actual time=1.454..3,333.164 rows=2,248,893 loops=1)

  • Merge Cond: (events_persons_2.event_id = hidden_external_event_ids_1.event_id)
26. 2,265.273 2,265.273 ↓ 1.0 2,273,074 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,246,255 width=20) (actual time=0.485..2,265.273 rows=2,273,074 loops=1)

  • Filter: (start_time > now())
  • Rows Removed by Filter: 2081283
27. 1.150 1.561 ↑ 12.7 1,757 1

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

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