explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3uFB

Settings
# exclusive inclusive rows x rows loops node
1. 113.217 4,542.867 ↑ 1.5 21,084 1

Sort (cost=241,611.87..241,688.49 rows=30,647 width=154) (actual time=4,538.134..4,542.867 rows=21,084 loops=1)

  • Output: l.login_id, l.first_name, l.last_name, l.email_address, l.active, l.locked, l.username, l.system_admin, l.can_create_group, l.retry_count, l.last_attempted, l.last_address, l.for_ix_api, l.ix_login_id, g.name, l.ix_username
  • Sort Key: l.last_name, l.first_name, l.username
  • Sort Method: quicksort Memory: 6343kB
  • Buffers: shared hit=173518
2. 18.159 4,429.650 ↑ 1.5 21,084 1

Merge Right Join (cost=238,771.74..239,328.14 rows=30,647 width=154) (actual time=4,401.226..4,429.650 rows=21,084 loops=1)

  • Output: l.login_id, l.first_name, l.last_name, l.email_address, l.active, l.locked, l.username, l.system_admin, l.can_create_group, l.retry_count, l.last_attempted, l.last_address, l.for_ix_api, l.ix_login_id, g.name, l.ix_username
  • Merge Cond: (identity.login_id = l.login_id)
  • Buffers: shared hit=173518
3. 14.220 274.341 ↓ 1.0 19,363 1

Sort (cost=105,114.69..105,163.04 rows=19,339 width=23) (actual time=269.690..274.341 rows=19,363 loops=1)

  • Output: identity.login_id, g.name
  • Sort Key: identity.login_id
  • Sort Method: quicksort Memory: 2278kB
  • Buffers: shared hit=75676
4. 2.743 260.121 ↓ 1.0 19,363 1

Gather (cost=1,556.71..103,737.83 rows=19,339 width=23) (actual time=9.783..260.121 rows=19,363 loops=1)

  • Output: identity.login_id, g.name
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=75676
5. 5.297 257.378 ↑ 1.2 6,454 3

Hash Left Join (cost=556.71..100,803.93 rows=8,058 width=23) (actual time=10.479..257.378 rows=6,454 loops=3)

  • Output: identity.login_id, g.name
  • Hash Cond: (identity.group_id = g.group_id)
  • Buffers: shared hit=75386
  • Worker 0: actual time=10.971..260.426 rows=5870 loops=1
  • Buffers: shared hit=22263
  • Worker 1: actual time=11.025..260.748 rows=5471 loops=1
  • Buffers: shared hit=21903
6. 241.805 241.805 ↑ 1.2 6,454 3

Parallel Seq Scan on brivo20.identity (cost=0.00..100,136.42 rows=8,058 width=16) (actual time=0.046..241.805 rows=6,454 loops=3)

  • Output: identity.identity_id, identity.login_id, identity.group_id, identity.username, identity.credential, identity.remote_login, identity.login_url, identity.admin, identity.external_id, identity.external_type_id, identity.owner, identi…
  • Filter: (identity.remote_login = 1)
  • Rows Removed by Filter: 1635886
  • Buffers: shared hit=74479
  • Worker 0: actual time=0.061..244.692 rows=5870 loops=1
  • Buffers: shared hit=21943
  • Worker 1: actual time=0.069..245.153 rows=5471 loops=1
  • Buffers: shared hit=21583
7. 5.035 10.276 ↓ 1.0 13,248 3

Hash (cost=395.76..395.76 rows=12,876 width=23) (actual time=10.276..10.276 rows=13,248 loops=3)

  • Output: g.name, g.group_id
  • Buckets: 16384 Batches: 1 Memory Usage: 887kB
  • Buffers: shared hit=801
  • Worker 0: actual time=10.712..10.712 rows=13248 loops=1
  • Buffers: shared hit=267
  • Worker 1: actual time=10.749..10.749 rows=13248 loops=1
  • Buffers: shared hit=267
8. 5.241 5.241 ↓ 1.0 13,248 3

Seq Scan on brivo20.login_group g (cost=0.00..395.76 rows=12,876 width=23) (actual time=0.012..5.241 rows=13,248 loops=3)

  • Output: g.name, g.group_id
  • Buffers: shared hit=801
  • Worker 0: actual time=0.015..5.568 rows=13248 loops=1
  • Buffers: shared hit=267
  • Worker 1: actual time=0.018..5.629 rows=13248 loops=1
  • Buffers: shared hit=267
9. 21.142 4,137.150 ↑ 1.0 21,084 1

Sort (cost=133,657.05..133,710.87 rows=21,530 width=139) (actual time=4,131.530..4,137.150 rows=21,084 loops=1)

  • Output: l.login_id, l.first_name, l.last_name, l.email_address, l.active, l.locked, l.username, l.system_admin, l.can_create_group, l.retry_count, l.last_attempted, l.last_address, l.for_ix_api, l.ix_login_id, l.ix_username
  • Sort Key: l.login_id
  • Sort Method: quicksort Memory: 6251kB
  • Buffers: shared hit=97842
10. 7.918 4,116.008 ↑ 1.0 21,080 1

