explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iylM

Settings
# exclusive inclusive rows x rows loops node
1. 17.452 1,746.108 ↑ 7.2 23,591 1

Nested Loop Left Join (cost=10,655.46..3,848,554.59 rows=170,300 width=60) (actual time=197.504..1,746.108 rows=23,591 loops=1)

  • Join Filter: ((COALESCE(r_corrections_peni.supplier_guid, mc.guid) = all_services.supplier_guid) AND (r_corrections_peni.service_guid = all_services.service_guid) AND (r_corrections_peni.payment_recipient_guid = all_services.payment_recipient_guid))
2.          

CTE all_services

3. 21.818 200.109 ↑ 11.7 14,609 1

HashAggregate (cost=5,880.15..7,583.15 rows=170,300 width=52) (actual time=192.002..200.109 rows=14,609 loops=1)

  • Group Key: msi_1.id, COALESCE(x.supplier_guid, $1), x.payment_recipient_guid, x.service_guid
4.          

Initplan (for HashAggregate)

5. 0.010 0.021 ↑ 1.0 1 1

Index Scan using management_companies_pkey on management_companies (cost=1.17..3.39 rows=1 width=16) (actual time=0.020..0.021 rows=1 loops=1)

  • Index Cond: (id = $0)
6.          

Initplan (for Index Scan)

7. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on model_building (cost=0.00..1.02 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1)

  • Filter: (id = 2)
  • Rows Removed by Filter: 1
8. 6.348 178.270 ↑ 3.3 51,485 1

Nested Loop (cost=0.31..4,173.77 rows=170,300 width=52) (actual time=0.253..178.270 rows=51,485 loops=1)

9. 2.075 2.075 ↓ 1.0 1,751 1

Index Scan using model_services_invoice_period_id on model_services_invoice msi_1 (cost=0.29..767.74 rows=1,703 width=2,321) (actual time=0.025..2.075 rows=1,751 loops=1)

  • Index Cond: (period_id = 10)
  • Filter: (building_id = 2)
  • Rows Removed by Filter: 62
10. 169.847 169.847 ↑ 3.4 29 1,751

Function Scan on jsonb_to_recordset x (cost=0.02..1.02 rows=100 width=48) (actual time=0.095..0.097 rows=29 loops=1,751)

11. 30.312 1,728.656 ↑ 7.2 23,591 1

Nested Loop Left Join (cost=3,072.31..3,372,220.69 rows=170,300 width=106) (actual time=197.502..1,728.656 rows=23,591 loops=1)

  • Join Filter: ((COALESCE(r_corrections.supplier_guid, mc.guid) = all_services.supplier_guid) AND (r_corrections.service_guid = all_services.service_guid) AND (r_corrections.payment_recipient_guid = all_services.payment_recipient_guid))
  • Rows Removed by Join Filter: 200,297
12. 9.389 1,236.554 ↑ 11.1 15,393 1

Nested Loop Left Join (cost=3,072.30..2,903,895.69 rows=170,300 width=429) (actual time=197.493..1,236.554 rows=15,393 loops=1)

  • Join Filter: ((COALESCE(r_closing_balance_peni.supplier_guid, mc.guid) = all_services.supplier_guid) AND (r_closing_balance_peni.service_guid = all_services.service_guid) AND (r_closing_balance_peni.payment_recipient_guid = all_services.payment_recipient_guid))
13. 10.991 1,227.165 ↑ 11.1 15,393 1

Nested Loop Left Join (cost=3,072.30..2,435,570.68 rows=170,300 width=461) (actual time=197.491..1,227.165 rows=15,393 loops=1)

  • Join Filter: ((COALESCE(r_opening_balance_peni.supplier_guid, mc.guid) = all_services.supplier_guid) AND (r_opening_balance_peni.service_guid = all_services.service_guid) AND (r_opening_balance_peni.payment_recipient_guid = all_services.payment_recipient_guid))
14. 20.888 1,216.174 ↑ 11.1 15,393 1

Nested Loop Left Join (cost=3,072.30..1,967,245.68 rows=170,300 width=493) (actual time=197.489..1,216.174 rows=15,393 loops=1)

  • Join Filter: ((COALESCE(r_closing_balance.supplier_guid, mc.guid) = all_services.supplier_guid) AND (r_closing_balance.service_guid = all_services.service_guid) AND (r_closing_balance.payment_recipient_guid = all_services.payment_recipient_guid))
  • Rows Removed by Join Filter: 90,564
15. 9.147 965.501 ↑ 11.1 15,319 1

Nested Loop Left Join (cost=3,072.30..1,498,920.68 rows=170,300 width=962) (actual time=197.472..965.501 rows=15,319 loops=1)

  • Join Filter: ((COALESCE(r_recalculation.supplier_guid, mc.guid) = all_services.supplier_guid) AND (r_recalculation.service_guid = all_services.service_guid) AND (r_recalculation.payment_recipient_guid = all_services.payment_recipient_guid) AND (COALESCE(r_recalculation.tariff_zone, 1) = COALESCE((r_charges.tariff_zone)::integer, 1)))
  • Rows Removed by Join Filter: 14,537
16. 22.813 897.686 ↑ 11.6 14,667 1

Nested Loop Left Join (cost=3,072.29..945,445.68 rows=170,300 width=1,450) (actual time=197.470..897.686 rows=14,667 loops=1)

  • Join Filter: ((COALESCE(r_charges.supplier_guid, mc.guid) = all_services.supplier_guid) AND (r_charges.service_guid = all_services.service_guid) AND (r_charges.payment_recipient_guid = all_services.payment_recipient_guid))
  • Rows Removed by Join Filter: 76,218
