explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q9JN

Settings
# exclusive inclusive rows x rows loops node
1. 283.398 7,360.860 ↓ 0.0 0 1

Insert on billing.billing_event_software_download (cost=9.37..2,942.84 rows=1 width=306) (actual time=7,360.860..7,360.860 rows=0 loops=1)

  • Buffers: shared hit=417,569 read=776 dirtied=777
2. 62.316 7,077.462 ↓ 19,221.0 19,221 1

Nested Loop Left Join (cost=9.37..2,942.84 rows=1 width=306) (actual time=2,099.096..7,077.462 rows=19,221 loops=1)

  • Output: nextval('billing.billing_event_software_download_id_seq'::regclass), '2020-07-30 18:00:00'::timestamp without time zone, '1'::bigint, '4'::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), el.id, el.org_id, org.name, el.device_id, el.device_sn, el.event_execution_dtt, el.dataset_name, el.dataset_version, el.create_dtt
  • Join Filter: (dst.software_download_id = el.id)
  • Filter: (dst.id IS NULL)
  • Buffers: shared hit=320,603
3. 14.570 7,015.146 ↓ 158.9 19,221 1

Merge Join (cost=9.23..2,932.86 rows=121 width=160) (actual time=2,099.002..7,015.146 rows=19,221 loops=1)

  • Output: el.id, el.org_id, el.device_id, el.device_sn, el.event_execution_dtt, el.dataset_name, el.dataset_version, el.create_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
  • Merge Cond: (client_org.bill_to_customer_id = cust.id)
  • Buffers: shared hit=301,370
4. 288.673 7,000.168 ↓ 158.9 19,221 1

Nested Loop (cost=8.81..6,809.50 rows=121 width=117) (actual time=2,098.466..7,000.168 rows=19,221 loops=1)

  • Output: el.id, el.org_id, el.device_id, el.device_sn, el.event_execution_dtt, el.dataset_name, el.dataset_version, el.create_dtt, org.name, cc.related_org_id, client_org.name, client_org.tems_customer_number, client_org.bill_to_customer_id
  • Inner Unique: true
  • Join Filter: (el.org_id = org.id)
  • Rows Removed by Join Filter: 749,618
  • Buffers: shared hit=301,324
5. 350.695 6,576.948 ↓ 409.0 19,221 1

Nested Loop (cost=8.81..6,764.92 rows=47 width=109) (actual time=2,098.411..6,576.948 rows=19,221 loops=1)

  • Output: el.id, el.org_id, el.device_id, el.device_sn, el.event_execution_dtt, el.dataset_name, el.dataset_version, el.create_dtt, cc.related_org_id, cc.org_id, client_org.name, client_org.tems_customer_number, client_org.bill_to_customer_id
  • Join Filter: (cc.org_id = el.org_id)
  • Rows Removed by Join Filter: 1,172,481
  • Buffers: shared hit=301,323
6. 1.332 2.941 ↓ 62.0 62 1

Nested Loop (cost=8.81..28.01 rows=1 width=35) (actual time=0.377..2.941 rows=62 loops=1)

  • Output: cc.related_org_id, cc.org_id, client_org.name, client_org.tems_customer_number, client_org.bill_to_customer_id
  • Join Filter: (cc.related_org_id = client_org.id)
  • Rows Removed by Join Filter: 3,782
  • Buffers: shared hit=3
7. 0.121 0.121 ↑ 1.0 62 1

Index Scan using organization_ak1 on users.organization client_org (cost=0.14..13.07 rows=62 width=31) (actual time=0.004..0.121 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=2
8. 0.766 1.488 ↓ 62.0 62 62

Materialize (cost=8.67..14.01 rows=1 width=8) (actual time=0.004..0.024 rows=62 loops=62)

  • Output: cc.related_org_id, cc.org_id
  • Buffers: shared hit=1
9. 0.053 0.722 ↓ 62.0 62 1

Subquery Scan on cc (cost=8.67..14.00 rows=1 width=8) (actual time=0.265..0.722 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
10. 0.384 0.669 ↑ 1.0 164 1

WindowAgg (cost=8.67..11.95 rows=164 width=20) (actual time=0.264..0.669 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
11. 0.221 0.285 ↑ 1.0 164 1

Sort (cost=8.67..9.08 rows=164 width=12) (actual time=0.256..0.285 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
12. 0.064 0.064 ↑ 1.0 164 1

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

  • Output: mv_org_tree.org_id, mv_org_tree.distance, mv_org_tree.related_org_id
  • Buffers: shared hit=1
13. 6,223.312 6,223.312 ↓ 2.6 19,221 62

Seq Scan on mgmtplan.event_log el (cost=0.00..6,643.03 rows=7,510 width=74) (actual time=2.051..100.376 rows=19,221 loops=62)

  • Output: el.id, el.org_id, el.device_id, el.device_sn, el.event_execution_dtt, el.result, el.dataset_name, el.dataset_type, el.dataset_version, el.addl_error_info, el.dataset_create_dtt, el.action_type, el.management_action_id, el.device_history_id, el.device_history_dtt, el.device_history_device_event_type, el.device_history_org_id, el.device_history_device_id2, el.device_history_serial_number, el.device_history_cpu_id, el.device_history_lane_id, el.device_history_device_state_id, el.device_history_type_id, el.device_history_store_id, el.device_history_timezone, el.device_history_timezone_source, el.device_history_online, el.device_history_store_name, el.device_history_org_name, el.device_history_user_scd_id, el.device_history_login_id, el.device_history_first_name, el.device_history_last_name, el.create_dtt
  • Filter: ((el.create_dtt >= '2000-01-01 00:00:00'::timestamp without time zone) AND (el.create_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((el.result)::text = 'SUCC'::text) AND ((el.action_type)::text = 'DWNL'::text) AND ((el.dataset_type)::text = 'SWPK'::text))
  • Rows Removed by Filter: 60,025
  • Buffers: shared hit=301,320
14. 134.521 134.547 ↑ 1.6 40 19,221

Materialize (cost=0.00..1.93 rows=62 width=16) (actual time=0.000..0.007 rows=40 loops=19,221)

  • Output: org.name, org.id
  • Buffers: shared hit=1
15. 0.026 0.026 ↑ 1.2 51 1

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

  • Output: org.name, org.id
  • Buffers: shared hit=1
16. 0.408 0.408 ↑ 1.0 780 1

Index Scan using customer_pkey on users.customer cust (cost=0.28..54.98 rows=809 width=51) (actual time=0.016..0.408 rows=780 loops=1)

  • 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
  • Buffers: shared hit=46
17. 0.000 0.000 ↓ 0.0 0 19,221

Materialize (cost=0.14..8.17 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=19,221)

  • Output: dst.software_download_id, dst.id
  • Buffers: shared hit=1
18. 0.001 0.019 ↓ 0.0 0 1

Append (cost=0.14..8.16 rows=1 width=16) (actual time=0.019..0.019 rows=0 loops=1)

  • Buffers: shared hit=1
19. 0.018 0.018 ↓ 0.0 0 1

Index Scan using billing_event_software_downlo_billing_cycle_id_cycle_run_d_idx3 on billing.billing_event_software_download_p2020_07 dst (cost=0.14..8.16 rows=1 width=16) (actual time=0.017..0.018 rows=0 loops=1)

  • Output: dst.software_download_id, dst.id
  • Index Cond: ((dst.billing_cycle_id = '1'::bigint) AND (dst.cycle_run_dtt = '2020-07-30 18:00:00'::timestamp without time zone))
  • Buffers: shared hit=1