explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ams2

Settings
# exclusive inclusive rows x rows loops node
1. 33.505 13,373.722 ↓ 28.6 71,338 1

Unique (cost=977,753.00..977,765.48 rows=2,496 width=40) (actual time=13,317.820..13,373.722 rows=71,338 loops=1)

2.          

CTE hidden_external_events

3. 1.816 7.423 ↑ 12.7 1,758 1

Nested Loop (cost=0.43..90,999.65 rows=22,239 width=12) (actual time=0.024..7.423 rows=1,758 loops=1)

4. 0.645 0.645 ↑ 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.645 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=16) (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. 58.429 13,340.217 ↓ 28.6 71,338 1

Sort (cost=886,753.34..886,759.58 rows=2,496 width=40) (actual time=13,317.819..13,340.217 rows=71,338 loops=1)

  • Sort Key: (COALESCE(last_events_persons.person_id, events_persons.person_id))
  • Sort Method: quicksort Memory: 8646kB
7. 88.852 13,281.788 ↓ 28.6 71,338 1

Nested Loop (cost=867,602.64..886,612.50 rows=2,496 width=40) (actual time=11,629.551..13,281.788 rows=71,338 loops=1)

8. 28.568 12,978.358 ↓ 25.8 71,526 1

Hash Full Join (cost=867,601.38..877,477.01 rows=2,768 width=48) (actual time=11,629.516..12,978.358 rows=71,526 loops=1)

  • Hash Cond: ((events_persons.person_id = last_events_persons.person_id) AND (events_persons.organization_id = last_events_persons.organization_id))
9. 519.019 6,605.133 ↑ 2.4 1,137 1

Unique (cost=437,213.31..447,040.36 rows=2,768 width=24) (actual time=5,284.834..6,605.133 rows=1,137 loops=1)

10. 2,712.151 6,086.114 ↓ 1.2 2,269,559 1

Sort (cost=437,213.31..442,126.83 rows=1,965,409 width=24) (actual time=5,284.831..6,086.114 rows=2,269,559 loops=1)

  • Sort Key: events_persons.person_id, events_persons.start_time
  • Sort Method: external merge Disk: 75424kB
11. 1,091.209 3,373.963 ↓ 1.2 2,269,559 1

Merge Anti Join (cost=2,050.96..202,972.70 rows=1,965,409 width=24) (actual time=1.463..3,373.963 rows=2,269,559 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: 20666
12. 2,276.504 2,276.504 ↓ 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,182 width=24) (actual time=0.487..2,276.504 rows=2,273,074 loops=1)

  • Filter: (start_time > now())
  • Rows Removed by Filter: 2081283
13. 5.825 6.250 ↑ 1.0 21,674 1

Sort (cost=2,050.53..2,106.12 rows=22,239 width=12) (actual time=0.968..6.250 rows=21,674 loops=1)

  • Sort Key: hidden_external_events.event_id
  • Sort Method: quicksort Memory: 131kB
14. 0.425 0.425 ↑ 12.7 1,758 1

CTE Scan on hidden_external_events (cost=0.00..444.78 rows=22,239 width=12) (actual time=0.002..0.425 rows=1,758 loops=1)

15. 25.339 6,344.657 ↓ 25.8 71,517 1

Hash (cost=430,346.55..430,346.55 rows=2,768 width=24) (actual time=6,344.657..6,344.657 rows=71,517 loops=1)

  • Buckets: 131072 (originally 4096) Batches: 1 (originally 1) Memory Usage: 4936kB
16. 31.098 6,319.318 ↓ 25.8 71,517 1

Subquery Scan on last_events_persons (cost=421,095.62..430,346.55 rows=2,768 width=24) (actual time=5,075.865..6,319.318 rows=71,517 loops=1)

17. 492.181 6,288.220 ↓ 25.8 71,517 1

Unique (cost=421,095.62..430,318.87 rows=2,768 width=24) (actual time=5,075.863..6,288.220 rows=71,517 loops=1)

18. 2,520.168 5,796.039 ↓ 1.1 2,074,688 1

Sort (cost=421,095.62..425,707.24 rows=1,844,651 width=24) (actual time=5,075.860..5,796.039 rows=2,074,688 loops=1)

  • Sort Key: events_persons_1.person_id, events_persons_1.start_time DESC
  • Sort Method: external merge Disk: 68944kB
19. 1,022.797 3,275.871 ↓ 1.1 2,074,688 1

Merge Anti Join (cost=2,050.96..202,091.83 rows=1,844,651 width=24) (actual time=9.066..3,275.871 rows=2,074,688 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: 44897
20. 2,233.033 2,233.033 ↑ 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,173 width=24) (actual time=0.016..2,233.033 rows=2,081,283 loops=1)

  • Filter: (start_time <= now())
  • Rows Removed by Filter: 2273074
21. 11.657 20.041 ↓ 2.1 45,646 1

Sort (cost=2,050.53..2,106.12 rows=22,239 width=12) (actual time=9.047..20.041 rows=45,646 loops=1)

  • Sort Key: hidden_external_events_1.event_id
  • Sort Method: quicksort Memory: 131kB
22. 8.384 8.384 ↑ 12.7 1,758 1

CTE Scan on hidden_external_events hidden_external_events_1 (cost=0.00..444.78 rows=22,239 width=12) (actual time=0.027..8.384 rows=1,758 loops=1)

23. 71.526 214.578 ↑ 1.0 1 71,526

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

  • 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=71526
24. 71.526 143.052 ↓ 0.0 0 71,526

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

25. 0.000 0.000 ↓ 0.0 0 71,526

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

  • Index Cond: (id = events_persons.organization_id)
26. 71.526 71.526 ↑ 1.0 1 71,526

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

  • Index Cond: (id = last_events_persons.organization_id)