explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wxmU

Settings
# exclusive inclusive rows x rows loops node
1. 4,843.474 488,099.665 ↑ 340.6 2,955,605 1

Nested Loop (cost=25,497,275.33..3,725,840,634.81 rows=1,006,630,358 width=40) (actual time=430,160.607..488,099.665 rows=2,955,605 loops=1)

2.          

CTE hidden_external_events

3. 101.881 228.789 ↓ 10.9 244,782 1

Nested Loop (cost=0.57..7,296.41 rows=22,432 width=12) (actual time=0.027..228.789 rows=244,782 loops=1)

4. 0.713 0.713 ↓ 1.0 2,685 1

Seq Scan on hidden_externals (cost=0.00..44.70 rows=2,670 width=8) (actual time=0.006..0.713 rows=2,685 loops=1)

5. 126.195 126.195 ↓ 18.2 91 2,685

Index Only Scan using events_persons_org_id_person_id_calendar_id_event_id_key on events_persons events_persons_2 (cost=0.57..2.67 rows=5 width=16) (actual time=0.004..0.047 rows=91 loops=2,685)

  • Index Cond: ((organization_id = hidden_externals.organization_id) AND (person_id = hidden_externals.external_id))
  • Heap Fetches: 88300
6. 3,608.361 474,350.658 ↑ 370.7 2,968,511 1

Hash Full Join (cost=25,489,977.60..26,248,406.24 rows=1,100,408,448 width=48) (actual time=430,160.577..474,350.658 rows=2,968,511 loops=1)

  • Hash Cond: ((last_events_persons.person_id = next_events_persons.person_id) AND (last_events_persons.organization_id = next_events_persons.organization_id))
  • Filter: (NOT (hashed SubPlan 2))
  • Rows Removed by Filter: 124130
7. 1,457.444 196,410.745 ↑ 3.1 3,067,054 1

Subquery Scan on last_events_persons (cost=15,055,351.15..15,597,747.26 rows=9,546,857 width=24) (actual time=155,596.058..196,410.745 rows=3,067,054 loops=1)

8. 17,291.758 194,953.301 ↑ 3.1 3,067,054 1

Unique (cost=15,055,351.15..15,502,278.69 rows=9,546,857 width=24) (actual time=155,596.056..194,953.301 rows=3,067,054 loops=1)

9. 90,954.451 177,661.543 ↓ 1.1 66,351,521 1

Sort (cost=15,055,351.15..15,204,327.00 rows=59,590,339 width=24) (actual time=155,596.054..177,661.543 rows=66,351,521 loops=1)

  • Sort Key: events_persons.organization_id, events_persons.person_id, events_persons.start_time DESC
  • Sort Method: external merge Disk: 2207416kB
10. 31,531.335 86,707.092 ↓ 1.1 66,351,521 1

Merge Anti Join (cost=2,070.29..6,486,775.91 rows=59,590,339 width=24) (actual time=152.106..86,707.092 rows=66,351,521 loops=1)

  • Merge Cond: (events_persons.event_id = hidden_external_events.event_id)
  • Join Filter: ((hidden_external_events.organization_id = events_persons.organization_id) AND (hidden_external_events.calendar_id = events_persons.calendar_id))
  • Rows Removed by Join Filter: 98880
11. 54,940.967 54,940.967 ↑ 1.0 67,292,087 1

Index Scan using events_persons_event_id_person_id_key on events_persons (cost=0.57..6,224,304.14 rows=68,103,245 width=24) (actual time=0.014..54,940.967 rows=67,292,087 loops=1)

  • Filter: (start_time <= now())
  • Rows Removed by Filter: 28341931
12. 175.347 234.790 ↓ 15.1 338,179 1

Sort (cost=2,069.72..2,125.80 rows=22,432 width=12) (actual time=152.087..234.790 rows=338,179 loops=1)

  • Sort Key: hidden_external_events.event_id
  • Sort Method: quicksort Memory: 17619kB
13. 59.443 59.443 ↓ 10.9 244,782 1

CTE Scan on hidden_external_events (cost=0.00..448.64 rows=22,432 width=12) (actual time=0.001..59.443 rows=244,782 loops=1)

14. 75.955 257,727.432 ↑ 58.6 157,456 1

