explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LLfq

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

Unique (cost=1,046,985.79..1,076,784.49 rows=2,768 width=32) (actual time=18,265.253..28,944.678 rows=71,587 loops=1)

2.          

CTE hidden_external_event_ids

3. 1.833 7.404 ↑ 12.7 1,758 1

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

4. 0.609 0.609 ↑ 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.609 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.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. 3,160.751 28,036.178 ↓ 2.2 4,353,160 1

Merge Left Join (cost=955,986.14..980,875.66 rows=1,963,671 width=32) (actual time=18,265.251..28,036.178 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,268.031 17,399.314 ↓ 2.2 4,353,160 1

Merge Left Join (cost=636,510.59..651,409.94 rows=1,963,671 width=20) (actual time=11,679.846..17,399.314 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,512.531 6,953.682 ↓ 2.2 4,353,160 1

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

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

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

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

11. 0.414 1.339 ↑ 1.0 1,329 1

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

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

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

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

Sort (cost=310,934.14..310,941.06 rows=2,768 width=20) (actual time=6,239.962..7,177.601 rows=4,319,021 loops=1)

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

Subquery Scan on last_events_persons (cost=305,478.43..310,775.88 rows=2,768 width=20) (actual time=4,990.073..6,220.399 rows=69,146 loops=1)

15. 477.766 6,191.444 ↓ 25.0 69,146 1

Unique (cost=305,478.43..310,748.20 rows=2,768 width=20) (actual time=4,990.071..6,191.444 rows=69,146 loops=1)

16. 2,440.329 5,713.678 ↓ 1.9 2,034,361 1

Sort (cost=305,478.43..308,113.32 rows=1,053,954 width=20) (actual time=4,990.069..5,713.678 rows=2,034,361 loops=1)

  • Sort Key: events_persons_1.person_id, events_persons_1.start_time DESC
  • Sort Method: external merge Disk: 67608kB
17. 999.799 3,273.349 ↓ 1.9 2,034,361 1

Merge Anti Join (cost=2,050.96..200,044.14 rows=1,053,954 width=20) (actual time=9.014..3,273.349 rows=2,034,361 loops=1)

  • Merge Cond: (events_persons_1.event_id = hidden_external_event_ids.event_id)
18. 2,263.956 2,263.956 ↑ 1.0 2,081,274 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,107,908 width=20) (actual time=0.030..2,263.956 rows=2,081,274 loops=1)

  • Filter: (start_time <= now())
  • Rows Removed by Filter: 2273083
19. 1.247 9.594 ↑ 12.7 1,758 1

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

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

21. 891.222 7,476.113 ↓ 1,450.6 4,015,294 1

Sort (cost=319,475.55..319,482.47 rows=2,768 width=20) (actual time=6,585.401..7,476.113 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.524 6,584.891 ↑ 2.5 1,101 1

Subquery Scan on next_events_persons (cost=313,673.50..319,317.30 rows=2,768 width=20) (actual time=5,226.895..6,584.891 rows=1,101 loops=1)

23. 530.074 6,584.367 ↑ 2.5 1,101 1

Unique (cost=313,673.50..319,289.62 rows=2,768 width=20) (actual time=5,226.892..6,584.367 rows=1,101 loops=1)

24. 2,671.921 6,054.293 ↓ 2.0 2,248,902 1

Sort (cost=313,673.50..316,481.56 rows=1,123,224 width=20) (actual time=5,226.891..6,054.293 rows=2,248,902 loops=1)

  • Sort Key: events_persons_2.person_id, events_persons_2.start_time
  • Sort Method: external merge Disk: 74736kB
25. 1,065.628 3,382.372 ↓ 2.0 2,248,902 1

Merge Anti Join (cost=2,050.96..200,793.90 rows=1,123,224 width=20) (actual time=1.486..3,382.372 rows=2,248,902 loops=1)

  • Merge Cond: (events_persons_2.event_id = hidden_external_event_ids_1.event_id)
26. 2,315.090 2,315.090 ↓ 1.0 2,273,083 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,447 width=20) (actual time=0.490..2,315.090 rows=2,273,083 loops=1)

  • Filter: (start_time > now())
  • Rows Removed by Filter: 2081274
27. 1.236 1.654 ↑ 12.7 1,757 1

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

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