explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5H64

Settings
# exclusive inclusive rows x rows loops node
1. 53.390 783.130 ↓ 2.0 2 1

Nested Loop (cost=1.42..1,620,796.68 rows=1 width=148) (actual time=485.689..783.130 rows=2 loops=1)

  • Output: ka.endpoint_id, sl_svc.str_val, sl_host.str_val, sl_user.str_val, sl_key.str_val, sl_svc2.str_val, ke.svc_id, ke.host_id, ke.user_id, ke.port, ke.pid, ke.start_time, ke.end_time, ka.key_id, ka.target_svc_id, ka.subscribe_time, ka.unsubscribe_time
  • Join Filter: (ke.user_id = sl_user.id_val)
  • Rows Removed by Join Filter: 842952
2. 52.978 687.856 ↓ 2.0 2 1

Nested Loop (cost=1.42..1,608,618.79 rows=1 width=136) (actual time=485.684..687.856 rows=2 loops=1)

  • Output: ka.endpoint_id, ka.key_id, ka.target_svc_id, ka.subscribe_time, ka.unsubscribe_time, ke.svc_id, ke.host_id, ke.user_id, ke.port, ke.pid, ke.start_time, ke.end_time, sl_svc.str_val, sl_host.str_val, sl_key.str_val, sl_svc2.str_val
  • Join Filter: (ke.host_id = sl_host.id_val)
  • Rows Removed by Join Filter: 842952
3. 52.917 592.658 ↓ 2.0 2 1

Nested Loop (cost=1.42..1,596,440.89 rows=1 width=124) (actual time=485.680..592.658 rows=2 loops=1)

  • Output: ka.endpoint_id, ka.key_id, ka.target_svc_id, ka.subscribe_time, ka.unsubscribe_time, ke.svc_id, ke.host_id, ke.user_id, ke.port, ke.pid, ke.start_time, ke.end_time, sl_svc.str_val, sl_key.str_val, sl_svc2.str_val
  • Join Filter: (ke.svc_id = sl_svc.id_val)
  • Rows Removed by Join Filter: 842952
4. 0.001 496.409 ↓ 2.0 2 1

Nested Loop (cost=1.42..1,584,263.00 rows=1 width=112) (actual time=484.919..496.409 rows=2 loops=1)

  • Output: ka.endpoint_id, ka.key_id, ka.target_svc_id, ka.subscribe_time, ka.unsubscribe_time, ke.svc_id, ke.host_id, ke.user_id, ke.port, ke.pid, ke.start_time, ke.end_time, sl_key.str_val, sl_svc2.str_val
5. 0.017 0.017 ↑ 1.0 1 1

Index Scan using string_lookup_idx2 on public.stringlookup sl_svc2 (cost=0.42..8.44 rows=1 width=20) (actual time=0.017..0.017 rows=1 loops=1)

  • Output: sl_svc2.id_val, sl_svc2.str_val
  • Index Cond: (sl_svc2.str_val = 'MD'::text)
6. 0.001 496.391 ↓ 2.0 2 1

Nested Loop (cost=0.99..1,584,254.55 rows=1 width=100) (actual time=484.901..496.391 rows=2 loops=1)

  • Output: ka.endpoint_id, ka.key_id, ka.target_svc_id, ka.subscribe_time, ka.unsubscribe_time, ke.svc_id, ke.host_id, ke.user_id, ke.port, ke.pid, ke.start_time, ke.end_time, sl_key.str_val
7. 0.009 0.009 ↑ 1.0 1 1

Index Scan using string_lookup_idx2 on public.stringlookup sl_key (cost=0.42..8.44 rows=1 width=20) (actual time=0.008..0.009 rows=1 loops=1)

  • Output: sl_key.id_val, sl_key.str_val
  • Index Cond: (sl_key.str_val = 'HYH.'::text)
8. 113.376 496.381 ↑ 19,206.0 2 1

Nested Loop (cost=0.57..1,583,861.99 rows=38,412 width=88) (actual time=484.892..496.381 rows=2 loops=1)

  • Output: ka.endpoint_id, ka.key_id, ka.target_svc_id, ka.subscribe_time, ka.unsubscribe_time, ke.svc_id, ke.host_id, ke.user_id, ke.port, ke.pid, ke.start_time, ke.end_time
9. 15.027 15.027 ↓ 1.0 183,989 1

Seq Scan on public.keyendpoint ke (cost=0.00..4,514.85 rows=183,485 width=56) (actual time=0.006..15.027 rows=183,989 loops=1)

  • Output: ke.endpoint_id, ke.svc_id, ke.host_id, ke.user_id, ke.port, ke.pid, ke.start_time, ke.end_time
10. 367.978 367.978 ↓ 0.0 0 183,989

Index Scan using keyaudit_idx2 on public.keyaudit ka (cost=0.57..8.60 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=183,989)

  • Output: ka.endpoint_id, ka.key_id, ka.target_svc_id, ka.subscribe_time, ka.unsubscribe_time
  • Index Cond: ((ka.endpoint_id = ke.endpoint_id) AND (ka.target_svc_id = sl_svc2.id_val) AND (ka.key_id = sl_key.id_val))
  • Filter: (ka.subscribe_time > '2018-12-05 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 0
11. 43.332 43.332 ↓ 1.0 421,477 2

Seq Scan on public.stringlookup sl_svc (cost=0.00..6,909.62 rows=421,462 width=20) (actual time=0.004..21.666 rows=421,477 loops=2)

  • Output: sl_svc.id_val, sl_svc.str_val
12. 42.220 42.220 ↓ 1.0 421,477 2

Seq Scan on public.stringlookup sl_host (cost=0.00..6,909.62 rows=421,462 width=20) (actual time=0.002..21.110 rows=421,477 loops=2)

  • Output: sl_host.id_val, sl_host.str_val
13. 41.884 41.884 ↓ 1.0 421,477 2

Seq Scan on public.stringlookup sl_user (cost=0.00..6,909.62 rows=421,462 width=20) (actual time=0.002..20.942 rows=421,477 loops=2)

  • Output: sl_user.id_val, sl_user.str_val