explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XKSh

Settings
# exclusive inclusive rows x rows loops node
1. 5,015.923 90,146.244 ↓ 1.9 10,271,307 1

Unique (cost=2,599,436.80..2,666,828.82 rows=5,383,085 width=32) (actual time=63,707.226..90,146.244 rows=10,271,307 loops=1)

2. 4,503.700 85,130.321 ↓ 1.9 10,271,307 1

Merge Left Join (cost=2,599,436.80..2,653,371.11 rows=5,383,085 width=32) (actual time=63,707.225..85,130.321 rows=10,271,307 loops=1)

  • Merge Cond: (persons.id = events_persons_2.person_id)
3. 4,389.829 72,773.599 ↓ 1.9 10,271,307 1

Merge Anti Join (cost=1,968,686.78..2,009,163.34 rows=5,383,085 width=20) (actual time=57,071.204..72,773.599 rows=10,271,307 loops=1)

  • Merge Cond: (persons.id = hidden_externals.external_id)
4. 4,742.814 68,378.877 ↓ 1.9 10,273,640 1

Merge Left Join (cost=1,968,686.50..1,995,607.35 rows=5,384,164 width=20) (actual time=57,071.156..68,378.877 rows=10,273,640 loops=1)

  • Merge Cond: (persons.id = events_persons.person_id)
5. 11,471.578 56,145.635 ↓ 1.9 10,273,640 1

Sort (cost=1,355,914.12..1,369,374.53 rows=5,384,164 width=8) (actual time=50,711.240..56,145.635 rows=10,273,640 loops=1)

  • Sort Key: persons.id
  • Sort Method: external merge Disk: 180664kB
6. 6,170.332 44,674.057 ↓ 1.9 10,273,640 1

Hash Join (cost=118.45..701,376.75 rows=5,384,164 width=8) (actual time=1.302..44,674.057 rows=10,273,640 loops=1)

  • Hash Cond: (persons.organization_id = organizations.id)
7. 38,502.442 38,502.442 ↑ 1.0 11,932,646 1

Seq Scan on persons (cost=0.00..602,644.85 rows=11,939,150 width=8) (actual time=0.009..38,502.442 rows=11,932,646 loops=1)

  • Filter: (inferred_type = 0)
  • Rows Removed by Filter: 209036
8. 0.394 1.283 ↑ 1.0 1,329 1