Subquery Scan on l (cost=131,676.93..132,107.53 rows=21,530 width=139) (actual time=4,099.750..4,116.008 rows=21,080 loops=1)

  • Output: l.login_id, l.first_name, l.last_name, l.email_address, l.active, l.locked, l.username, l.system_admin, l.can_create_group, l.retry_count, l.last_attempted, l.last_address, l.for_ix_api, l.ix_login_id, l.ix_username
  • Buffers: shared hit=97842
11. 23.645 4,108.090 ↑ 1.0 21,080 1

Finalize HashAggregate (cost=131,676.93..131,892.23 rows=21,530 width=147) (actual time=4,099.748..4,108.090 rows=21,080 loops=1)

  • Output: l_1.login_id, NULL::bigint, l_1.first_name, l_1.last_name, l_1.email_address, l_1.active, l_1.locked, l_1.username, l_1.system_admin, l_1.retry_count, l_1.last_attempted, l_1.last_address, l_1.ix_login_id, l_1.for_ix_api, l_1.can_create…
  • Group Key: l_1.login_id
  • Buffers: shared hit=97842
12. 15.822 4,084.445 ↑ 1.9 22,891 1

Gather (cost=127,047.98..131,569.28 rows=43,060 width=120) (actual time=4,067.840..4,084.445 rows=22,891 loops=1)

  • Output: l_1.login_id, l_1.first_name, l_1.last_name, l_1.email_address, l_1.active, l_1.locked, l_1.username, l_1.system_admin, l_1.retry_count, l_1.last_attempted, l_1.last_address, l_1.ix_login_id, l_1.for_ix_api, l_1.can_create_group
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=97842
13. 720.683 4,068.623 ↑ 2.8 7,630 3

Partial HashAggregate (cost=126,047.98..126,263.28 rows=21,530 width=120) (actual time=4,064.557..4,068.623 rows=7,630 loops=3)

  • Output: l_1.login_id, l_1.first_name, l_1.last_name, l_1.email_address, l_1.active, l_1.locked, l_1.username, l_1.system_admin, l_1.retry_count, l_1.last_attempted, l_1.last_address, l_1.ix_login_id, l_1.for_ix_api, l_1.can_create_g…
  • Group Key: l_1.login_id
  • Buffers: shared hit=97396
  • Worker 0: actual time=4063.209..4067.140 rows=7145 loops=1
  • Buffers: shared hit=32014
  • Worker 1: actual time=4063.372..4067.050 rows=6869 loops=1
  • Buffers: shared hit=31125
14. 1,155.625 3,347.940 ↓ 3.6 1,642,129 3

Hash Join (cost=11,498.94..124,919.95 rows=451,210 width=120) (actual time=160.317..3,347.940 rows=1,642,129 loops=3)

  • Output: l_1.login_id, l_1.first_name, l_1.last_name, l_1.email_address, l_1.active, l_1.locked, l_1.username, l_1.system_admin, l_1.retry_count, l_1.last_attempted, l_1.last_address, l_1.ix_login_id, l_1.for_ix_api, l_1.can_cr…
  • Hash Cond: (pi.group_id = g_1.group_id)
  • Buffers: shared hit=97396
  • Worker 0: actual time=165.704..3348.580 rows=1637081 loops=1
  • Buffers: shared hit=32014
  • Worker 1: actual time=163.386..3348.968 rows=1579759 loops=1
  • Buffers: shared hit=31125
15. 1,481.972 2,182.505 ↓ 3.6 1,642,129 3

Hash Join (cost=10,942.23..118,159.10 rows=451,210 width=128) (actual time=150.384..2,182.505 rows=1,642,129 loops=3)

  • Output: pi.group_id, l_1.login_id, l_1.first_name, l_1.last_name, l_1.email_address, l_1.active, l_1.locked, l_1.username, l_1.system_admin, l_1.retry_count, l_1.last_attempted, l_1.last_address, l_1.ix_login_id, l_1.for…
  • Hash Cond: (pi.login_id = l_1.login_id)
  • Buffers: shared hit=96569
  • Worker 0: actual time=155.009..2190.167 rows=1637081 loops=1
  • Buffers: shared hit=31734
  • Worker 1: actual time=152.823..2204.110 rows=1579759 loops=1
  • Buffers: shared hit=30845
16. 550.275 550.275 ↑ 1.3 1,642,340 3

Parallel Seq Scan on brivo20.identity pi (cost=0.00..94,999.74 rows=2,054,674 width=16) (actual time=0.007..550.275 rows=1,642,340 loops=3)

  • Output: pi.identity_id, pi.login_id, pi.group_id, pi.username, pi.credential, pi.remote_login, pi.login_url, pi.admin, pi.external_id, pi.external_type_id, pi.owner, pi.remote_service, pi.remote_xmlrpc, pi.provider…
  • Buffers: shared hit=74453
  • Worker 0: actual time=0.010..561.149 rows=1637295 loops=1
  • Buffers: shared hit=24361
  • Worker 1: actual time=0.009..559.426 rows=1579917 loops=1
  • Buffers: shared hit=23472
17. 14.925 150.258 ↑ 1.0 21,083 3

