explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pdf4

Settings
# exclusive inclusive rows x rows loops node
1. 0.942 3.086 ↓ 0.0 0 1

Insert on billing.billing_event_user (cost=18.63..22.89 rows=1 width=301) (actual time=3.086..3.086 rows=0 loops=1)

  • Buffers: shared hit=497 read=7 dirtied=8
2. 0.219 2.144 ↓ 47.0 47 1

Nested Loop Left Join (cost=18.63..22.89 rows=1 width=301) (actual time=1.455..2.144 rows=47 loops=1)

  • Output: nextval('billing.billing_event_user_id_seq'::regclass), '2020-07-30 13:00:00'::timestamp without time zone, '2'::bigint, '1'::bigint, NULL::character varying(40), cc.related_org_id, client_org.name, client_org.tems_customer_number, cust.id, cust.gl_legal_entity, cust.gl_cust_no, cust.name, 1.00::numeric(10,2), usr.id, usr.login_id, usr.org_id, org.name, usr.first_name, usr.last_name, usr.email, usr.state, usr.employee_id, usrx.effective_start_dtt
  • Filter: (dst.id IS NULL)
  • Buffers: shared hit=338 read=1 dirtied=1
3. 0.088 1.737 ↓ 47.0 47 1

Nested Loop (cost=18.49..21.95 rows=1 width=155) (actual time=1.368..1.737 rows=47 loops=1)

  • Output: usr.id, usr.login_id, usr.org_id, usr.first_name, usr.last_name, usr.email, usr.state, usr.employee_id, usrx.effective_start_dtt, org.name, cc.related_org_id, client_org.name, client_org.tems_customer_number, cust.id, cust.gl_legal_entity, cust.gl_cust_no, cust.name
  • Inner Unique: true
  • Buffers: shared hit=235 read=1
4. 0.107 1.475 ↓ 58.0 58 1

Hash Join (cost=18.22..20.37 rows=1 width=112) (actual time=1.351..1.475 rows=58 loops=1)

  • Output: usr.id, usr.login_id, usr.org_id, usr.first_name, usr.last_name, usr.email, usr.state, usr.employee_id, usrx.effective_start_dtt, org.name, cc.related_org_id, client_org.name, client_org.tems_customer_number, client_org.bill_to_customer_id
  • Hash Cond: (usrx.user_id = usr.id)
  • Buffers: shared hit=95
5. 0.055 0.055 ↑ 1.0 70 1