17. 0.608 258.859 ↑ 11.6 14,667 1

Nested Loop Left Join (cost=3,072.29..477,120.67 rows=170,300 width=1,660) (actual time=197.407..258.859 rows=14,667 loops=1)

  • Join Filter: ((COALESCE(r_opening_balance.supplier_guid, mc.guid) = all_services.supplier_guid) AND (r_opening_balance.service_guid = all_services.service_guid) AND (r_opening_balance.payment_recipient_guid = all_services.payment_recipient_guid))
  • Rows Removed by Join Filter: 1,881
18. 5.631 243.642 ↑ 11.7 14,609 1

Hash Join (cost=3,072.29..8,795.67 rows=170,300 width=1,808) (actual time=197.402..243.642 rows=14,609 loops=1)

  • Hash Cond: (a.company_id = mc.id)
19. 7.514 237.942 ↑ 11.7 14,609 1

Hash Join (cost=3,063.82..8,311.02 rows=170,300 width=1,796) (actual time=197.324..237.942 rows=14,609 loops=1)

  • Hash Cond: (msi.account_id = a.id)
20. 6.289 229.715 ↑ 11.7 14,609 1

Hash Left Join (cost=2,842.14..7,640.58 rows=170,300 width=1,792) (actual time=196.606..229.715 rows=14,609 loops=1)

  • Hash Cond: (all_services.service_guid = s.guid)
21. 11.922 223.408 ↑ 11.7 14,609 1

Hash Join (cost=2,839.33..6,692.60 rows=170,300 width=1,792) (actual time=196.580..223.408 rows=14,609 loops=1)

  • Hash Cond: (all_services.si_id = msi.id)
22. 206.921 206.921 ↑ 11.7 14,609 1

CTE Scan on all_services (cost=0.00..3,406.00 rows=170,300 width=52) (actual time=192.004..206.921 rows=14,609 loops=1)

23. 1.457 4.565 ↑ 1.0 8,237 1

Hash (cost=2,736.37..2,736.37 rows=8,237 width=1,744) (actual time=4.565..4.565 rows=8,237 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 2,267kB
24. 3.108 3.108 ↑ 1.0 8,237 1

Seq Scan on model_services_invoice msi (cost=0.00..2,736.37 rows=8,237 width=1,744) (actual time=0.006..3.108 rows=8,237 loops=1)

25. 0.005 0.018 ↑ 1.0 36 1

Hash (cost=2.36..2.36 rows=36 width=16) (actual time=0.018..0.018 rows=36 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
26. 0.013 0.013 ↑ 1.0 36 1

Seq Scan on model_service s (cost=0.00..2.36 rows=36 width=16) (actual time=0.007..0.013 rows=36 loops=1)

27. 0.177 0.713 ↑ 1.0 1,097 1

Hash (cost=207.97..207.97 rows=1,097 width=8) (actual time=0.713..0.713 rows=1,097 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 59kB
28. 0.536 0.536 ↑ 1.0 1,097 1

Seq Scan on model_account a (cost=0.00..207.97 rows=1,097 width=8) (actual time=0.005..0.536 rows=1,097 loops=1)

29. 0.013 0.069 ↑ 1.0 65 1

Hash (cost=7.65..7.65 rows=65 width=20) (actual time=0.069..0.069 rows=65 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
30. 0.056 0.056 ↑ 1.0 65 1

Seq Scan on management_companies mc (cost=0.00..7.65 rows=65 width=20) (actual time=0.015..0.056 rows=65 loops=1)

31. 14.609 14.609 ↓ 0.0 0 14,609

Function Scan on jsonb_to_recordset r_opening_balance (cost=0.00..1.00 rows=100 width=48) (actual time=0.001..0.001 rows=0 loops=14,609)

32. 616.014 616.014 ↑ 16.7 6 14,667

Function Scan on jsonb_to_recordset r_charges (cost=0.00..1.00 rows=100 width=50) (actual time=0.042..0.042 rows=6 loops=14,667)

33. 58.668 58.668 ↑ 100.0 1 14,667

Function Scan on jsonb_to_recordset r_recalculation (cost=0.00..1.00 rows=100 width=52) (actual time=0.004..0.004 rows=1 loops=14,667)

34. 229.785 229.785 ↑ 14.3 7 15,319

Function Scan on jsonb_to_recordset r_closing_balance (cost=0.00..1.00 rows=100 width=48) (actual time=0.015..0.015 rows=7 loops=15,319)

35. 0.000 0.000 ↓ 0.0 0 15,393

Function Scan on jsonb_to_recordset r_opening_balance_peni (cost=0.00..1.00 rows=100 width=48) (actual time=0.000..0.000 rows=0 loops=15,393)

36. 0.000 0.000 ↓ 0.0 0 15,393

Function Scan on jsonb_to_recordset r_closing_balance_peni (cost=0.00..1.00 rows=100 width=48) (actual time=0.000..0.000 rows=0 loops=15,393)

37. 461.790 461.790 ↑ 7.1 14 15,393

Function Scan on jsonb_to_recordset r_corrections (cost=0.00..1.00 rows=100 width=48) (actual time=0.029..0.030 rows=14 loops=15,393)

38. 0.000 0.000 ↓ 0.0 0 23,591

Function Scan on jsonb_to_recordset r_corrections_peni (cost=0.00..1.00 rows=100 width=48) (actual time=0.000..0.000 rows=0 loops=23,591)

Planning time : 32.137 ms
Execution time : 1,748.360 ms