explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1rLV

Settings
# exclusive inclusive rows x rows loops node
1. 7.211 1,624.607 ↑ 7.2 23,591 1

Sort (cost=3,863,351.72..3,863,777.47 rows=170,300 width=60) (actual time=1,623.522..1,624.607 rows=23,591 loops=1)

  • Sort Key: (COALESCE((r_charges.tariff_zone)::integer, 1))
  • Sort Method: quicksort Memory: 3,891kB
2.          

CTE all_services

3. 21.254 199.472 ↑ 11.7 14,609 1

HashAggregate (cost=5,880.15..7,583.15 rows=170,300 width=52) (actual time=191.992..199.472 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.009 0.018 ↑ 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.017..0.018 rows=1 loops=1)

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

Initplan (for Index Scan)

7. 0.009 0.009 ↑ 1.0 1 1

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

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

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

9. 1.925 1.925 ↓ 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..1.925 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. 14.187 1,617.396 ↑ 7.2 23,591 1

Nested Loop Left Join (cost=3,072.31..3,840,971.44 rows=170,300 width=60) (actual time=197.519..1,617.396 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))
12. 35.101 1,603.209 ↑ 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.517..1,603.209 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
13. 8.467 1,152.497 ↑ 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.507..1,152.497 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))
14. 9.805 1,144.030 ↑ 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.506..1,144.030 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))
15. 14.234 1,134.225 ↑ 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.503..1,134.225 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
16. 3.922 905.525 ↑ 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.485..905.525 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
17. 16.396 842.935 ↑ 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.483..842.935 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
18. 0.249 254.526 ↑ 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.421..254.526 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
19. 5.346 239.668 ↑ 11.7 14,609 1

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

  • Hash Cond: (a.company_id = mc.id)
20. 6.983 234.248 ↑ 11.7 14,609 1

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

  • Hash Cond: (msi.account_id = a.id)
21. 5.642 226.507 ↑ 11.7 14,609 1

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

  • Hash Cond: (all_services.service_guid = s.guid)
22. 10.834 220.846 ↑ 11.7 14,609 1

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

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

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

24. 1.450 4.542 ↑ 1.0 8,237 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 2,267kB
25. 3.092 3.092 ↑ 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.092 rows=8,237 loops=1)

26. 0.006 0.019 ↑ 1.0 36 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
27. 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)

28. 0.205 0.758 ↑ 1.0 1,097 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 59kB
29. 0.553 0.553 ↑ 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.553 rows=1,097 loops=1)

30. 0.014 0.074 ↑ 1.0 65 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
31. 0.060 0.060 ↑ 1.0 65 1

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

32. 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)

33. 572.013 572.013 ↑ 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.038..0.039 rows=6 loops=14,667)

34. 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)

35. 214.466 214.466 ↑ 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.013..0.014 rows=7 loops=15,319)

36. 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)

37. 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)

38. 415.611 415.611 ↑ 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.026..0.027 rows=14 loops=15,393)

39. 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.413 ms
Execution time : 1,626.748 ms