explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kP5G : prod 2nd query

Settings
# exclusive inclusive rows x rows loops node
1. 1,192.700 7,887.387 ↑ 146.2 156,239 1

Sort (cost=6,546,571.05..6,603,688.53 rows=22,846,991 width=134) (actual time=7,712.240..7,887.387 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: 21352kB
  • Buffers: shared hit=45255 dirtied=7, temp read=21723 written=21721
2. 195.663 6,694.687 ↑ 146.2 156,239 1

Merge Right Join (cost=265,191.71..630,423.36 rows=22,846,991 width=134) (actual time=5,384.369..6,694.687 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: (identity.login_id = l.login_id)
  • Buffers: shared hit=45252 dirtied=7, temp read=19052 written=19050
3. 155.735 545.058 ↓ 1.0 154,556 1

Sort (cost=45,882.26..46,252.11 rows=147,938 width=29) (actual time=474.779..545.058 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
4. 113.273 389.323 ↓ 1.0 154,556 1

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

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

Seq Scan on brivo20.identity (cost=0.00..29,428.70 rows=147,938 width=16) (actual time=0.010..236.171 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
6. 20.573 39.879 ↓ 1.0 48,609 1

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

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

  • Output: g.name, g.group_id
  • Buffers: shared hit=623
8. 115.013 5,953.966 ↑ 5.1 156,239 1

Materialize (cost=219,309.45..243,077.19 rows=792,258 width=113) (actual time=4,909.555..5,953.966 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…
  • Buffers: shared hit=29396 dirtied=7, temp read=19052 written=19050
9. 392.238 5,838.953 ↑ 5.1 156,239 1

GroupAggregate (cost=219,309.45..233,173.97 rows=792,258 width=121) (actual time=4,909.545..5,838.953 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.…
  • Group Key: l.login_id, px.username
  • Buffers: shared hit=29396 dirtied=7, temp read=19052 written=19050
10. 2,259.346 5,446.715 ↓ 1.4 1,146,082 1

Sort (cost=219,309.45..221,290.10 rows=792,258 width=113) (actual time=4,909.512..5,446.715 rows=1,146,082 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_attempte…
  • Sort Key: l.login_id, px.username
  • Sort Method: external merge Disk: 135416kB
  • Buffers: shared hit=29396 dirtied=7, temp read=19052 written=19050
11. 961.544 3,187.369 ↓ 1.4 1,146,082 1

Hash Join (cost=29,412.66..92,941.05 rows=792,258 width=113) (actual time=656.798..3,187.369 rows=1,146,082 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_at…
  • Hash Cond: (pi.group_id = g_1.group_id)
  • Buffers: shared hit=29396 dirtied=7, temp read=2113 written=2111
12. 1,079.526 2,184.701 ↓ 1.4 1,146,082 1

Hash Join (cost=27,697.15..80,332.00 rows=792,258 width=121) (actual time=615.319..2,184.701 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.l…
  • Hash Cond: (pi.login_id = l.login_id)
  • Buffers: shared hit=28773 dirtied=7, temp read=2113 written=2111
13. 491.349 491.349 ↓ 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.007..491.349 rows=1,146,581 loops=1)

  • Output: pi.login_id, pi.group_id
  • Buffers: shared hit=15233
14. 137.709 613.826 ↑ 1.0 156,545 1

Hash (cost=22,954.01..22,954.01 rows=157,691 width=113) (actual time=613.826..613.826 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…
  • Buckets: 131072 Batches: 2 Memory Usage: 11904kB
  • Buffers: shared hit=13540 dirtied=7, temp written=1239
15. 98.058 476.117 ↑ 1.0 156,545 1

Hash Left Join (cost=11,855.92..22,954.01 rows=157,691 width=113) (actual time=244.862..476.117 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.la…
  • Hash Cond: (l.ix_login_id = px.login_id)
  • Buffers: shared hit=13540 dirtied=7
16. 134.790 134.790 ↑ 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.028..134.790 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_passw…
  • Filter: ((l.deleted = 0) AND (l.for_ix_api = 0))
  • Rows Removed by Filter: 69498
  • Buffers: shared hit=6770
17. 118.605 243.269 ↓ 1.0 226,043 1

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

  • Output: px.username, px.login_id
  • Buckets: 262144 Batches: 1 Memory Usage: 14366kB
  • Buffers: shared hit=6770 dirtied=7
18. 124.664 124.664 ↓ 1.0 226,043 1

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

  • Output: px.username, px.login_id
  • Buffers: shared hit=6770 dirtied=7
19. 21.012 41.124 ↓ 1.0 48,609 1

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

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

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