explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1pjj

Settings
# exclusive inclusive rows x rows loops node
1. 54.287 13,175.822 ↓ 27.6 68,971 1

Unique (cost=730,431.42..730,443.90 rows=2,496 width=32) (actual time=13,084.874..13,175.822 rows=68,971 loops=1)

2.          

CTE hidden_external_event_ids

3. 1.794 7.402 ↑ 12.7 1,758 1

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

4. 0.646 0.646 ↑ 1.0 2,481 1

Seq Scan on hidden_externals (cost=0.00..40.81 rows=2,481 width=8) (actual time=0.005..0.646 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_2 (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. 77.156 13,121.535 ↓ 27.6 68,971 1

Sort (cost=639,431.77..639,438.01 rows=2,496 width=32) (actual time=13,084.871..13,121.535 rows=68,971 loops=1)

  • Sort Key: (COALESCE(last_events_persons.person_id, events_persons.person_id))
  • Sort Method: quicksort Memory: 8461kB
7. 80.294 13,044.379 ↓ 27.6 68,971 1

Nested Loop (cost=624,492.60..639,290.93 rows=2,496 width=32) (actual time=11,354.249..13,044.379 rows=68,971 loops=1)

8. 32.589 12,687.481 ↓ 25.0 69,151 1

Hash Full Join (cost=624,491.34..630,155.43 rows=2,768 width=40) (actual time=11,354.214..12,687.481 rows=69,151 loops=1)

  • Hash Cond: ((events_persons.person_id = last_events_persons.person_id) AND (events_persons.organization_id = last_events_persons.organization_id))
9. 503.854 6,462.225 ↑ 2.5 1,101 1

Unique (cost=313,659.05..319,274.56 rows=2,768 width=20) (actual time=5,161.523..6,462.225 rows=1,101 loops=1)

10. 2,635.186 5,958.371 ↓ 2.0 2,248,893 1

Sort (cost=313,659.05..316,466.80 rows=1,123,102 width=20) (actual time=5,161.521..5,958.371 rows=2,248,893 loops=1)

  • Sort Key: events_persons.person_id, events_persons.start_time
  • Sort Method: external merge Disk: 74736kB
11. 1,062.538 3,323.185 ↓ 2.0 2,248,893 1

Merge Anti Join (cost=2,050.96..200,792.59 rows=1,123,102 width=20) (actual time=1.448..3,323.185 rows=2,248,893 loops=1)

  • Merge Cond: (events_persons.event_id = hidden_external_event_ids.event_id)
12. 2,259.055 2,259.055 ↓ 1.0 2,273,074 1

Index Scan using events_persons_event_id_person_id_key on events_persons (cost=0.43..186,585.82 rows=2,246,205 width=20) (actual time=0.486..2,259.055 rows=2,273,074 loops=1)

  • Filter: (start_time > now())
  • Rows Removed by Filter: 2081283
13. 1.189 1.592 ↑ 12.7 1,757 1

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

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

15. 23.720 6,192.667 ↓ 25.0 69,146 1

Hash (cost=310,790.78..310,790.78 rows=2,768 width=20) (actual time=6,192.667..6,192.667 rows=69,146 loops=1)

  • Buckets: 131072 (originally 4096) Batches: 1 (originally 1) Memory Usage: 4806kB
16. 29.724 6,168.947 ↓ 25.0 69,146 1

Subquery Scan on last_events_persons (cost=305,492.72..310,790.78 rows=2,768 width=20) (actual time=4,975.954..6,168.947 rows=69,146 loops=1)

17. 469.976 6,139.223 ↓ 25.0 69,146 1

Unique (cost=305,492.72..310,763.10 rows=2,768 width=20) (actual time=4,975.952..6,139.223 rows=69,146 loops=1)

18. 2,423.657 5,669.247 ↓ 1.9 2,034,370 1

Sort (cost=305,492.72..308,127.91 rows=1,054,075 width=20) (actual time=4,975.949..5,669.247 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
19. 983.058 3,245.590 ↓ 1.9 2,034,370 1

Merge Anti Join (cost=2,050.96..200,045.45 rows=1,054,075 width=20) (actual time=8.953..3,245.590 rows=2,034,370 loops=1)

  • Merge Cond: (events_persons_1.event_id = hidden_external_event_ids_1.event_id)
20. 2,252.980 2,252.980 ↑ 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,150 width=20) (actual time=0.016..2,252.980 rows=2,081,283 loops=1)

  • Filter: (start_time <= now())
  • Rows Removed by Filter: 2273074
21. 1.228 9.552 ↑ 12.7 1,758 1

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

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

23. 138.302 276.604 ↑ 1.0 1 69,151

Bitmap Heap Scan on organizations (cost=1.26..3.29 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=69,151)

  • Recheck Cond: ((id = events_persons.organization_id) OR (id = last_events_persons.organization_id))
  • Filter: (type = ANY ('{0,1}'::integer[]))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=69151
24. 69.151 138.302 ↓ 0.0 0 69,151

BitmapOr (cost=1.26..1.26 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=69,151)

25. 0.000 0.000 ↓ 0.0 0 69,151

Bitmap Index Scan on organizations_pkey (cost=0.00..0.30 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=69,151)

  • Index Cond: (id = events_persons.organization_id)
26. 69.151 69.151 ↑ 1.0 1 69,151

Bitmap Index Scan on organizations_pkey (cost=0.00..0.30 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=69,151)

  • Index Cond: (id = last_events_persons.organization_id)