explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dIW4

Settings
# exclusive inclusive rows x rows loops node
1. 15.021 1,583.651 ↓ 0.0 0 1

Insert on billing.billing_event_terminal_monitoring (cost=4,750.08..5,197.49 rows=1 width=1,804) (actual time=1,583.651..1,583.651 rows=0 loops=1)

  • Buffers: shared hit=31,456 read=43 dirtied=44
2.          

CTE _filtered_devices

3. 19.576 95.216 ↑ 3.5 11,106 1

Hash Join (cost=426.88..4,739.56 rows=38,319 width=40) (actual time=13.679..95.216 rows=11,106 loops=1)

  • Output: dev_1.id, dev_1.org_id, dev_1.device_id, dev_1.serial_number, dev_1.type_id, dh.history_dtt
  • Inner Unique: true
  • Hash Cond: (dh.device_id = dev_1.id)
  • Buffers: shared hit=2,671
4. 9.726 62.012 ↑ 1.0 38,313 1

Append (cost=0.00..4,212.06 rows=38,319 width=16) (actual time=0.007..62.012 rows=38,313 loops=1)

  • Buffers: shared hit=2,494
5. 0.010 0.010 ↓ 3.0 3 1

Seq Scan on users.device_history_p2020_02 dh (cost=0.00..1.04 rows=1 width=16) (actual time=0.006..0.010 rows=3 loops=1)

  • Output: dh.history_dtt, dh.device_id
  • Filter: ((dh.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((dh.device_event_type)::text = 'ADDITION'::text))
  • Buffers: shared hit=1
6. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on users.device_history_p2020_03 dh_1 (cost=0.00..1.01 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=1)

  • Output: dh_1.history_dtt, dh_1.device_id
  • Filter: ((dh_1.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((dh_1.device_event_type)::text = 'ADDITION'::text))
  • Buffers: shared hit=1
7. 0.005 0.005 ↓ 2.0 2 1

Seq Scan on users.device_history_p2020_04 dh_2 (cost=0.00..1.03 rows=1 width=16) (actual time=0.004..0.005 rows=2 loops=1)

  • Output: dh_2.history_dtt, dh_2.device_id
  • Filter: ((dh_2.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((dh_2.device_event_type)::text = 'ADDITION'::text))
  • Buffers: shared hit=1
8. 47.907 47.907 ↑ 1.0 33,271 1

Seq Scan on users.device_history_p2020_05 dh_3 (cost=0.00..3,750.56 rows=33,279 width=16) (actual time=0.007..47.907 rows=33,271 loops=1)

  • Output: dh_3.history_dtt, dh_3.device_id
  • Filter: ((dh_3.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((dh_3.device_event_type)::text = 'ADDITION'::text))
  • Rows Removed by Filter: 61,433
  • Buffers: shared hit=2,330
9. 0.438 0.438 ↑ 1.0 8 1

Seq Scan on users.device_history_p2020_06 dh_4 (cost=0.00..48.24 rows=8 width=16) (actual time=0.011..0.438 rows=8 loops=1)

  • Output: dh_4.history_dtt, dh_4.device_id
  • Filter: ((dh_4.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((dh_4.device_event_type)::text = 'ADDITION'::text))
  • Rows Removed by Filter: 1,008
  • Buffers: shared hit=33
10. 0.400 0.400 ↓ 0.0 0 1

Seq Scan on users.device_history_p2020_07 dh_5 (cost=0.00..46.15 rows=1 width=16) (actual time=0.400..0.400 rows=0 loops=1)

  • Output: dh_5.history_dtt, dh_5.device_id
  • Filter: ((dh_5.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((dh_5.device_event_type)::text = 'ADDITION'::text))
  • Rows Removed by Filter: 1,010
  • Buffers: shared hit=31
11. 3.521 3.521 ↑ 1.0 5,028 1

Seq Scan on users.device_history_default dh_6 (cost=0.00..172.42 rows=5,028 width=16) (actual time=0.011..3.521 rows=5,028 loops=1)

  • Output: dh_6.history_dtt, dh_6.device_id
  • Filter: ((dh_6.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((dh_6.device_event_type)::text = 'ADDITION'::text))
  • Buffers: shared hit=97
12. 7.384 13.628 ↑ 1.0 11,106 1

Hash (cost=288.06..288.06 rows=11,106 width=32) (actual time=13.628..13.628 rows=11,106 loops=1)

  • Output: dev_1.id, dev_1.org_id, dev_1.device_id, dev_1.serial_number, dev_1.type_id
  • Buckets: 16,384 Batches: 1 Memory Usage: 803kB
  • Buffers: shared hit=177
13. 6.244 6.244 ↑ 1.0 11,106 1

Seq Scan on users.device dev_1 (cost=0.00..288.06 rows=11,106 width=32) (actual time=0.007..6.244 rows=11,106 loops=1)

  • Output: dev_1.id, dev_1.org_id, dev_1.device_id, dev_1.serial_number, dev_1.type_id
  • Buffers: shared hit=177
14. 58.814 1,568.630 ↓ 1,027.0 1,027 1

Nested Loop (cost=10.52..457.93 rows=1 width=1,804) (actual time=131.837..1,568.630 rows=1,027 loops=1)

  • Output: nextval('billing.billing_event_terminal_monitoring_id_seq'::regclass), '2020-07-30 18:00:00'::timestamp without time zone, '1'::bigint, '3'::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), dev.id, dev.org_id, org.name, dev.device_id, dev.serial_number, dev.type_id, dt.name, dev.device_history_addition_dtt
  • Inner Unique: true
  • Join Filter: (dev.org_id = org.id)
  • Rows Removed by Join Filter: 146,342
  • Buffers: shared hit=26,773
15. 9.565 1,209.981 ↓ 11,105.0 11,105 1

Nested Loop Left Join (cost=10.52..456.13 rows=1 width=1,650) (actual time=131.695..1,209.981 rows=11,105 loops=1)

  • Output: dev.id, dev.org_id, dev.device_id, dev.serial_number, dev.type_id, dev.device_history_addition_dtt, dt.name, cc.related_org_id, cc.org_id, client_org.name, client_org.tems_customer_number, cust.id, cust.gl_legal_entity, cust.gl_cust_no, cust.name
  • Filter: (dst.id IS NULL)
  • Buffers: shared hit=14,630
16. 8.364 1,155.996 ↓ 1,586.4 11,105 1

Merge Join (cost=10.39..454.90 rows=7 width=1,650) (actual time=131.684..1,155.996 rows=11,105 loops=1)

  • Output: dev.id, dev.org_id, dev.device_id, dev.serial_number, dev.type_id, dev.device_history_addition_dtt, dt.name, cc.related_org_id, cc.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=2,721
17. 190.838 1,147.205 ↓ 1,586.6 11,106 1

Nested Loop (cost=9.97..915.03 rows=7 width=1,607) (actual time=131.639..1,147.205 rows=11,106 loops=1)

  • Output: dev.id, dev.org_id, dev.device_id, dev.serial_number, dev.type_id, dev.device_history_addition_dtt, dt.name, cc.related_org_id, cc.org_id, client_org.name, client_org.tems_customer_number, client_org.bill_to_customer_id
  • Join Filter: (dev.org_id = cc.org_id)
  • Rows Removed by Join Filter: 660,308
  • Buffers: shared hit=2,675
18. 0.868 2.022 ↓ 61.0 61 1

Nested Loop (cost=8.81..28.01 rows=1 width=35) (actual time=0.350..2.022 rows=61 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: 2,420
  • Buffers: shared hit=3
19. 0.047 0.047 ↑ 1.5 41 1

Index Scan using organization_ak1 on users.organization client_org (cost=0.14..13.07 rows=62 width=31) (actual time=0.005..0.047 rows=41 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
20. 0.397 1.107 ↓ 61.0 61 41

Materialize (cost=8.67..14.01 rows=1 width=8) (actual time=0.006..0.027 rows=61 loops=41)

  • Output: cc.related_org_id, cc.org_id
  • Buffers: shared hit=1
21. 0.054 0.710 ↓ 62.0 62 1

Subquery Scan on cc (cost=8.67..14.00 rows=1 width=8) (actual time=0.239..0.710 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
22. 0.397 0.656 ↑ 1.0 164 1

WindowAgg (cost=8.67..11.95 rows=164 width=20) (actual time=0.237..0.656 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
23. 0.189 0.259 ↑ 1.0 164 1

Sort (cost=8.67..9.08 rows=164 width=12) (actual time=0.229..0.259 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
24. 0.070 0.070 ↑ 1.0 164 1

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

  • Output: mv_org_tree.org_id, mv_org_tree.distance, mv_org_tree.related_org_id
  • Buffers: shared hit=1
25. 637.251 954.345 ↓ 8.2 11,007 61

Hash Join (cost=1.16..870.26 rows=1,341 width=1,572) (actual time=0.226..15.645 rows=11,007 loops=61)

  • Output: dev.id, dev.org_id, dev.device_id, dev.serial_number, dev.type_id, dev.device_history_addition_dtt, dt.name
  • Inner Unique: true
  • Hash Cond: (dev.type_id = dt.id)
  • Buffers: shared hit=2,672
26. 317.078 317.078 ↑ 3.5 11,007 61

CTE Scan on _filtered_devices dev (cost=0.00..766.38 rows=38,319 width=1,056) (actual time=0.225..5.198 rows=11,007 loops=61)

  • Output: dev.id, dev.org_id, dev.device_id, dev.serial_number, dev.type_id, dev.device_history_addition_dtt
  • Buffers: shared hit=2,671
27. 0.006 0.016 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=520) (actual time=0.016..0.016 rows=7 loops=1)

  • Output: dt.name, dt.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
28. 0.010 0.010 ↑ 1.0 7 1

Seq Scan on users.device_type dt (cost=0.00..1.07 rows=7 width=520) (actual time=0.007..0.010 rows=7 loops=1)

  • Output: dt.name, dt.id
  • Buffers: shared hit=1
29. 0.427 0.427 ↑ 1.0 808 1

Index Scan using customer_pkey on users.customer cust (cost=0.28..54.98 rows=809 width=51) (actual time=0.018..0.427 rows=808 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
30. 11.105 44.420 ↓ 0.0 0 11,105

Append (cost=0.14..0.17 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=11,105)

  • Buffers: shared hit=11,909
31. 33.315 33.315 ↓ 0.0 0 11,105

Index Scan using billing_event_terminal_monito_billing_cycle_id_cycle_run_d_idx3 on billing.billing_event_terminal_monitoring_p2020_07 dst (cost=0.14..0.16 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=11,105)

  • Output: dst.device_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.device_id = dev.id))
  • Buffers: shared hit=11,909
32. 299.835 299.835 ↑ 1.1 13 11,105

Seq Scan on users.organization org (cost=0.00..1.62 rows=14 width=16) (actual time=0.005..0.027 rows=13 loops=11,105)

  • 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
  • Filter: org.monitor_my_terminals
  • Rows Removed by Filter: 47