explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ow4w

Settings
# exclusive inclusive rows x rows loops node
1. 1,241.289 7,371.711 ↑ 29.1 156,239 1

Sort (cost=1,150,157.20..1,161,525.86 rows=4,547,464 width=134) (actual time=7,212.359..7,371.711 rows=156,239 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=45252, temp read=21531 written=21529
2. 168.011 6,130.422 ↑ 29.1 156,239 1

Merge Left Join (cost=260,699.99..336,421.29 rows=4,547,464 width=134) (actual time=4,942.179..6,130.422 rows=156,239 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, temp read=18670 written=18668
3. 396.500 5,467.454 ↑ 1.0 156,239 1

GroupAggregate (cost=214,817.73..220,355.93 rows=157,691 width=121) (actual time=4,507.592..5,467.454 rows=156,239 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, temp read=18670 written=18668
4. 2,133.849 5,070.954 ↓ 1.4 1,146,082 1

Sort (cost=214,817.73..216,798.38 rows=792,258 width=101) (actual time=4,507.576..5,070.954 rows=1,146,082 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, temp read=18670 written=18668
5. 872.727 2,937.105 ↓ 1.4 1,146,082 1

Hash Join (cost=30,489.44..93,863.83 rows=792,258 width=101) (actual time=646.123..2,937.105 rows=1,146,082 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, temp read=2193 written=2191
6. 975.028 2,014.753 ↓ 1.4 1,146,082 1

Hash Join (cost=28,773.93..81,254.78 rows=792,258 width=109) (actual time=596.212..2,014.753 rows=1,146,082 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, temp read=2193 written=2191
7. 444.097 444.097 ↓ 1.0 1,146,581 1

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

  • Output: pi.login_id, pi.group_id
  • Buffers: shared hit=15233
8. 141.479 595.628 ↑ 1.0 156,545 1

Hash (cost=24,184.79..24,184.79 rows=157,691 width=109) (actual time=595.628..595.628 rows=156,545 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, temp written=1319
9. 143.203 454.149 ↑ 1.0 156,545 1

Hash Join (cost=11,855.92..24,184.79 rows=157,691 width=109) (actual time=185.482..454.149 rows=156,545 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
10. 126.864 126.864 ↑ 1.0 156,545 1

Seq Scan on brivo20.login l (cost=0.00..10,160.61 rows=157,691 width=101) (actual time=0.052..126.864 rows=156,545 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: 69498
  • Buffers: shared hit=6770
11. 97.806 184.082 ↓ 1.0 226,043 1

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

  • Output: px.login_id
  • Buckets: 262144 Batches: 1 Memory Usage: 10878kB
  • Buffers: shared hit=6770
12. 86.276 86.276 ↓ 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.006..86.276 rows=226,043 loops=1)

  • Output: px.login_id
  • Buffers: shared hit=6770
13. 26.016 49.625 ↓ 1.0 48,609 1

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

  • Output: g_1.group_id
  • Buckets: 65536 Batches: 1 Memory Usage: 2411kB
  • Buffers: shared hit=623
14. 23.609 23.609 ↓ 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.006..23.609 rows=48,609 loops=1)

  • Output: g_1.group_id
  • Buffers: shared hit=623
15. 143.060 494.957 ↓ 1.0 154,556 1

Sort (cost=45,882.26..46,252.11 rows=147,938 width=29) (actual time=434.576..494.957 rows=154,556 loops=1)

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

Hash Left Join (cost=1,715.51..33,178.36 rows=147,938 width=29) (actual time=44.357..351.897 rows=154,556 loops=1)

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

Seq Scan on brivo20.identity (cost=0.00..29,428.70 rows=147,938 width=16) (actual time=0.040..190.061 rows=154,556 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. 24.267 43.944 ↓ 1.0 48,609 1

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

  • Output: g.name, g.group_id
  • Buckets: 65536 Batches: 1 Memory Usage: 3586kB
  • Buffers: shared hit=623
19. 19.677 19.677 ↓ 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.009..19.677 rows=48,609 loops=1)

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