explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z2LJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 1,883.906 ↓ 15.0 15 1

Nested Loop (cost=9,616.62..224,720.51 rows=1 width=4) (actual time=112.337..1,883.906 rows=15 loops=1)

2. 15.372 1,883.762 ↓ 14.7 44 1

Hash Semi Join (cost=9,616.48..224,719.93 rows=3 width=9) (actual time=112.316..1,883.762 rows=44 loops=1)

  • Hash Cond: (csd.id = hierarchy.id)
3. 538.894 1,868.375 ↓ 3.2 193,753 1

Hash Left Join (cost=9,493.52..224,520.48 rows=60,706 width=17) (actual time=68.025..1,868.375 rows=193,753 loops=1)

  • Hash Cond: (cdr.time_zone_id = tzcdr.id)
  • Filter: (CASE WHEN (sdpv.pricing_entity_type_id = 63) THEN ((change_tz(ili.service_start_date, 'UTC'::character varying, tzl.time_zone))::date >= (change_tz(cr.effective_date, 'UTC'::character varying, tzcr.time_zone))::date) WHEN (sdpv.pricing_entity_type_id = 160) THEN ((change_tz(ili.service_start_date, 'UTC'::character varying, tzl.time_zone))::date >= (change_tz(cdr.effective_date, 'UTC'::character varying, tzcdr.time_zone))::date) ELSE true END OR (sdpv.pricing_entity_id IS NULL))
  • Rows Removed by Filter: 2315
4. 43.645 1,329.458 ↓ 2.8 196,068 1

Hash Left Join (cost=9,489.98..223,601.25 rows=69,156 width=85) (actual time=67.988..1,329.458 rows=196,068 loops=1)

  • Hash Cond: (sdpv.pricing_entity_id = cdr.id)
  • Join Filter: (sdpv.pricing_entity_type_id = 160)
  • Rows Removed by Join Filter: 42289
  • Filter: (CASE WHEN (sdpv.pricing_entity_type_id = 63) THEN (NOT cr.deleted) WHEN (sdpv.pricing_entity_type_id = 160) THEN (NOT cdr.deleted) ELSE true END OR (sdpv.pricing_entity_id IS NULL))
5. 35.619 1,278.271 ↓ 2.5 196,068 1

Hash Left Join (cost=6,380.90..220,392.90 rows=78,782 width=74) (actual time=60.405..1,278.271 rows=196,068 loops=1)

  • Hash Cond: (sdpv.pricing_entity_id = cr.id)
  • Join Filter: (sdpv.pricing_entity_type_id = 63)
6. 43.953 1,235.789 ↓ 2.5 196,068 1

Hash Left Join (cost=4,415.25..218,249.38 rows=78,782 width=49) (actual time=53.519..1,235.789 rows=196,068 loops=1)

  • Hash Cond: (csd.current_pricing_version_id = sdpv.id)
7. 36.484 1,140.234 ↓ 2.5 196,068 1

Hash Left Join (cost=836.63..214,571.48 rows=78,782 width=45) (actual time=1.767..1,140.234 rows=196,068 loops=1)

  • Hash Cond: (ili.time_zone_id = tzl.id)
8. 42.460 1,103.731 ↓ 2.5 196,068 1

Merge Join (cost=833.09..214,465.73 rows=78,782 width=33) (actual time=1.742..1,103.731 rows=196,068 loops=1)

  • Merge Cond: (ili.service_data_id = csd.id)
9. 24.237 928.527 ↓ 1.0 197,259 1

Merge Append (cost=1.06..156,156.36 rows=195,719 width=25) (actual time=0.066..928.527 rows=197,259 loops=1)

  • Sort Key: ili.service_data_id
10. 17.650 17.650 ↑ 1.1 13,423 1

