explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8tf6

Settings
# exclusive inclusive rows x rows loops node
1. 100.534 14,506.788 ↑ 2.0 71,338 1

Nested Loop (cost=949,307.71..1,511,896.22 rows=145,268 width=40) (actual time=11,302.712..14,506.788 rows=71,338 loops=1)

2.          

CTE hidden_external_events

3. 1.932 7.455 ↑ 12.7 1,758 1

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

4. 0.561 0.561 ↑ 1.0 2,481 1

Seq Scan on hidden_externals (cost=0.00..40.81 rows=2,481 width=8) (actual time=0.006..0.561 rows=2,481 loops=1)

5. 4.962 4.962 ↑ 54.0 1 2,481

Index Scan using asdf 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))
6. 40.406 14,191.676 ↑ 2.3 71,526 1

Merge Full Join (cost=858,306.82..891,708.17 rows=161,090 width=48) (actual time=11,302.673..14,191.676 rows=71,526 loops=1)

  • Merge Cond: ((events_persons.organization_id = events_persons_1.organization_id) AND (events_persons.person_id = events_persons_1.person_id))
7. 546.575 6,880.979 ↑ 1.1 71,517 1

Unique (cost=421,103.12..434,938.48 rows=80,272 width=24) (actual time=5,507.185..6,880.979 rows=71,517 loops=1)

8. 3,089.509 6,334.404 ↓ 1.1 2,074,688 1

Sort (cost=421,103.12..425,714.91 rows=1,844,714 width=24) (actual time=5,507.183..6,334.404 rows=2,074,688 loops=1)

  • Sort Key: events_persons.organization_id, events_persons.person_id, events_persons.start_time DESC
  • Sort Method: external merge Disk: 68944kB
9. 1,002.452 3,244.895 ↓ 1.1 2,074,688 1

Merge Anti Join (cost=2,050.96..202,092.33 rows=1,844,714 width=24) (actual time=9.130..3,244.895 rows=2,074,688 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: 44887
10. 2,222.156 2,222.156 ↑ 1.0 2,081,283 1

Index Scan using events_persons_event_id_person_id_key on events_persons (cost=0.43..186,585.86 rows=2,108,244 width=24) (actual time=0.017..2,222.156 rows=2,081,283 loops=1)

  • Filter: (start_time <= now())
  • Rows Removed by Filter: 2273074
11. 11.818 20.287 ↓ 2.1 45,636 1

Sort (cost=2,050.53..2,106.12 rows=22,239 width=12) (actual time=9.112..20.287 rows=45,636 loops=1)

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

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

13. 0.706 7,270.291 ↑ 70.6 1,137 1

Materialize (cost=437,203.69..452,947.21 rows=80,272 width=24) (actual time=5,795.481..7,270.291 rows=1,137 loops=1)

14. 571.207 7,269.585 ↑ 70.6 1,137 1

Unique (cost=437,203.69..451,943.81 rows=80,272 width=24) (actual time=5,795.477..7,269.585 rows=1,137 loops=1)

15. 3,296.409 6,698.378 ↓ 1.2 2,269,559 1

Sort (cost=437,203.69..442,117.07 rows=1,965,349 width=24) (actual time=5,795.474..6,698.378 rows=2,269,559 loops=1)

  • Sort Key: events_persons_1.organization_id, events_persons_1.person_id, events_persons_1.start_time
  • Sort Method: external merge Disk: 75424kB
16. 1,106.278 3,401.969 ↓ 1.2 2,269,559 1

Merge Anti Join (cost=2,050.96..202,972.28 rows=1,965,349 width=24) (actual time=1.482..3,401.969 rows=2,269,559 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: 20666
17. 2,289.277 2,289.277 ↓ 1.0 2,273,074 1

Index Scan using events_persons_event_id_person_id_key on events_persons events_persons_1 (cost=0.43..186,585.86 rows=2,246,113 width=24) (actual time=0.484..2,289.277 rows=2,273,074 loops=1)

  • Filter: (start_time > now())
  • Rows Removed by Filter: 2081283
18. 5.999 6.414 ↑ 1.0 21,674 1

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

  • Sort Key: hidden_external_events_1.event_id
  • Sort Method: quicksort Memory: 131kB
19. 0.415 0.415 ↑ 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.002..0.415 rows=1,758 loops=1)

20. 71.526 214.578 ↑ 1.0 1 71,526

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

  • Recheck Cond: ((id = events_persons_1.organization_id) OR (id = events_persons.organization_id))
  • Filter: (type = ANY ('{0,1}'::integer[]))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=71526
21. 71.526 143.052 ↓ 0.0 0 71,526

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

22. 0.000 0.000 ↓ 0.0 0 71,526

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

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

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

  • Index Cond: (id = events_persons.organization_id)