Hash (cost=9,888,408.14..9,888,408.14 rows=9,221,116 width=24) (actual time=257,727.432..257,727.432 rows=157,456 loops=1)

  • Buckets: 8388608 Batches: 2 Memory Usage: 70166kB
15. 72.715 257,651.477 ↑ 58.6 157,456 1

Subquery Scan on next_events_persons (cost=9,616,609.68..9,888,408.14 rows=9,221,116 width=24) (actual time=241,410.736..257,651.477 rows=157,456 loops=1)

16. 6,964.853 257,578.762 ↑ 58.6 157,456 1

Unique (cost=9,616,609.68..9,796,196.98 rows=9,221,116 width=24) (actual time=241,410.734..257,578.762 rows=157,456 loops=1)

17. 37,708.193 250,613.909 ↓ 1.2 28,179,457 1

Sort (cost=9,616,609.68..9,676,472.11 rows=23,944,973 width=24) (actual time=241,410.732..250,613.909 rows=28,179,457 loops=1)

  • Sort Key: events_persons_1.organization_id, events_persons_1.person_id, events_persons_1.start_time
  • Sort Method: external merge Disk: 937496kB
18. 13,977.347 212,905.716 ↓ 1.2 28,179,457 1

Merge Anti Join (cost=2,070.29..6,331,010.29 rows=23,944,973 width=24) (actual time=482.498..212,905.716 rows=28,179,457 loops=1)

  • Merge Cond: (events_persons_1.event_id = hidden_external_events_1.event_id)
  • Join Filter: ((hidden_external_events_1.organization_id = events_persons_1.organization_id) AND (hidden_external_events_1.calendar_id = events_persons_1.calendar_id))
  • Rows Removed by Join Filter: 60419
19. 198,385.516 198,385.516 ↓ 1.0 28,341,890 1

Index Scan using events_persons_event_id_person_id_key on events_persons events_persons_1 (cost=0.57..6,224,304.14 rows=27,365,683 width=24) (actual time=16.650..198,385.516 rows=28,341,890 loops=1)

  • Filter: (start_time > now())
  • Rows Removed by Filter: 67292128
20. 175.179 542.853 ↓ 13.5 302,814 1

Sort (cost=2,069.72..2,125.80 rows=22,432 width=12) (actual time=465.842..542.853 rows=302,814 loops=1)

  • Sort Key: hidden_external_events_1.event_id
  • Sort Method: quicksort Memory: 17619kB
21. 367.674 367.674 ↓ 10.9 244,782 1

CTE Scan on hidden_external_events hidden_external_events_1 (cost=0.00..448.64 rows=22,432 width=12) (actual time=0.030..367.674 rows=244,782 loops=1)

22.          

SubPlan (forHash Full Join)

23. 15,842.808 16,604.120 ↑ 1.0 217,427 1

Bitmap Heap Scan on persons (cost=6,454.05..353,318.38 rows=221,280 width=4) (actual time=792.035..16,604.120 rows=217,427 loops=1)

  • Recheck Cond: (inferred_type = 1)
  • Heap Blocks: exact=158102
24. 761.312 761.312 ↑ 1.0 217,521 1

Bitmap Index Scan on persons_organization_id_inferred_internals_index (cost=0.00..6,398.73 rows=221,280 width=0) (actual time=761.312..761.312 rows=217,521 loops=1)

25. 2,968.511 8,905.533 ↑ 1.0 1 2,968,511

Bitmap Heap Scan on organizations (cost=1.32..3.35 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,968,511)

  • Recheck Cond: ((id = next_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=3153038
26. 2,968.511 5,937.022 ↓ 0.0 0 2,968,511

BitmapOr (cost=1.32..1.32 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=2,968,511)

27. 0.000 0.000 ↓ 0.0 0 2,968,511

Bitmap Index Scan on organizations_pkey (cost=0.00..0.29 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=2,968,511)

  • Index Cond: (id = next_events_persons.organization_id)
28. 2,968.511 2,968.511 ↑ 1.0 1 2,968,511

Bitmap Index Scan on organizations_pkey (cost=0.00..0.29 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=2,968,511)

  • Index Cond: (id = last_events_persons.organization_id)