Index Scan using invoice_line_item_till_201812_service_idx on invoice_line_item_till_201812 ili (cost=0.14..1,247.27 rows=14,244 width=25) (actual time=0.011..17.650 rows=13,423 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 5606
11. 4.318 4.318 ↑ 1.0 3,246 1

Index Scan using invoice_line_item_201901_service_idx on invoice_line_item_201901 ili_1 (cost=0.14..358.38 rows=3,340 width=25) (actual time=0.011..4.318 rows=3,246 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1436
12. 4.486 4.486 ↑ 1.1 985 1

Index Scan using invoice_line_item_201904_service_idx on invoice_line_item_201904 ili_2 (cost=0.14..374.54 rows=1,044 width=25) (actual time=0.013..4.486 rows=985 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 4582
13. 9.656 9.656 ↑ 1.0 3,625 1

Index Scan using invoice_line_item_201907_service_idx on invoice_line_item_201907 ili_3 (cost=0.14..955.51 rows=3,650 width=25) (actual time=0.009..9.656 rows=3,625 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 7385
14. 665.791 665.791 ↓ 1.0 84,162 1

Index Scan using invoice_line_item_201910_service_idx on invoice_line_item_201910 ili_4 (cost=0.20..114,901.29 rows=81,989 width=25) (actual time=0.012..665.791 rows=84,162 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 685923
15. 202.388 202.388 ↓ 1.0 91,823 1

Index Scan using invoice_line_item_202001_service_idx on invoice_line_item_202001 ili_5 (cost=0.20..36,900.52 rows=91,451 width=25) (actual time=0.009..202.388 rows=91,823 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 101577
16. 0.001 0.001 ↓ 0.0 0 1

Index Scan using invoice_line_item_202004_service_idx on invoice_line_item_202004 ili_6 (cost=0.06..2.26 rows=1 width=25) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (NOT deleted)
17. 132.744 132.744 ↓ 1.0 81,200 1

Index Scan using pk_contract_service_data on contract_service_data csd (cost=0.20..63,761.99 rows=80,992 width=8) (actual time=0.104..132.744 rows=81,200 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 112416
18. 0.006 0.019 ↑ 1.0 41 1

Hash (cost=3.45..3.45 rows=41 width=20) (actual time=0.019..0.019 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
19. 0.013 0.013 ↑ 1.0 41 1

Index Scan using pk_time_zone on time_zone tzl (cost=0.07..3.45 rows=41 width=20) (actual time=0.003..0.013 rows=41 loops=1)

20. 14.101 51.602 ↓ 1.0 103,997 1

Hash (cost=3,349.84..3,349.84 rows=103,993 width=12) (actual time=51.602..51.602 rows=103,997 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4879kB
21. 37.501 37.501 ↓ 1.0 103,997 1

Index Scan using pk_service_data_pricing_version on service_data_pricing_version sdpv (cost=0.14..3,349.84 rows=103,993 width=12) (actual time=0.007..37.501 rows=103,997 loops=1)

22. 0.606 6.863 ↑ 1.0 3,945 1

Hash (cost=1,956.97..1,956.97 rows=3,945 width=29) (actual time=6.863..6.863 rows=3,945 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 267kB
23. 0.766 6.257 ↑ 1.0 3,945 1

Hash Left Join (cost=3.68..1,956.97 rows=3,945 width=29) (actual time=0.064..6.257 rows=3,945 loops=1)

  • Hash Cond: (cr.time_zone_id = tzcr.id)
24. 5.468 5.468 ↑ 1.0 3,945 1

Index Scan using change_request_pkey on change_request cr (cost=0.14..1,948.31 rows=3,945 width=17) (actual time=0.031..5.468 rows=3,945 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 687
25. 0.009 0.023 ↑ 1.0 41 1

Hash (cost=3.45..3.45 rows=41 width=20) (actual time=0.023..0.023 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
26. 0.014 0.014 ↑ 1.0 41 1

Index Scan using pk_time_zone on time_zone tzcr (cost=0.07..3.45 rows=41 width=20) (actual time=0.005..0.014 rows=41 loops=1)

27. 0.829 7.542 ↓ 1.0 5,655 1

Hash (cost=3,096.72..3,096.72 rows=5,622 width=17) (actual time=7.542..7.542 rows=5,655 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 332kB
28. 6.713 6.713 ↓ 1.0 5,655 1

Index Scan using idx_contract_draft_request_1 on contract_draft_request cdr (cost=0.14..3,096.72 rows=5,622 width=17) (actual time=0.038..6.713 rows=5,655 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 804
29. 0.007 0.023 ↑ 1.0 41 1

Hash (cost=3.45..3.45 rows=41 width=20) (actual time=0.023..0.023 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
30. 0.016 0.016 ↑ 1.0 41 1

Index Scan using pk_time_zone on time_zone tzcdr (cost=0.07..3.45 rows=41 width=20) (actual time=0.007..0.016 rows=41 loops=1)

31. 0.001 0.015 ↑ 10.0 1 1

Hash (cost=122.94..122.94 rows=10 width=4) (actual time=0.015..0.015 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.014 0.014 ↑ 10.0 1 1

CTE Scan on hierarchy (cost=122.89..122.93 rows=10 width=4) (actual time=0.012..0.014 rows=1 loops=1)

  • Filter: billing_available
33.          

CTE hierarchy

34. 0.001 0.012 ↑ 21.0 1 1

Recursive Union (cost=0.20..122.89 rows=21 width=9) (actual time=0.011..0.012 rows=1 loops=1)

35. 0.010 0.010 ↑ 1.0 1 1

Index Scan using pk_contract_service_data on contract_service_data (cost=0.20..2.40 rows=1 width=9) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (id = 8034)
36. 0.000 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.20..12.04 rows=2 width=9) (actual time=0.001..0.001 rows=0 loops=1)

37. 0.001 0.001 ↓ 0.0 0 1

WorkTable Scan on hierarchy hr (cost=0.00..0.02 rows=5 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (NOT billing_available)
  • Rows Removed by Filter: 1
38. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_contract_service_data on contract_service_data csd_1 (cost=0.20..2.40 rows=1 width=9) (never executed)

  • Index Cond: (id = hr.parent_id)
  • Filter: (NOT deleted)
39. 0.132 0.132 ↓ 0.0 0 44

Index Scan using base_invoice_pkey on base_invoice bi (cost=0.14..0.19 rows=1 width=5) (actual time=0.003..0.003 rows=0 loops=44)

  • Index Cond: (id = ili.invoice_id)
  • Filter: ((NOT deleted) AND ((payment_approved IS NULL) OR (NOT payment_approved) OR (ili.approved IS NULL) OR (NOT ili.approved)) AND ((payment_approved IS NULL) OR (NOT payment_approved) OR (ili.approved IS NULL) OR (NOT ili.approved)))
  • Rows Removed by Filter: 1
Planning time : 2.404 ms
Execution time : 1,884.101 ms