explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lk9g : Ummm

Settings
# exclusive inclusive rows x rows loops node
1. 9,068.864 102,588.231 ↓ 14.4 1,242,159 1

GroupAggregate (cost=11,507,643.24..11,512,400.30 rows=86,492 width=692) (actual time=92,622.013..102,588.231 rows=1,242,159 loops=1)

  • Group Key: person_credentials.id, door_controllers.id, accounts.hf_credentials_only
2. 10,615.556 93,519.367 ↓ 70.6 6,108,162 1

Sort (cost=11,507,643.24..11,507,859.47 rows=86,492 width=125) (actual time=92,621.987..93,519.367 rows=6,108,162 loops=1)

  • Sort Key: person_credentials.id, door_controllers.id, accounts.hf_credentials_only
  • Sort Method: external merge Disk: 759896kB
3. 975.620 82,903.811 ↓ 70.6 6,108,162 1

Hash Join (cost=314,454.48..11,494,933.27 rows=86,492 width=125) (actual time=70,648.326..82,903.811 rows=6,108,162 loops=1)

  • Hash Cond: ((roles.id = door_controllers_roles.role_id) AND (roles.shift_id = compiled_shifts.shift_id))
4. 44,984.192 81,924.890 ↑ 3.8 1,169,002 1

Merge Right Join (cost=314,127.77..11,382,297.73 rows=4,457,751 width=111) (actual time=70,645.016..81,924.890 rows=1,169,002 loops=1)

  • Merge Cond: (people.id = person_credentials.person_id)
  • Filter: ((roles.id = people_roles.role_id) OR (roles.id = groups_roles.role_id))
  • Rows Removed by Filter: 396256910
5. 593.867 2,144.550 ↑ 1.3 2,636,957 1

Merge Left Join (cost=314,125.68..367,994.23 rows=3,435,848 width=12) (actual time=1,112.370..2,144.550 rows=2,636,957 loops=1)

  • Merge Cond: (people.id = people_roles.person_id)
6. 519.748 988.383 ↑ 1.1 1,321,105 1

Sort (cost=222,230.66..225,978.38 rows=1,499,088 width=8) (actual time=775.960..988.383 rows=1,321,105 loops=1)

  • Sort Key: people.id
  • Sort Method: external merge Disk: 23336kB
7. 155.825 468.635 ↑ 1.1 1,321,130 1

Hash Left Join (cost=6,334.26..47,960.81 rows=1,499,088 width=8) (actual time=158.319..468.635 rows=1,321,130 loops=1)

  • Hash Cond: (groups_people.group_id = groups.id)
8. 178.031 304.163 ↑ 1.2 241,299 1

Hash Right Join (cost=5,272.76..29,455.04 rows=280,376 width=8) (actual time=149.651..304.163 rows=241,299 loops=1)

  • Hash Cond: (groups_people.person_id = people.id)
9. 74.976 74.976 ↓ 1.0 680,018 1

Seq Scan on groups_people (cost=0.00..9,808.69 rows=679,969 width=8) (actual time=0.013..74.976 rows=680,018 loops=1)

10. 20.156 51.156 ↑ 1.0 121,424 1

