explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Aa8Y : prod v8 write

Settings
# exclusive inclusive rows x rows loops node
1. 4,983.657 327,689.859 ↑ 340.6 2,955,674 1

Nested Loop (cost=25,497,273.28..3,725,820,978.46 rows=1,006,625,008 width=40) (actual time=268,551.204..327,689.859 rows=2,955,674 loops=1)

2.          

CTE hidden_external_events

3. 102.656 259.095 ↓ 10.9 244,789 1

Nested Loop (cost=0.57..7,296.41 rows=22,432 width=12) (actual time=0.029..259.095 rows=244,789 loops=1)

4. 0.709 0.709 ↓ 1.0 2,685 1

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

5. 155.730 155.730 ↓ 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.013..0.058 rows=91 loops=2,685)

  • Index Cond: ((organization_id = hidden_externals.organization_id) AND (person_id = hidden_externals.external_id))
  • Heap Fetches: 85039
6. 3,683.872 313,800.462 ↑ 370.7 2,968,580 1

Hash Full Join (cost=25,489,975.55..26,248,410.90 rows=1,100,402,600 width=48) (actual time=268,551.180..313,800.462 rows=2,968,580 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,498.733 201,822.494 ↑ 3.1 3,067,063 1

Subquery Scan on last_events_persons (cost=15,055,489.61..15,597,892.43 rows=9,546,857 width=24) (actual time=160,014.243..201,822.494 rows=3,067,063 loops=1)

8. 17,685.561 200,323.761 ↑ 3.1 3,067,063 1

Unique (cost=15,055,489.61..15,502,423.86 rows=9,546,857 width=24) (actual time=160,014.241..200,323.761 rows=3,067,063 loops=1)

9. 93,993.818 182,638.200 ↓ 1.1 66,351,689 1

Sort (cost=15,055,489.61..15,204,467.69 rows=59,591,234 width=24) (actual time=160,014.239..182,638.200 rows=66,351,689 loops=1)

  • Sort Key: events_persons.organization_id, events_persons.person_id, events_persons.start_time DESC
  • Sort Method: external merge Disk: 2207424kB
10. 33,275.109 88,644.382 ↓ 1.1 66,351,689 1

Merge Anti Join (cost=2,070.29..6,486,779.83 rows=59,591,234 width=24) (actual time=151.419..88,644.382 rows=66,351,689 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: 98881
11. 55,131.832 55,131.832 ↑ 1.0 67,292,255 1

Index Scan using events_persons_event_id_person_id_key on events_persons (cost=0.57..6,224,304.14 rows=68,104,268 width=24) (actual time=0.016..55,131.832 rows=67,292,255 loops=1)

  • Filter: (start_time <= now())
  • Rows Removed by Filter: 28347279
12. 177.863 237.441 ↓ 15.1 338,188 1

Sort (cost=2,069.72..2,125.80 rows=22,432 width=12) (actual time=151.397..237.441 rows=338,188 loops=1)

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

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

14. 76.202 105,268.937 ↑ 58.5 157,593 1

Hash (cost=9,888,268.37..9,888,268.37 rows=9,221,067 width=24) (actual time=105,268.937..105,268.937 rows=157,593 loops=1)

  • Buckets: 8388608 Batches: 2 Memory Usage: 70171kB
15. 72.570 105,192.735 ↑ 58.5 157,593 1

Subquery Scan on next_events_persons (cost=9,616,477.11..9,888,268.37 rows=9,221,067 width=24) (actual time=88,782.184..105,192.735 rows=157,593 loops=1)

16. 7,102.417 105,120.165 ↑ 58.5 157,593 1

Unique (cost=9,616,477.11..9,796,057.70 rows=9,221,067 width=24) (actual time=88,782.182..105,120.165 rows=157,593 loops=1)

17. 37,301.239 98,017.748 ↓ 1.2 28,184,797 1

Sort (cost=9,616,477.11..9,676,337.31 rows=23,944,078 width=24) (actual time=88,782.179..98,017.748 rows=28,184,797 loops=1)

  • Sort Key: events_persons_1.organization_id, events_persons_1.person_id, events_persons_1.start_time
  • Sort Method: external merge Disk: 937672kB
18. 13,763.353 60,716.509 ↓ 1.2 28,184,797 1

Merge Anti Join (cost=2,070.29..6,331,006.37 rows=23,944,078 width=24) (actual time=509.893..60,716.509 rows=28,184,797 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. 46,389.471 46,389.471 ↓ 1.0 28,347,238 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,364,660 width=24) (actual time=16.433..46,389.471 rows=28,347,238 loops=1)

  • Filter: (start_time > now())
  • Rows Removed by Filter: 67292296
20. 166.358 563.685 ↓ 13.5 302,821 1

Sort (cost=2,069.72..2,125.80 rows=22,432 width=12) (actual time=493.454..563.685 rows=302,821 loops=1)

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

CTE Scan on hidden_external_events hidden_external_events_1 (cost=0.00..448.64 rows=22,432 width=12) (actual time=0.031..397.327 rows=244,789 loops=1)

22.          

SubPlan (forHash Full Join)

23. 2,611.931 3,025.159 ↑ 1.0 217,427 1

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

  • Recheck Cond: (inferred_type = 1)
  • Heap Blocks: exact=158093
24. 413.228 413.228 ↑ 1.0 217,460 1

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

25. 2,968.580 8,905.740 ↑ 1.0 1 2,968,580

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,580)

  • 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=2968580
26. 2,968.580 5,937.160 ↓ 0.0 0 2,968,580

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

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

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,580)

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

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,580)

  • Index Cond: (id = last_events_persons.organization_id)