explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xWRO

Settings
# exclusive inclusive rows x rows loops node
1. 151.252 1,196.431 ↓ 0.0 0 1

Insert on billing.billing_event_application_licensing (cost=907.33..1,098.43 rows=1 width=856) (actual time=1,196.431..1,196.431 rows=0 loops=1)

  • Buffers: shared hit=63,294 read=518 dirtied=398
2.          

CTE _filtered_device_components

3. 6.864 18.903 ↓ 1.0 10,442 1

Hash Left Join (cost=350.20..895.57 rows=10,441 width=43) (actual time=3.435..18.903 rows=10,442 loops=1)

  • Output: dc_1.id, dc_1.org_id, dc_1.comp_ident_identification, dc_1.status_version, dch.history_dtt
  • Hash Cond: (dc_1.id = dch.device_component_id)
  • Filter: ((dch.history_dtt IS NULL) OR (dch.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=245 read=119
4. 8.699 10.925 ↑ 1.0 10,442 1

Bitmap Heap Scan on mgmtplan.device_component dc_1 (cost=201.21..688.84 rows=10,442 width=35) (actual time=2.314..10.925 rows=10,442 loops=1)

  • Output: dc_1.id, dc_1.org_id, dc_1.device_id, dc_1.component_type, dc_1.comp_ident_item_number, dc_1.comp_ident_provider_ident, dc_1.comp_ident_identification, dc_1.comp_ident_serial_number, dc_1.status_version, dc_1.status_status, dc_1.status_expiry_dt
  • Recheck Cond: ((dc_1.component_type)::text = 'APLI'::text)
  • Filter: ((dc_1.status_status)::text = 'OPER'::text)
  • Heap Blocks: exact=328
  • Buffers: shared hit=242 read=116
5. 2.226 2.226 ↑ 1.0 10,442 1

Bitmap Index Scan on component_type_device_component_fk (cost=0.00..198.60 rows=10,442 width=0) (actual time=2.226..2.226 rows=10,442 loops=1)

  • Index Cond: ((dc_1.component_type)::text = 'APLI'::text)
  • Buffers: shared read=30
6. 0.474 1.114 ↑ 1.1 992 1

Hash (cost=135.46..135.46 rows=1,082 width=16) (actual time=1.114..1.114 rows=992 loops=1)

  • Output: dch.history_dtt, dch.device_component_id
  • Buckets: 2,048 Batches: 1 Memory Usage: 63kB
  • Buffers: shared hit=3 read=3
7. 0.251 0.640 ↑ 1.1 992 1

Append (cost=0.00..135.46 rows=1,082 width=16) (actual time=0.038..0.640 rows=992 loops=1)

  • Buffers: shared hit=3 read=3
8. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on mgmtplan.device_component_history_p2020_02 dch (cost=0.00..10.10 rows=10 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: dch.history_dtt, dch.device_component_id
9. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on mgmtplan.device_component_history_p2020_03 dch_1 (cost=0.00..10.10 rows=10 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: dch_1.history_dtt, dch_1.device_component_id
10. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on mgmtplan.device_component_history_p2020_04 dch_2 (cost=0.00..10.10 rows=10 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: dch_2.history_dtt, dch_2.device_component_id
11. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on mgmtplan.device_component_history_p2020_05 dch_3 (cost=0.00..10.10 rows=10 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: dch_3.history_dtt, dch_3.device_component_id
12. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on mgmtplan.device_component_history_p2020_06 dch_4 (cost=0.00..10.10 rows=10 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: dch_4.history_dtt, dch_4.device_component_id
13. 0.380 0.380 ↑ 1.0 992 1

Index Only Scan using device_component_history_p202_history_dtt_device_component_idx5 on mgmtplan.device_component_history_p2020_07 dch_5 (cost=0.28..39.16 rows=992 width=16) (actual time=0.031..0.380 rows=992 loops=1)

  • Output: dch_5.history_dtt, dch_5.device_component_id
  • Heap Fetches: 0
  • Buffers: shared hit=3 read=3
14. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on mgmtplan.device_component_history_p2020_08 dch_6 (cost=0.00..10.10 rows=10 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: dch_6.history_dtt, dch_6.device_component_id
15. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on mgmtplan.device_component_history_p2020_09 dch_7 (cost=0.00..10.10 rows=10 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: dch_7.history_dtt, dch_7.device_component_id
16. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on mgmtplan.device_component_history_p2020_10 dch_8 (cost=0.00..10.10 rows=10 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: dch_8.history_dtt, dch_8.device_component_id
17. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on mgmtplan.device_component_history_default dch_9 (cost=0.00..10.10 rows=10 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: dch_9.history_dtt, dch_9.device_component_id
18. 32.373 1,045.179 ↓ 10,442.0 10,442 1

Nested Loop Left Join (cost=11.76..202.86 rows=1 width=856) (actual time=223.931..1,045.179 rows=10,442 loops=1)

  • Output: nextval('billing.billing_event_application_licensing_id_seq'::regclass), '2020-07-31 13:00:00'::timestamp without time zone, '1'::bigint, '10'::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), dc.id, dc.org_id, org.name, dc.comp_ident_identification, dc.status_version, dc.device_component_history_dtt
  • Join Filter: (dst.app_licensing_id = dc.id)
  • Filter: (dst.id IS NULL)
  • Buffers: shared hit=10,748 read=120 dirtied=1
19. 7.535 1,012.806 ↓ 200.8 10,442 1

Merge Join (cost=11.62..193.91 rows=52 width=710) (actual time=223.799..1,012.806 rows=10,442 loops=1)

  • Output: dc.id, dc.org_id, dc.comp_ident_identification, dc.status_version, dc.device_component_history_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=295 read=119
20. 186.175 1,004.848 ↓ 200.8 10,442 1

Nested Loop (cost=11.21..307.67 rows=52 width=667) (actual time=223.586..1,004.848 rows=10,442 loops=1)

  • Output: dc.id, dc.org_id, dc.comp_ident_identification, dc.status_version, dc.device_component_history_dtt, org.name, cc.related_org_id, client_org.name, client_org.tems_customer_number, client_org.bill_to_customer_id
  • Join Filter: (org.id = cc.org_id)
  • Rows Removed by Join Filter: 636,962
  • Buffers: shared hit=249 read=119
21. 1.298 2.753 ↓ 62.0 62 1

Nested Loop (cost=8.81..28.01 rows=1 width=35) (actual time=0.370..2.753 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
22. 0.091 0.091 ↑ 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.091 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
23. 0.670 1.364 ↓ 62.0 62 62

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

  • Output: cc.related_org_id, cc.org_id
  • Buffers: shared hit=1
24. 0.051 0.694 ↓ 62.0 62 1

Subquery Scan on cc (cost=8.67..14.00 rows=1 width=8) (actual time=0.260..0.694 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
25. 0.363 0.643 ↑ 1.0 164 1

WindowAgg (cost=8.67..11.95 rows=164 width=20) (actual time=0.259..0.643 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
26. 0.180 0.280 ↑ 1.0 164 1

Sort (cost=8.67..9.08 rows=164 width=12) (actual time=0.252..0.280 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
27. 0.100 0.100 ↑ 1.0 164 1

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

  • Output: mv_org_tree.org_id, mv_org_tree.distance, mv_org_tree.related_org_id
  • Buffers: shared hit=1
28. 595.701 815.920 ↓ 3.2 10,442 62

Hash Join (cost=2.40..239.20 rows=3,237 width=640) (actual time=0.058..13.160 rows=10,442 loops=62)

  • Output: dc.id, dc.org_id, dc.comp_ident_identification, dc.status_version, dc.device_component_history_dtt, org.name, org.id
  • Inner Unique: true
  • Hash Cond: (dc.org_id = org.id)
  • Buffers: shared hit=246 read=119
29. 220.162 220.162 ↓ 1.0 10,442 62

CTE Scan on _filtered_device_components dc (cost=0.00..208.82 rows=10,441 width=624) (actual time=0.056..3.551 rows=10,442 loops=62)

  • Output: dc.id, dc.org_id, dc.comp_ident_identification, dc.status_version, dc.device_component_history_dtt
  • Buffers: shared hit=245 read=119
30. 0.031 0.057 ↑ 1.0 62 1

Hash (cost=1.62..1.62 rows=62 width=16) (actual time=0.057..0.057 rows=62 loops=1)

  • Output: org.name, org.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=1
31. 0.026 0.026 ↑ 1.0 62 1

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

  • Output: org.name, org.id
  • Buffers: shared hit=1
32. 0.423 0.423 ↑ 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.026..0.423 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
33. 0.000 0.000 ↓ 0.0 0 10,442

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

  • Output: dst.app_licensing_id, dst.id
  • Buffers: shared hit=1
34. 0.001 0.007 ↓ 0.0 0 1

Append (cost=0.14..8.17 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=1)

  • Buffers: shared hit=1
35. 0.006 0.006 ↓ 0.0 0 1

Index Scan using billing_event_application_lic_billing_cycle_id_cycle_run_d_idx3 on billing.billing_event_application_licensing_p2020_07 dst (cost=0.14..8.16 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=1)

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