Hash (cost=10,673.10..10,673.10 rows=21,530 width=128) (actual time=150.258..150.258 rows=21,083 loops=3)

  • Output: l_1.login_id, l_1.first_name, l_1.last_name, l_1.email_address, l_1.active, l_1.locked, l_1.username, l_1.system_admin, l_1.retry_count, l_1.last_attempted, l_1.last_address, l_1.ix_login_id, l_1.for_ix_api…
  • Buckets: 32768 Batches: 1 Memory Usage: 3515kB
  • Buffers: shared hit=22116
  • Worker 0: actual time=154.852..154.852 rows=21083 loops=1
  • Buffers: shared hit=7373
  • Worker 1: actual time=152.670..152.670 rows=21083 loops=1
  • Buffers: shared hit=7373
18. 46.978 135.333 ↑ 1.0 21,083 3

Hash Join (cost=5,424.74..10,673.10 rows=21,530 width=128) (actual time=57.938..135.333 rows=21,083 loops=3)

  • Output: l_1.login_id, l_1.first_name, l_1.last_name, l_1.email_address, l_1.active, l_1.locked, l_1.username, l_1.system_admin, l_1.retry_count, l_1.last_attempted, l_1.last_address, l_1.ix_login_id, l_1.for_…
  • Hash Cond: (px.login_id = l_1.login_id)
  • Buffers: shared hit=22116
  • Worker 0: actual time=61.651..139.737 rows=21083 loops=1
  • Buffers: shared hit=7373
  • Worker 1: actual time=61.951..138.100 rows=21083 loops=1
  • Buffers: shared hit=7373
19. 30.579 30.579 ↑ 1.0 97,938 3

Seq Scan on brivo20.login px (cost=0.00..4,665.41 rows=98,041 width=8) (actual time=0.012..30.579 rows=97,938 loops=3)

  • Output: px.login_id, px.first_name, px.last_name, px.email_address, px.active, px.locked, px.username, px.password, px.temporary_password, px.security_question, px.security_answer, px.locale, px.time_zo…
  • Buffers: shared hit=11055
  • Worker 0: actual time=0.015..30.829 rows=97938 loops=1
  • Buffers: shared hit=3685
  • Worker 1: actual time=0.016..29.839 rows=97938 loops=1
  • Buffers: shared hit=3685
20. 13.653 57.776 ↑ 1.0 21,083 3

Hash (cost=5,155.61..5,155.61 rows=21,530 width=120) (actual time=57.776..57.776 rows=21,083 loops=3)

  • Output: l_1.login_id, l_1.first_name, l_1.last_name, l_1.email_address, l_1.active, l_1.locked, l_1.username, l_1.system_admin, l_1.retry_count, l_1.last_attempted, l_1.last_address, l_1.ix_login_id, l_…
  • Buckets: 32768 Batches: 1 Memory Usage: 3302kB
  • Buffers: shared hit=11061
  • Worker 0: actual time=61.485..61.485 rows=21083 loops=1
  • Buffers: shared hit=3688
  • Worker 1: actual time=61.785..61.785 rows=21083 loops=1
  • Buffers: shared hit=3688
21. 44.123 44.123 ↑ 1.0 21,083 3

Seq Scan on brivo20.login l_1 (cost=0.00..5,155.61 rows=21,530 width=120) (actual time=0.024..44.123 rows=21,083 loops=3)

  • Output: l_1.login_id, l_1.first_name, l_1.last_name, l_1.email_address, l_1.active, l_1.locked, l_1.username, l_1.system_admin, l_1.retry_count, l_1.last_attempted, l_1.last_address, l_1.ix_login_…
  • Filter: ((l_1.deleted = 0) AND (l_1.for_ix_api = 0))
  • Rows Removed by Filter: 76855
  • Buffers: shared hit=11061
  • Worker 0: actual time=0.029..47.642 rows=21083 loops=1
  • Buffers: shared hit=3688
  • Worker 1: actual time=0.032..48.070 rows=21083 loops=1
  • Buffers: shared hit=3688
22. 4.632 9.810 ↓ 1.0 13,248 3

Hash (cost=395.76..395.76 rows=12,876 width=8) (actual time=9.810..9.810 rows=13,248 loops=3)

  • Output: g_1.group_id
  • Buckets: 16384 Batches: 1 Memory Usage: 646kB
  • Buffers: shared hit=801
  • Worker 0: actual time=10.566..10.566 rows=13248 loops=1
  • Buffers: shared hit=267
  • Worker 1: actual time=10.400..10.400 rows=13248 loops=1
  • Buffers: shared hit=267
23. 5.178 5.178 ↓ 1.0 13,248 3

Seq Scan on brivo20.login_group g_1 (cost=0.00..395.76 rows=12,876 width=8) (actual time=0.016..5.178 rows=13,248 loops=3)

  • Output: g_1.group_id
  • Buffers: shared hit=801
  • Worker 0: actual time=0.019..5.715 rows=13248 loops=1
  • Buffers: shared hit=267
  • Worker 1: actual time=0.022..5.721 rows=13248 loops=1
  • Buffers: shared hit=267