explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kAJl

Settings
# exclusive inclusive rows x rows loops node
1. 93.479 14,915.023 ↑ 1.0 69,942 1

Nested Loop (cost=1,185,006.72..1,483,403.76 rows=72,634 width=40) (actual time=11,824.693..14,915.023 rows=69,942 loops=1)

2.          

CTE hidden_external_events

3. 1.845 7.434 ↑ 12.7 1,758 1

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

4. 0.627 0.627 ↑ 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.627 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. 163.562 14,611.157 ↑ 1.1 70,129 1

Merge Full Join (cost=1,094,005.83..1,127,809.90 rows=80,545 width=48) (actual time=11,824.652..14,611.157 rows=70,129 loops=1)

  • Merge Cond: ((events_persons.organization_id = events_persons_1.organization_id) AND (events_persons.person_id = events_persons_1.person_id))
  • Filter: (NOT (hashed SubPlan 2))
  • Rows Removed by Filter: 1397
7. 529.971 6,885.954 ↑ 1.1 71,517 1

Unique (cost=421,120.23..434,956.50 rows=80,272 width=24) (actual time=5,538.178..6,885.954 rows=71,517 loops=1)

8. 3,135.052 6,355.983 ↓ 1.1 2,074,702 1

Sort (cost=421,120.23..425,732.32 rows=1,844,837 width=24) (actual time=5,538.177..6,355.983 rows=2,074,702 loops=1)

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

Merge Anti Join (cost=2,050.96..202,093.24 rows=1,844,837 width=24) (actual time=9.093..3,220.931 rows=2,074,702 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,206.127 2,206.127 ↑ 1.0 2,081,297 1

Index Scan using events_persons_event_id_person_id_key on events_persons (cost=0.43..186,585.86 rows=2,108,385 width=24) (actual time=0.016..2,206.127 rows=2,081,297 loops=1)

  • Filter: (start_time <= now())
  • Rows Removed by Filter: 2273060
11. 11.536 19.911 ↓ 2.1 45,636 1

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

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

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

13. 0.609 7,282.826 ↑ 70.6 1,137 1

Materialize (cost=437,186.56..452,929.15 rows=80,272 width=24) (actual time=5,827.468..7,282.826 rows=1,137 loops=1)

14. 560.919 7,282.217 ↑ 70.6 1,137 1

Unique (cost=437,186.56..451,925.75 rows=80,272 width=24) (actual time=5,827.464..7,282.217 rows=1,137 loops=1)

15. 3,333.651 6,721.298 ↓ 1.2 2,269,545 1

Sort (cost=437,186.56..442,099.62 rows=1,965,226 width=24) (actual time=5,827.461..6,721.298 rows=2,269,545 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,114.771 3,387.647 ↓ 1.2 2,269,545 1

Merge Anti Join (cost=2,050.96..202,971.39 rows=1,965,226 width=24) (actual time=1.452..3,387.647 rows=2,269,545 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,266.479 2,266.479 ↓ 1.0 2,273,060 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,245,972 width=24) (actual time=0.471..2,266.479 rows=2,273,060 loops=1)

  • Filter: (start_time > now())
  • Rows Removed by Filter: 2081297
18. 5.968 6.397 ↑ 1.0 21,674 1

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

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

20.          

SubPlan (forMerge Full Join)

21. 246.815 278.815 ↓ 1.0 209,036 1

Bitmap Heap Scan on persons (cost=2,180.82..235,193.15 rows=202,361 width=4) (actual time=61.246..278.815 rows=209,036 loops=1)

  • Recheck Cond: (inferred_type = 1)
  • Heap Blocks: exact=138598
22. 32.000 32.000 ↓ 1.0 209,036 1

Bitmap Index Scan on persons_organization_id_inferred_internals_index (cost=0.00..2,130.23 rows=202,361 width=0) (actual time=32.000..32.000 rows=209,036 loops=1)

23. 70.129 210.387 ↑ 1.0 1 70,129

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

  • 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=70129
24. 70.129 140.258 ↓ 0.0 0 70,129

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

25. 0.000 0.000 ↓ 0.0 0 70,129

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

  • Index Cond: (id = events_persons_1.organization_id)
26. 70.129 70.129 ↑ 1.0 1 70,129

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

  • Index Cond: (id = events_persons.organization_id)