explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MUAv

Settings
# exclusive inclusive rows x rows loops node
1. 1,224.584 7,330.159 ↑ 29.1 156,238 1

Sort (cost=1,150,157.20..1,161,525.86 rows=4,547,464 width=134) (actual time=7,149.589..7,330.159 rows=156,238 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…
  • Sort Key: l.last_name, l.first_name, l.username
  • Sort Method: external merge Disk: 22872kB
  • Buffers: shared hit=45255 dirtied=3, temp read=21531 written=21529
2. 151.003 6,105.575 ↑ 29.1 156,238 1

Merge Left Join (cost=260,699.99..336,421.29 rows=4,547,464 width=134) (actual time=5,011.596..6,105.575 rows=156,238 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_a…
  • Merge Cond: (l.login_id = identity.login_id)
  • Buffers: shared hit=45252 dirtied=3, temp read=18670 written=18668
3. 359.930 5,471.429 ↑ 1.0 156,238 1

GroupAggregate (cost=214,817.73..220,355.93 rows=157,691 width=121) (actual time=4,597.539..5,471.429 rows=156,238 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_a…
  • Group Key: l.login_id
  • Buffers: shared hit=29396 dirtied=3, temp read=18670 written=18668
4. 2,113.008 5,111.499 ↓ 1.4 1,146,081 1

Sort (cost=214,817.73..216,798.38 rows=792,258 width=101) (actual time=4,597.498..5,111.499 rows=1,146,081 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, …
  • Sort Key: l.login_id
  • Sort Method: external merge Disk: 131704kB
  • Buffers: shared hit=29396 dirtied=3, temp read=18670 written=18668
5. 861.898 2,998.491 ↓ 1.4 1,146,081 1

Hash Join (cost=30,489.44..93,863.83 rows=792,258 width=101) (actual time=633.311..2,998.491 rows=1,146,081 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_add…
  • Hash Cond: (pi.group_id = g_1.group_id)
  • Buffers: shared hit=29396 dirtied=3, temp read=2193 written=2191
6. 996.104 2,100.172 ↓ 1.4 1,146,081 1

Hash Join (cost=28,773.93..81,254.78 rows=792,258 width=109) (actual time=596.516..2,100.172 rows=1,146,081 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_at…
  • Hash Cond: (pi.login_id = l.login_id)
  • Buffers: shared hit=28773 dirtied=3, temp read=2193 written=2191
7. 508.257 508.257 ↓ 1.0 1,146,580 1

Seq Scan on brivo20.identity pi (cost=0.00..26,589.56 rows=1,135,656 width=16) (actual time=0.007..508.257 rows=1,146,580 loops=1)

  • Output: pi.login_id, pi.group_id
  • Buffers: shared hit=15233
8. 145.957 595.811 ↑ 1.0 156,544 1

Hash (cost=24,184.79..24,184.79 rows=157,691 width=109) (actual time=595.811..595.811 rows=156,544 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…
  • Buckets: 131072 Batches: 2 Memory Usage: 12548kB
  • Buffers: shared hit=13540 dirtied=3, temp written=1319
9. 140.377 449.854 ↑ 1.0 156,544 1

Hash Join (cost=11,855.92..24,184.79 rows=157,691 width=109) (actual time=191.616..449.854 rows=156,544 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_att…
  • Hash Cond: (l.login_id = px.login_id)
  • Buffers: shared hit=13540 dirtied=3
10. 119.924 119.924 ↑ 1.0 156,544 1

Seq Scan on brivo20.login l (cost=0.00..10,160.61 rows=157,691 width=101) (actual time=0.025..119.924 rows=156,544 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…
  • Filter: ((l.deleted = 0) AND (l.for_ix_api = 0))
  • Rows Removed by Filter: 69499
  • Buffers: shared hit=6770
11. 90.920 189.553 ↓ 1.0 226,043 1

Hash (cost=9,030.41..9,030.41 rows=226,041 width=8) (actual time=189.553..189.553 rows=226,043 loops=1)

  • Output: px.login_id
  • Buckets: 262144 Batches: 1 Memory Usage: 10878kB
  • Buffers: shared hit=6770 dirtied=3
12. 98.633 98.633 ↓ 1.0 226,043 1

Seq Scan on brivo20.login px (cost=0.00..9,030.41 rows=226,041 width=8) (actual time=0.007..98.633 rows=226,043 loops=1)

  • Output: px.login_id
  • Buffers: shared hit=6770 dirtied=3
13. 17.852 36.421 ↓ 1.0 48,609 1

Hash (cost=1,108.56..1,108.56 rows=48,556 width=8) (actual time=36.421..36.421 rows=48,609 loops=1)

  • Output: g_1.group_id
  • Buckets: 65536 Batches: 1 Memory Usage: 2411kB
  • Buffers: shared hit=623
14. 18.569 18.569 ↓ 1.0 48,609 1

Seq Scan on brivo20.login_group g_1 (cost=0.00..1,108.56 rows=48,556 width=8) (actual time=0.014..18.569 rows=48,609 loops=1)

  • Output: g_1.group_id
  • Buffers: shared hit=623
15. 156.566 483.143 ↓ 1.0 154,555 1

Sort (cost=45,882.26..46,252.11 rows=147,938 width=29) (actual time=414.031..483.143 rows=154,555 loops=1)

  • Output: identity.login_id, g.name
  • Sort Key: identity.login_id
  • Sort Method: quicksort Memory: 15664kB
  • Buffers: shared hit=15856
16. 111.732 326.577 ↓ 1.0 154,555 1

Hash Left Join (cost=1,715.51..33,178.36 rows=147,938 width=29) (actual time=37.109..326.577 rows=154,555 loops=1)

  • Output: identity.login_id, g.name
  • Hash Cond: (identity.group_id = g.group_id)
  • Buffers: shared hit=15856
17. 178.116 178.116 ↓ 1.0 154,555 1

Seq Scan on brivo20.identity (cost=0.00..29,428.70 rows=147,938 width=16) (actual time=0.038..178.116 rows=154,555 loops=1)

  • Output: identity.identity_id, identity.login_id, identity.group_id, identity.username, identity.credential, identity.remote_login, identity.login_url, iden…
  • Filter: (identity.remote_login = 1)
  • Rows Removed by Filter: 992025
  • Buffers: shared hit=15233
18. 19.794 36.729 ↓ 1.0 48,609 1

Hash (cost=1,108.56..1,108.56 rows=48,556 width=29) (actual time=36.729..36.729 rows=48,609 loops=1)

  • Output: g.name, g.group_id
  • Buckets: 65536 Batches: 1 Memory Usage: 3586kB
  • Buffers: shared hit=623
19. 16.935 16.935 ↓ 1.0 48,609 1

Seq Scan on brivo20.login_group g (cost=0.00..1,108.56 rows=48,556 width=29) (actual time=0.013..16.935 rows=48,609 loops=1)

  • Output: g.name, g.group_id
  • Buffers: shared hit=623