explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WOn8 : prod v8 write w/ stats

Settings
# exclusive inclusive rows x rows loops node
1. 4,551.621 329,548.492 ↓ 302.9 2,955,693 1

Nested Loop (cost=25,084,372.78..25,749,986.49 rows=9,759 width=40) (actual time=257,524.663..329,548.492 rows=2,955,693 loops=1)

2.          

CTE hidden_external_events

3. 101.258 244.286 ↓ 10.9 244,792 1

Nested Loop (cost=0.57..13,015.91 rows=22,496 width=12) (actual time=0.039..244.286 rows=244,792 loops=1)

4. 0.723 0.723 ↓ 1.0 2,685 1

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

5. 142.305 142.305 ↓ 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..4.81 rows=5 width=16) (actual time=0.008..0.053 rows=91 loops=2,685)

  • Index Cond: ((organization_id = hidden_externals.organization_id) AND (person_id = hidden_externals.external_id))
  • Heap Fetches: 85223
6. 2,717.363 316,091.074 ↓ 278.3 2,968,599 1

Merge Full Join (cost=25,071,355.54..25,701,054.76 rows=10,668 width=48) (actual time=257,524.634..316,091.074 rows=2,968,599 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: 124132
7. 17,533.196 207,053.441 ↓ 143.8 3,067,085 1

Unique (cost=14,911,372.73..15,351,219.87 rows=21,336 width=24) (actual time=167,462.413..207,053.441 rows=3,067,085 loops=1)

8. 94,500.489 189,520.245 ↓ 1.1 66,352,442 1

Sort (cost=14,911,372.73..15,057,988.45 rows=58,646,285 width=24) (actual time=167,462.412..189,520.245 rows=66,352,442 loops=1)

  • Sort Key: events_persons.organization_id, events_persons.person_id, events_persons.start_time DESC
  • Sort Method: external merge Disk: 2207448kB
9. 33,502.415 95,019.756 ↓ 1.1 66,352,442 1

Merge Anti Join (cost=2,076.65..6,485,301.03 rows=58,646,285 width=24) (actual time=480.164..95,019.756 rows=66,352,442 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: 98863
10. 60,951.498 60,951.498 ↓ 1.0 67,293,008 1

Index Scan using events_persons_event_id_person_id_key on events_persons (cost=0.57..6,226,150.61 rows=67,024,326 width=24) (actual time=0.027..60,951.498 rows=67,293,008 loops=1)

  • Filter: (start_time <= now())
  • Rows Removed by Filter: 28349461
11. 182.458 565.843 ↓ 15.0 338,168 1

Sort (cost=2,076.09..2,132.33 rows=22,496 width=12) (actual time=480.132..565.843 rows=338,168 loops=1)

  • Sort Key: hidden_external_events.event_id
  • Sort Method: quicksort Memory: 17619kB
12. 383.385 383.385 ↓ 10.9 244,792 1

CTE Scan on hidden_external_events (cost=0.00..449.92 rows=22,496 width=12) (actual time=0.041..383.385 rows=244,792 loops=1)

13. 74.519 103,899.387 ↓ 7.4 157,581 1

Materialize (cost=9,806,111.23..9,995,394.33 rows=21,336 width=24) (actual time=87,401.073..103,899.387 rows=157,581 loops=1)

14. 7,129.741 103,824.868 ↓ 7.4 157,581 1

Unique (cost=9,806,111.23..9,995,127.63 rows=21,336 width=24) (actual time=87,401.069..103,824.868 rows=157,581 loops=1)

15. 37,423.060 96,695.127 ↓ 1.1 28,186,976 1

Sort (cost=9,806,111.23..9,869,116.70 rows=25,202,186 width=24) (actual time=87,401.065..96,695.127 rows=28,186,976 loops=1)

  • Sort Key: events_persons_1.organization_id, events_persons_1.person_id, events_persons_1.start_time
  • Sort Method: external merge Disk: 937744kB
16. 13,657.362 59,272.067 ↓ 1.1 28,186,976 1

Merge Anti Join (cost=2,076.65..6,338,699.76 rows=25,202,186 width=24) (actual time=158.075..59,272.067 rows=28,186,976 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
17. 45,401.590 45,401.590 ↑ 1.0 28,349,420 1

Index Scan using events_persons_event_id_person_id_key on events_persons events_persons_1 (cost=0.57..6,226,150.61 rows=28,802,498 width=24) (actual time=16.183..45,401.590 rows=28,349,420 loops=1)

  • Filter: (start_time > now())
  • Rows Removed by Filter: 67293049
18. 155.286 213.115 ↓ 13.5 302,824 1

Sort (cost=2,076.09..2,132.33 rows=22,496 width=12) (actual time=141.887..213.115 rows=302,824 loops=1)

  • Sort Key: hidden_external_events_1.event_id
  • Sort Method: quicksort Memory: 17619kB
19. 57.829 57.829 ↓ 10.9 244,792 1

CTE Scan on hidden_external_events hidden_external_events_1 (cost=0.00..449.92 rows=22,496 width=12) (actual time=0.001..57.829 rows=244,792 loops=1)

20.          

SubPlan (forMerge Full Join)

21. 2,335.770 2,420.883 ↑ 1.0 217,432 1

Bitmap Heap Scan on persons (cost=6,454.05..353,318.38 rows=221,280 width=4) (actual time=115.355..2,420.883 rows=217,432 loops=1)

  • Recheck Cond: (inferred_type = 1)
  • Heap Blocks: exact=158097
22. 85.113 85.113 ↑ 1.0 217,473 1

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

23. 2,968.599 8,905.797 ↑ 1.0 1 2,968,599

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

  • 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=2968599
24. 2,968.599 5,937.198 ↓ 0.0 0 2,968,599

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

25. 0.000 0.000 ↓ 0.0 0 2,968,599

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

  • Index Cond: (id = events_persons_1.organization_id)
26. 2,968.599 2,968.599 ↑ 1.0 1 2,968,599

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

  • Index Cond: (id = events_persons.organization_id)