Hash (cost=3,279.32..3,279.32 rows=121,475 width=4) (actual time=51.156..51.156 rows=121,424 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3166kB
11. 31.000 31.000 ↑ 1.0 121,424 1

Seq Scan on people (cost=0.00..3,279.32 rows=121,475 width=4) (actual time=0.011..31.000 rows=121,424 loops=1)

  • Filter: ((NOT deleted) AND enabled)
  • Rows Removed by Filter: 51
12. 1.913 8.647 ↓ 1.0 16,748 1

Hash (cost=855.45..855.45 rows=16,484 width=8) (actual time=8.647..8.647 rows=16,748 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 911kB
13. 3.867 6.734 ↓ 1.0 16,748 1

Hash Right Join (cost=101.37..855.45 rows=16,484 width=8) (actual time=2.330..6.734 rows=16,748 loops=1)

  • Hash Cond: (groups_roles.group_id = groups.id)
14. 2.192 2.192 ↓ 1.0 27,853 1

Seq Scan on groups_roles (cost=0.00..402.51 rows=27,851 width=8) (actual time=0.011..2.192 rows=27,853 loops=1)

15. 0.300 0.675 ↑ 1.0 3,083 1

Hash (cost=62.83..62.83 rows=3,083 width=4) (actual time=0.675..0.675 rows=3,083 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 141kB
16. 0.375 0.375 ↑ 1.0 3,083 1

Seq Scan on groups (cost=0.00..62.83 rows=3,083 width=4) (actual time=0.012..0.375 rows=3,083 loops=1)

17. 206.370 562.300 ↓ 4.5 3,075,865 1

Materialize (cost=85,029.41..88,431.16 rows=680,349 width=8) (actual time=218.727..562.300 rows=3,075,865 loops=1)

18. 284.667 355.930 ↓ 1.0 680,415 1

Sort (cost=85,029.41..86,730.29 rows=680,349 width=8) (actual time=218.723..355.930 rows=680,415 loops=1)

  • Sort Key: people_roles.person_id
  • Sort Method: external merge Disk: 12064kB
19. 71.263 71.263 ↓ 1.0 680,415 1

Seq Scan on people_roles (cost=0.00..9,814.49 rows=680,349 width=8) (actual time=0.020..71.263 rows=680,415 loops=1)

20. 26,057.033 34,796.148 ↓ 8.7 397,425,910 1

Materialize (cost=2.08..735,975.22 rows=45,837,400 width=111) (actual time=0.097..34,796.148 rows=397,425,910 loops=1)

21. 6,111.195 8,739.115 ↓ 1.0 48,054,032 1

Nested Loop (cost=2.08..621,381.72 rows=45,837,400 width=111) (actual time=0.094..8,739.115 rows=48,054,032 loops=1)

22. 317.630 317.630 ↓ 1.0 462,058 1

Index Scan using index_person_credentials_on_person_id on person_credentials (cost=0.42..48,401.53 rows=458,374 width=102) (actual time=0.034..317.630 rows=462,058 loops=1)

  • Filter: ((value IS NOT NULL) AND ((value)::text <> ''::text))
  • Rows Removed by Filter: 4350
23. 2,310.201 2,310.290 ↓ 1.0 104 462,058

Materialize (cost=1.66..12.94 rows=100 width=9) (actual time=0.000..0.005 rows=104 loops=462,058)

24. 0.029 0.089 ↓ 1.0 104 1

Hash Left Join (cost=1.66..12.44 rows=100 width=9) (actual time=0.058..0.089 rows=104 loops=1)

  • Hash Cond: (roles.account_id = accounts.id)
25. 0.047 0.047 ↓ 1.0 104 1

Seq Scan on roles (cost=0.00..10.40 rows=100 width=12) (actual time=0.036..0.047 rows=104 loops=1)

  • Filter: (account_id = 4127)
  • Rows Removed by Filter: 332
26. 0.002 0.013 ↑ 1.0 1 1

Hash (cost=1.65..1.65 rows=1 width=5) (actual time=0.013..0.013 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on accounts (cost=0.00..1.65 rows=1 width=5) (actual time=0.010..0.011 rows=1 loops=1)

  • Filter: (id = 4127)
  • Rows Removed by Filter: 51
28. 0.558 3.301 ↓ 1.2 3,272 1

Hash (cost=284.18..284.18 rows=2,836 width=30) (actual time=3.301..3.301 rows=3,272 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 241kB
29. 0.619 2.743 ↓ 1.2 3,272 1

Hash Left Join (cost=154.49..284.18 rows=2,836 width=30) (actual time=1.373..2.743 rows=3,272 loops=1)

  • Hash Cond: (door_controllers_roles.door_controller_id = door_controllers.id)
30. 0.473 1.661 ↓ 1.2 3,272 1

Hash Join (cost=103.40..197.29 rows=2,836 width=20) (actual time=0.896..1.661 rows=3,272 loops=1)

  • Hash Cond: (compiled_shifts.door_controller_id = door_controllers_roles.door_controller_id)
31. 0.313 0.313 ↑ 1.0 550 1

Seq Scan on compiled_shifts (cost=0.00..54.51 rows=551 width=16) (actual time=0.013..0.313 rows=550 loops=1)

32. 0.420 0.875 ↑ 1.1 3,618 1

Hash (cost=55.40..55.40 rows=3,840 width=8) (actual time=0.875..0.875 rows=3,618 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 174kB
33. 0.455 0.455 ↑ 1.1 3,618 1

Seq Scan on door_controllers_roles (cost=0.00..55.40 rows=3,840 width=8) (actual time=0.021..0.455 rows=3,618 loops=1)

34. 0.125 0.463 ↑ 1.0 893 1

Hash (cost=39.93..39.93 rows=893 width=14) (actual time=0.463..0.463 rows=893 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
35. 0.338 0.338 ↑ 1.0 893 1

Seq Scan on door_controllers (cost=0.00..39.93 rows=893 width=14) (actual time=0.018..0.338 rows=893 loops=1)