Hash (cost=101.84..101.84 rows=1,329 width=4) (actual time=1.283..1.283 rows=1,329 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 63kB
9. 0.889 0.889 ↑ 1.0 1,329 1

Seq Scan on organizations (cost=0.00..101.84 rows=1,329 width=4) (actual time=0.006..0.889 rows=1,329 loops=1)

  • Filter: (type = ANY ('{0,1}'::integer[]))
  • Rows Removed by Filter: 1618
10. 28.982 7,490.428 ↓ 69,146.0 69,146 1

Materialize (cost=612,772.38..612,772.40 rows=1 width=16) (actual time=6,359.908..7,490.428 rows=69,146 loops=1)

11. 466.108 7,461.446 ↓ 69,146.0 69,146 1

Unique (cost=612,772.38..612,772.39 rows=1 width=16) (actual time=6,359.904..7,461.446 rows=69,146 loops=1)

12. 2,349.820 6,995.338 ↓ 2,034,361.0 2,034,361 1

Sort (cost=612,772.38..612,772.39 rows=1 width=16) (actual time=6,359.901..6,995.338 rows=2,034,361 loops=1)

  • Sort Key: events_persons.person_id, events_persons.start_time DESC
  • Sort Method: external merge Disk: 51696kB
13. 1,294.095 4,645.518 ↓ 2,034,361.0 2,034,361 1

Hash Anti Join (cost=218,245.57..612,772.37 rows=1 width=16) (actual time=2,086.850..4,645.518 rows=2,034,361 loops=1)

  • Hash Cond: (events_persons.event_id = events_persons_1.event_id)
14. 1,274.468 1,274.468 ↑ 1.0 2,081,274 1

Seq Scan on events_persons (cost=0.00..106,010.32 rows=2,107,904 width=16) (actual time=0.023..1,274.468 rows=2,081,274 loops=1)

  • Filter: (start_time <= now())
  • Rows Removed by Filter: 2273083
15. 1.089 2,076.955 ↑ 2,181.7 1,758 1

Hash (cost=155,321.61..155,321.61 rows=3,835,357 width=4) (actual time=2,076.955..2,076.955 rows=1,758 loops=1)

  • Buckets: 2097152 Batches: 4 Memory Usage: 16407kB
16. 1,067.313 2,075.866 ↑ 2,181.7 1,758 1

Hash Join (cost=71.82..155,321.61 rows=3,835,357 width=4) (actual time=4.852..2,075.866 rows=1,758 loops=1)

  • Hash Cond: (events_persons_1.person_id = hidden_externals_1.external_id)
17. 1,006.759 1,006.759 ↓ 1.0 4,354,357 1

Seq Scan on events_persons events_persons_1 (cost=0.00..84,238.55 rows=4,354,355 width=8) (actual time=0.013..1,006.759 rows=4,354,357 loops=1)

18. 0.926 1.794 ↑ 1.0 2,481 1

Hash (cost=40.81..40.81 rows=2,481 width=4) (actual time=1.794..1.794 rows=2,481 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 120kB
19. 0.868 0.868 ↑ 1.0 2,481 1

Seq Scan on hidden_externals hidden_externals_1 (cost=0.00..40.81 rows=2,481 width=4) (actual time=0.010..0.868 rows=2,481 loops=1)

20. 4.893 4.893 ↑ 1.0 2,481 1

Index Only Scan using hidden_externals_external_id_index on hidden_externals (cost=0.28..78.37 rows=2,481 width=4) (actual time=0.046..4.893 rows=2,481 loops=1)

  • Heap Fetches: 2481
21. 0.529 7,853.022 ↓ 1,101.0 1,101 1

Materialize (cost=630,750.02..630,750.04 rows=1 width=16) (actual time=6,636.016..7,853.022 rows=1,101 loops=1)

22. 499.266 7,852.493 ↓ 1,101.0 1,101 1

Unique (cost=630,750.02..630,750.03 rows=1 width=16) (actual time=6,636.012..7,852.493 rows=1,101 loops=1)

23. 2,419.591 7,353.227 ↓ 2,248,902.0 2,248,902 1

Sort (cost=630,750.02..630,750.03 rows=1 width=16) (actual time=6,636.011..7,353.227 rows=2,248,902 loops=1)

  • Sort Key: events_persons_2.person_id, events_persons_2.start_time
  • Sort Method: external merge Disk: 57136kB
24. 1,509.948 4,933.636 ↓ 2,248,902.0 2,248,902 1

Hash Anti Join (cost=218,245.57..630,750.01 rows=1 width=16) (actual time=2,074.060..4,933.636 rows=2,248,902 loops=1)

  • Hash Cond: (events_persons_2.event_id = events_persons_3.event_id)
25. 1,352.118 1,352.118 ↓ 1.0 2,273,083 1

Seq Scan on events_persons events_persons_2 (cost=0.00..106,010.32 rows=2,246,451 width=16) (actual time=0.029..1,352.118 rows=2,273,083 loops=1)

  • Filter: (start_time > now())
  • Rows Removed by Filter: 2081274
26. 0.998 2,071.570 ↑ 2,181.7 1,758 1

Hash (cost=155,321.61..155,321.61 rows=3,835,357 width=4) (actual time=2,071.570..2,071.570 rows=1,758 loops=1)

  • Buckets: 2097152 Batches: 4 Memory Usage: 16407kB
27. 1,064.986 2,070.572 ↑ 2,181.7 1,758 1

Hash Join (cost=71.82..155,321.61 rows=3,835,357 width=4) (actual time=4.432..2,070.572 rows=1,758 loops=1)

  • Hash Cond: (events_persons_3.person_id = hidden_externals_2.external_id)
28. 1,004.192 1,004.192 ↓ 1.0 4,354,357 1

Seq Scan on events_persons events_persons_3 (cost=0.00..84,238.55 rows=4,354,355 width=8) (actual time=0.007..1,004.192 rows=4,354,357 loops=1)

29. 0.680 1.394 ↑ 1.0 2,481 1

Hash (cost=40.81..40.81 rows=2,481 width=4) (actual time=1.394..1.394 rows=2,481 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 120kB
30. 0.714 0.714 ↑ 1.0 2,481 1

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