explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QJfI

Settings
# exclusive inclusive rows x rows loops node
1. 156.518 682.583 ↓ 0.0 0 1

Insert on billing.billing_event_application_licensing (cost=980.01..1,070.68 rows=1 width=856) (actual time=682.583..682.583 rows=0 loops=1)

  • Buffers: shared hit=146,233 read=769 dirtied=376
2.          

CTE _filtered_device_components

3. 7.493 29.588 ↓ 3.0 10,442 1

Hash Right Join (cost=819.37..965.44 rows=3,515 width=43) (actual time=21.211..29.588 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
  • Inner Unique: true
  • Hash Cond: (dch.device_component_id = dc_1.id)
  • Filter: ((dch.history_dtt IS NULL) OR (dch.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=1 read=394
4. 0.247 0.975 ↑ 1.1 992 1

Append (cost=0.00..143.23 rows=1,082 width=16) (actual time=0.027..0.975 rows=992 loops=1)

  • Buffers: shared hit=1 read=36
5. 0.003 0.003 ↓ 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.003..0.003 rows=0 loops=1)

  • Output: dch.history_dtt, dch.device_component_id
6. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=1)

  • Output: dch_1.history_dtt, dch_1.device_component_id
7. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=1)

  • Output: dch_2.history_dtt, dch_2.device_component_id
8. 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
9. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=1)

  • Output: dch_4.history_dtt, dch_4.device_component_id
10. 0.710 0.710 ↑ 1.0 992 1

Seq Scan on mgmtplan.device_component_history_p2020_07 dch_5 (cost=0.00..46.92 rows=992 width=16) (actual time=0.015..0.710 rows=992 loops=1)

  • Output: dch_5.history_dtt, dch_5.device_component_id
  • Buffers: shared hit=1 read=36
11. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=1)

  • Output: dch_6.history_dtt, dch_6.device_component_id
12. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=1)

  • Output: dch_7.history_dtt, dch_7.device_component_id
13. 0.003 0.003 ↓ 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.003..0.003 rows=0 loops=1)

  • Output: dch_8.history_dtt, dch_8.device_component_id
14. 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
15. 6.989 21.120 ↑ 1.0 10,442 1

Hash (cost=688.84..688.84 rows=10,442 width=35) (actual time=21.120..21.120 rows=10,442 loops=1)

  • Output: dc_1.id, dc_1.org_id, dc_1.comp_ident_identification, dc_1.status_version
  • Buckets: 16,384 Batches: 1 Memory Usage: 815kB
  • Buffers: shared read=358
16. 11.869 14.131 ↑ 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.370..14.131 rows=10,442 loops=1)

  • Output: dc_1.id, dc_1.org_id, dc_1.comp_ident_identification, dc_1.status_version
  • Recheck Cond: ((dc_1.component_type)::text = 'APLI'::text)
  • Filter: ((dc_1.status_status)::text = 'OPER'::text)
  • Heap Blocks: exact=328
  • Buffers: shared read=358
17. 2.262 2.262 ↑ 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.262..2.262 rows=10,442 loops=1)

  • Index Cond: ((dc_1.component_type)::text = 'APLI'::text)
  • Buffers: shared read=30
18. 44.113 526.065 ↓ 10,442.0 10,442 1

Nested Loop (cost=14.57..105.24 rows=1 width=856) (actual time=22.094..526.065 rows=10,442 loops=1)

  • Output: nextval('billing.billing_event_application_licensing_id_seq'::regclass), '2020-07-30 18: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
  • Inner Unique: true
  • Buffers: shared hit=93,819 read=394
19. 151.983 450.626 ↓ 10,442.0 10,442 1

Nested Loop (cost=14.30..103.65 rows=1 width=667) (actual time=22.009..450.626 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
  • Inner Unique: true
  • Join Filter: (cc.related_org_id = client_org.id)
  • Rows Removed by Join Filter: 408,944
  • Buffers: shared hit=52,040 read=394
20. 10.783 173.339 ↓ 10,442.0 10,442 1

Nested Loop (cost=14.30..101.26 rows=1 width=640) (actual time=21.974..173.339 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
  • Inner Unique: true
  • Buffers: shared hit=41,598 read=394
21. 9.563 131.230 ↓ 10,442.0 10,442 1

Nested Loop Left Join (cost=14.16..101.10 rows=1 width=632) (actual time=21.961..131.230 rows=10,442 loops=1)

  • Output: dc.id, dc.org_id, dc.comp_ident_identification, dc.status_version, dc.device_component_history_dtt, cc.related_org_id, cc.org_id
  • Filter: (dst.id IS NULL)
  • Buffers: shared hit=20,714 read=394
22. 14.370 59.015 ↓ 580.1 10,442 1

Hash Join (cost=14.02..97.68 rows=18 width=632) (actual time=21.930..59.015 rows=10,442 loops=1)

  • Output: dc.id, dc.org_id, dc.comp_ident_identification, dc.status_version, dc.device_component_history_dtt, cc.related_org_id, cc.org_id
  • Hash Cond: (dc.org_id = cc.org_id)
  • Buffers: shared hit=2 read=394
23. 43.958 43.958 ↓ 3.0 10,442 1

CTE Scan on _filtered_device_components dc (cost=0.00..70.30 rows=3,515 width=624) (actual time=21.213..43.958 rows=10,442 loops=1)

  • Output: dc.id, dc.org_id, dc.comp_ident_identification, dc.status_version, dc.device_component_history_dtt
  • Buffers: shared hit=1 read=394
24. 0.034 0.687 ↓ 62.0 62 1

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

  • Output: cc.related_org_id, cc.org_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
25. 0.053 0.653 ↓ 62.0 62 1

Subquery Scan on cc (cost=8.67..14.00 rows=1 width=8) (actual time=0.227..0.653 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
26. 0.358 0.600 ↑ 1.0 164 1

WindowAgg (cost=8.67..11.95 rows=164 width=20) (actual time=0.226..0.600 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
27. 0.166 0.242 ↑ 1.0 164 1

Sort (cost=8.67..9.08 rows=164 width=12) (actual time=0.216..0.242 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
28. 0.076 0.076 ↑ 1.0 164 1

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

  • Output: mv_org_tree.org_id, mv_org_tree.distance, mv_org_tree.related_org_id
  • Buffers: shared hit=1
29. 10.442 62.652 ↓ 0.0 0 10,442

Append (cost=0.14..0.18 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=10,442)

  • Buffers: shared hit=20,712
30. 52.210 52.210 ↓ 0.0 0 10,442

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..0.18 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=10,442)

  • Output: dst.app_licensing_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) AND (dst.app_licensing_id = dc.id))
  • Buffers: shared hit=20,712
31. 31.326 31.326 ↑ 1.0 1 10,442

Index Scan using organization_pk on users.organization org (cost=0.14..0.16 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=10,442)

  • 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
  • Index Cond: (org.id = dc.org_id)
  • Buffers: shared hit=20,884
32. 125.304 125.304 ↑ 1.6 40 10,442

Seq Scan on users.organization client_org (cost=0.00..1.62 rows=62 width=31) (actual time=0.005..0.012 rows=40 loops=10,442)

  • 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=10,442
33. 31.326 31.326 ↑ 1.0 1 10,442

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=10,442)

  • 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=31,326