Seq Scan on users.user_scd usrx (cost=0.00..1.88 rows=70 width=16) (actual time=0.008..0.055 rows=70 loops=1)

  • Output: usrx.id, usrx.user_id, usrx.user_org_id, usrx.user_client_org_id, usrx.effective_start_dtt, usrx.effective_end_dtt, usrx.login_id, usrx.first_name, usrx.last_name
  • Filter: (usrx.effective_start_dtt < '2020-08-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=1
6. 0.075 1.313 ↓ 58.0 58 1

Hash (cost=18.21..18.21 rows=1 width=104) (actual time=1.313..1.313 rows=58 loops=1)

  • Output: usr.id, usr.login_id, usr.org_id, usr.first_name, usr.last_name, usr.email, usr.state, usr.employee_id, org.name, cc.related_org_id, client_org.name, client_org.tems_customer_number, client_org.bill_to_customer_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=91
7. 0.083 1.238 ↓ 58.0 58 1

Nested Loop (cost=16.03..18.21 rows=1 width=104) (actual time=0.919..1.238 rows=58 loops=1)

  • Output: usr.id, usr.login_id, usr.org_id, usr.first_name, usr.last_name, usr.email, usr.state, usr.employee_id, org.name, cc.related_org_id, client_org.name, client_org.tems_customer_number, client_org.bill_to_customer_id
  • Buffers: shared hit=91
8. 0.075 0.969 ↓ 62.0 62 1

Hash Join (cost=15.89..17.75 rows=1 width=51) (actual time=0.902..0.969 rows=62 loops=1)

  • Output: org.name, org.id, cc.related_org_id, cc.org_id, client_org.name, client_org.tems_customer_number, client_org.bill_to_customer_id
  • Hash Cond: (client_org.id = cc.related_org_id)
  • Buffers: shared hit=3
9. 0.019 0.019 ↑ 1.0 62 1

Seq Scan on users.organization client_org (cost=0.00..1.62 rows=62 width=31) (actual time=0.007..0.019 rows=62 loops=1)

  • Output: client_org.id, client_org.name, client_org.parent_id, client_org.enabled, client_org.location_id, client_org.tems_customer_number, client_org.bill_to_customer_id, client_org.exclusivity, client_org.hsm_slot, client_org.agreement_accepted_ts, client_org.heart_beat_period, client_org.monitor_my_terminals, client_org.idp_version, client_org.sales_director_name, client_org.sales_director_email, client_org.sales_admin_name, client_org.sales_admin_email
  • Buffers: shared hit=1
10. 0.044 0.875 ↓ 62.0 62 1

Hash (cost=15.88..15.88 rows=1 width=24) (actual time=0.874..0.875 rows=62 loops=1)

  • Output: org.name, org.id, cc.related_org_id, cc.org_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=2
11. 0.071 0.831 ↓ 62.0 62 1

Hash Join (cost=14.02..15.88 rows=1 width=24) (actual time=0.763..0.831 rows=62 loops=1)

  • Output: org.name, org.id, cc.related_org_id, cc.org_id
  • Hash Cond: (org.id = cc.org_id)
  • Buffers: shared hit=2
12. 0.017 0.017 ↑ 1.0 62 1

Seq Scan on users.organization org (cost=0.00..1.62 rows=62 width=16) (actual time=0.005..0.017 rows=62 loops=1)

  • Output: org.id, org.name, org.parent_id, org.enabled, org.location_id, org.tems_customer_number, org.bill_to_customer_id, org.exclusivity, org.hsm_slot, org.agreement_accepted_ts, org.heart_beat_period, org.monitor_my_terminals, org.idp_version, org.sales_director_name, org.sales_director_email, org.sales_admin_name, org.sales_admin_email
  • Buffers: shared hit=1
13. 0.056 0.743 ↓ 62.0 62 1

Hash (cost=14.00..14.00 rows=1 width=8) (actual time=0.743..0.743 rows=62 loops=1)

  • Output: cc.related_org_id, cc.org_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
14. 0.056 0.687 ↓ 62.0 62 1

Subquery Scan on cc (cost=8.67..14.00 rows=1 width=8) (actual time=0.217..0.687 rows=62 loops=1)

  • Output: cc.related_org_id, cc.org_id
  • Filter: (cc.rr = 1)
  • Rows Removed by Filter: 102
  • Buffers: shared hit=1
15. 0.396 0.631 ↑ 1.0 164 1

WindowAgg (cost=8.67..11.95 rows=164 width=20) (actual time=0.216..0.631 rows=164 loops=1)

  • Output: mv_org_tree.org_id, mv_org_tree.related_org_id, rank() OVER (?), mv_org_tree.distance
  • Buffers: shared hit=1
16. 0.172 0.235 ↑ 1.0 164 1

Sort (cost=8.67..9.08 rows=164 width=12) (actual time=0.207..0.235 rows=164 loops=1)

  • Output: mv_org_tree.org_id, mv_org_tree.distance, mv_org_tree.related_org_id
  • Sort Key: mv_org_tree.org_id, mv_org_tree.distance
  • Sort Method: quicksort Memory: 32kB
  • Buffers: shared hit=1
17. 0.063 0.063 ↑ 1.0 164 1

Seq Scan on users.mv_org_tree (cost=0.00..2.64 rows=164 width=12) (actual time=0.009..0.063 rows=164 loops=1)

  • Output: mv_org_tree.org_id, mv_org_tree.distance, mv_org_tree.related_org_id
  • Buffers: shared hit=1
18. 0.186 0.186 ↑ 2.0 1 62

Index Scan using user_nk1 on users."user" usr (cost=0.14..0.43 rows=2 width=61) (actual time=0.002..0.003 rows=1 loops=62)

  • Output: usr.id, usr.login_id, usr.language, usr.org_id, usr.first_name, usr.last_name, usr.email, usr.last_login, usr.enabled, usr.state, usr.state_change_ts, usr.agreement_accepted_ts, usr.employee_id, usr.public_key, usr.encrypt_req, usr.sso_session_id
  • Index Cond: (usr.org_id = org.id)
  • Filter: usr.enabled
  • Rows Removed by Filter: 0
  • Buffers: shared hit=88
19. 0.174 0.174 ↑ 1.0 1 58

Index Scan using customer_pkey on users.customer cust (cost=0.28..1.58 rows=1 width=51) (actual time=0.003..0.003 rows=1 loops=58)

  • Output: cust.id, cust.gl_legal_entity, cust.gl_cust_no, cust.name, cust.address, cust.address_2, cust.city, cust.postal_code, cust.blocked, cust.sim_auto_activation, cust.sim_activate_graceperiod_days, cust.sim_suspend_requests, cust.sim_suspend_days_max, cust.sim_guaranteed_cycle_max, cust.single_multi_src_invoice_ind, cust.create_dtt, cust.last_upd_dtt, cust.payment_terms_code
  • Index Cond: (cust.id = client_org.bill_to_customer_id)
  • Buffers: shared hit=140 read=1
20. 0.047 0.188 ↓ 0.0 0 47

Append (cost=0.14..0.93 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=47)

  • Buffers: shared hit=48
21. 0.141 0.141 ↓ 0.0 0 47

Index Scan using billing_event_user_p2020_07_billing_cycle_id_cycle_run_dtt__idx on billing.billing_event_user_p2020_07 dst (cost=0.14..0.92 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=47)

  • Output: dst.user_id, dst.id
  • Index Cond: ((dst.billing_cycle_id = '2'::bigint) AND (dst.cycle_run_dtt = '2020-07-30 13:00:00'::timestamp without time zone) AND (dst.user_id = usr.id))
  • Buffers: shared hit=48