explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MGfHD

Settings
# exclusive inclusive rows x rows loops node
1. 118.187 16,202.933 ↑ 73.1 21,084 1

Sort (cost=803,945.71..807,799.36 rows=1,541,462 width=154) (actual time=16,197.019..16,202.933 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, px.username
  • Sort Key: l.last_name, l.first_name, l.username
  • Sort Method: quicksort Memory: 6247kB
  • Buffers: shared hit=157766, temp read=107181 written=107181
2. 21.515 16,084.746 ↑ 73.1 21,084 1

Merge Right Join (cost=471,336.25..524,334.76 rows=1,541,462 width=154) (actual time=13,022.670..16,084.746 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, px.username
  • Merge Cond: (identity.login_id = l.login_id)
  • Buffers: shared hit=157766, temp read=107181 written=107181
3. 14.688 273.742 ↓ 1.0 19,363 1

Sort (cost=105,114.69..105,163.04 rows=19,339 width=23) (actual time=268.581..273.742 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.726 259.054 ↓ 1.0 19,363 1

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

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

Hash Left Join (cost=556.71..100,803.93 rows=8,058 width=23) (actual time=10.987..256.328 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=11.417..259.616 rows=5570 loops=1
  • Buffers: shared hit=22029
  • Worker 1: actual time=11.487..259.597 rows=5866 loops=1
  • Buffers: shared hit=22184
6. 240.099 240.099 ↑ 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.045..240.099 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, identity.remote_service, identity.remote_xmlrpc, identity.provider_url, identity.disable_dealer_group_view, identity.disable_device_edit
  • Filter: (identity.remote_login = 1)
  • Rows Removed by Filter: 1635886
  • Buffers: shared hit=74479
  • Worker 0: actual time=0.063..243.509 rows=5570 loops=1
  • Buffers: shared hit=21709
  • Worker 1: actual time=0.066..243.204 rows=5866 loops=1
  • Buffers: shared hit=21864
7. 5.311 10.780 ↓ 1.0 13,248 3

Hash (cost=395.76..395.76 rows=12,876 width=23) (actual time=10.780..10.780 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=11.155..11.155 rows=13248 loops=1
  • Buffers: shared hit=267
  • Worker 1: actual time=11.210..11.210 rows=13248 loops=1
  • Buffers: shared hit=267
8. 5.469 5.469 ↓ 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.469 rows=13,248 loops=3)

  • Output: g.name, g.group_id
  • Buffers: shared hit=801
  • Worker 0: actual time=0.016..5.707 rows=13248 loops=1
  • Buffers: shared hit=267
  • Worker 1: actual time=0.018..5.864 rows=13248 loops=1
  • Buffers: shared hit=267
9. 13.507 15,789.489 ↑ 51.4 21,084 1

Materialize (cost=366,221.56..398,708.71 rows=1,082,905 width=139) (actual time=12,754.079..15,789.489 rows=21,084 loops=1)

  • Output: l.login_id, (NULL::bigint), l.first_name, l.last_name, l.email_address, l.active, l.locked, l.username, l.system_admin, l.retry_count, l.last_attempted, l.last_address, l.ix_login_id, l.for_ix_api, l.can_create_group, px.username
  • Buffers: shared hit=82090, temp read=107181 written=107181
10. 1,096.993 15,775.982 ↑ 51.4 21,080 1

GroupAggregate (cost=366,221.56..385,172.40 rows=1,082,905 width=147) (actual time=12,754.073..15,775.982 rows=21,080 loops=1)

  • Output: l.login_id, NULL::bigint, l.first_name, l.last_name, l.email_address, l.active, l.locked, l.username, l.system_admin, l.retry_count, l.last_attempted, l.last_address, l.ix_login_id, l.for_ix_api, l.can_create_group, px.username
  • Group Key: l.login_id, px.username
  • Buffers: shared hit=82090, temp read=107181 written=107181
11. 7,444.954 14,678.989 ↓ 4.5 4,926,388 1

Sort (cost=366,221.56..368,928.82 rows=1,082,905 width=139) (actual time=12,754.063..14,678.989 rows=4,926,388 loops=1)

  • Output: l.login_id, px.username, l.first_name, l.last_name, l.email_address, l.active, l.locked, l.username, l.system_admin, l.retry_count, l.last_attempted, l.last_address, l.ix_login_id, l.for_ix_api, l.can_create_group
  • Sort Key: l.login_id, px.username
  • Sort Method: external merge Disk: 607912kB
  • Buffers: shared hit=82090, temp read=107181 written=107181
12. 2,522.265 7,234.035 ↓ 4.5 4,926,388 1

Hash Join (cost=11,971.69..179,947.92 rows=1,082,905 width=139) (actual time=136.080..7,234.035 rows=4,926,388 loops=1)

  • Output: l.login_id, px.username, l.first_name, l.last_name, l.email_address, l.active, l.locked, l.username, l.system_admin, l.retry_count, l.last_attempted, l.last_address, l.ix_login_id, l.for_ix_api, l.can_create_group
  • Hash Cond: (pi.group_id = g_1.group_id)
  • Buffers: shared hit=82090
13. 3,470.624 4,704.478 ↓ 4.5 4,926,388 1

Hash Join (cost=11,414.98..164,501.26 rows=1,082,905 width=147) (actual time=128.763..4,704.478 rows=4,926,388 loops=1)

  • Output: pi.group_id, l.login_id, l.first_name, l.last_name, l.email_address, l.active, l.locked, l.username, l.system_admin, l.retry_count, l.last_attempted, l.last_address, l.ix_login_id, l.for_ix_api, l.can_create_group, px.username
  • Hash Cond: (pi.login_id = l.login_id)
  • Buffers: shared hit=81823
14. 1,105.243 1,105.243 ↑ 1.0 4,927,021 1

Seq Scan on brivo20.identity pi (cost=0.00..123,765.17 rows=4,931,217 width=16) (actual time=0.004..1,105.243 rows=4,927,021 loops=1)

  • 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_url, pi.disable_dealer_group_view, pi.disable_device_edit
  • Buffers: shared hit=74453
15. 12.680 128.611 ↑ 1.0 21,083 1

Hash (cost=11,145.85..11,145.85 rows=21,530 width=139) (actual time=128.611..128.611 rows=21,083 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.retry_count, l.last_attempted, l.last_address, l.ix_login_id, l.for_ix_api, l.can_create_group, px.username
  • Buckets: 32768 Batches: 1 Memory Usage: 3339kB
  • Buffers: shared hit=7370
16. 10.525 115.931 ↑ 1.0 21,083 1

Hash Left Join (cost=5,890.92..11,145.85 rows=21,530 width=139) (actual time=70.888..115.931 rows=21,083 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.retry_count, l.last_attempted, l.last_address, l.ix_login_id, l.for_ix_api, l.can_create_group, px.username
  • Hash Cond: (l.ix_login_id = px.login_id)
  • Buffers: shared hit=7370
17. 35.019 35.019 ↑ 1.0 21,083 1

Seq Scan on brivo20.login l (cost=0.00..5,155.61 rows=21,530 width=120) (actual time=0.012..35.019 rows=21,083 loops=1)

  • Output: l.login_id, l.first_name, l.last_name, l.email_address, l.active, l.locked, l.username, l.password, l.temporary_password, l.security_question, l.security_answer, l.locale, l.time_zone, l.system_admin, l.can_create_group, l.auto_login, l.retry_count, l.last_attempted, l.last_address, l.deleted, l.ix_login_id, l.for_ix_api, l.temp_pwd_expiration, l.expiration, l.reset_hash, l.reset_hash_expiration, l.pwd_reset_retry_count, l.initial_lockout_time, l.two_factor_token, l.two_factor_token_expiration, l.two_factor_retry_count, l.two_factor_reset_hash, l.two_factor_reset_hash_exp, l.two_factor_auth_method_id, l.two_factor_reset_token, l.two_factor_reset_token_exp, l.two_factor_reset_retry
  • Filter: ((l.deleted = 0) AND (l.for_ix_api = 0))
  • Rows Removed by Filter: 76855
  • Buffers: shared hit=3685
18. 33.862 70.387 ↑ 1.0 97,938 1

Hash (cost=4,665.41..4,665.41 rows=98,041 width=27) (actual time=70.387..70.387 rows=97,938 loops=1)

  • Output: px.username, px.login_id
  • Buckets: 131072 Batches: 1 Memory Usage: 7106kB
  • Buffers: shared hit=3685
19. 36.525 36.525 ↑ 1.0 97,938 1

Seq Scan on brivo20.login px (cost=0.00..4,665.41 rows=98,041 width=27) (actual time=0.004..36.525 rows=97,938 loops=1)

  • Output: px.username, px.login_id
  • Buffers: shared hit=3685
20. 3.721 7.292 ↓ 1.0 13,248 1

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

  • Output: g_1.group_id
  • Buckets: 16384 Batches: 1 Memory Usage: 646kB
  • Buffers: shared hit=267
21. 3.571 3.571 ↓ 1.0 13,248 1

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

  • Output: g_1.group_id
  • Buffers: shared hit=267