explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2RYh

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=21,252,914,130,141.50..23,903,957,677,609.02 rows=5,701,168,919,285 width=2,376) (actual rows= loops=)

  • Group Key: q.id, qs.status, f.name, (COALESCE(replace(replace((((qs.option_selections)::json -> 'term'::text))::text, '["'::text, ''::text), '"]'::text, ''::text), ''::text)), (COALESCE(replace((((qs.option_selections)::json -> 'bandwidth'::text))::text, '"'::text, ''::text), ''::text)), (COALESCE(replace((((qs.option_selections)::json -> 'port_bandwidth'::text))::text, '"'::text, ''::text), ''::text)), (COALESCE(replace((((qs.option_selections)::json -> 'class_of_service'::text))::text, '"'::text, ''::text), ''::text)), (COALESCE(replace((((qs.option_selections)::json -> 'service_requirements'::text))::text, '"'::text, ''::text), ''::text)), (COALESCE(replace(replace((((d.option_selections)::json -> 'term'::text))::text, '["'::text, ''::text), '"]'::text, ''::text), ''::text)), (COALESCE(replace((((d.option_selections)::json -> 'bandwidth'::text))::text, '"'::text, ''::text), ''::text)), (COALESCE(replace((((d.option_selections)::json -> 'port_bandwidth'::text))::text, '"'::text, ''::text), ''::text)), (COALESCE(replace((((d.option_selections)::json -> 'class_of_service'::text))::text, '"'::text, ''::text), ''::text)), (concat(la.street_number, ', ', la.street, ', ', la.city, ', ', la.postal_code)), la.state, ca.name, la.longitude, la.latitude, lta.name, (concat(lz.street_number, ', ', lz.street, ', ', lz.city, ', ', lz.postal_code)), lz.state, cz.name, lz.longitude, lz.latitude, ltz.name, acco.name, accc.name, cu.name, di.vendor_currency_to_usd_rate, accu.name, acco1.name, t.id, d.id, qs.location_a_text, qs.location_z_text, acco2.name, acco2.mrr_partner_commission_enterprise, acco2.mrr_partner_commission_wholesale, accu1.name, acco1.is_wholesale_partner, acco1.is_enterprise_partner, acco2.is_wholesale_partner, acco2.is_enterprise_partner, accu2.name, di.location_a_proximity, di.location_z_proximity, di.type_2_vendor_name, di.location_a_id, di.location_z_id
2. 0.000 0.000 ↓ 0.0

Sort (cost=21,252,914,130,141.50..21,267,167,052,439.71 rows=5,701,168,919,285 width=950) (actual rows= loops=)

  • Sort Key: q.id, qs.status, f.name, (COALESCE(replace(replace((((qs.option_selections)::json -> 'term'::text))::text, '["'::text, ''::text), '"]'::text, ''::text), ''::text)), (COALESCE(replace((((qs.option_selections)::json -> 'bandwidth'::text))::text, '"'::text, ''::text), ''::text)), (COALESCE(replace((((qs.option_selections)::json -> 'port_bandwidth'::text))::text, '"'::text, ''::text), ''::text)), (COALESCE(replace((((qs.option_selections)::json -> 'class_of_service'::text))::text, '"'::text, ''::text), ''::text)), (COALESCE(replace((((qs.option_selections)::json -> 'service_requirements'::text))::text, '"'::text, ''::text), ''::text)), (COALESCE(replace(replace((((d.option_selections)::json -> 'term'::text))::text, '["'::text, ''::text), '"]'::text, ''::text), ''::text)), (COALESCE(replace((((d.option_selections)::json -> 'bandwidth'::text))::text, '"'::text, ''::text), ''::text)), (COALESCE(replace((((d.option_selections)::json -> 'port_bandwidth'::text))::text, '"'::text, ''::text), ''::text)), (COALESCE(replace((((d.option_selections)::json -> 'class_of_service'::text))::text, '"'::text, ''::text), ''::text)), (concat(la.street_number, ', ', la.street, ', ', la.city, ', ', la.postal_code)), la.state, ca.name, la.longitude, la.latitude, lta.name, (concat(lz.street_number, ', ', lz.street, ', ', lz.city, ', ', lz.postal_code)), lz.state, cz.name, lz.longitude, lz.latitude, ltz.name, acco.name, accc.name, cu.name, di.vendor_currency_to_usd_rate, accu.name, acco1.name, t.id, d.id, qs.location_a_text, qs.location_z_text, acco2.name, acco2.mrr_partner_commission_enterprise, acco2.mrr_partner_commission_wholesale, accu1.name, acco1.is_wholesale_partner, acco1.is_enterprise_partner, acco2.is_wholesale_partner, acco2.is_enterprise_partner, accu2.name, di.location_a_proximity, di.location_z_proximity, di.type_2_vendor_name, di.location_a_id, di.location_z_id
3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=141,102.48..1,026,253,022,947.94 rows=5,701,168,919,285 width=950) (actual rows= loops=)

  • Join Filter: CASE WHEN (d.location_z_id IS NOT NULL) THEN (d.location_z_id = lz.id) ELSE (qs.location_z_id = lz.id) END
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=141,065.43..42,616,412.66 rows=652,867,898 width=762) (actual rows= loops=)

  • Hash Cond: (d.primary_vendor_id = acco.id)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=141,046.50..33,655,346.58 rows=652,867,898 width=756) (actual rows= loops=)

  • Hash Cond: ((di.vendor_currency_id)::text = (cu.currency_code)::text)
6. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=141,038.74..24,678,405.22 rows=652,867,898 width=744) (actual rows= loops=)

  • Merge Cond: (d.id = t.id)
7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=121,016.66..22,977,224.07 rows=652,867,898 width=740) (actual rows= loops=)

  • Join Filter: CASE WHEN (d.location_a_id IS NOT NULL) THEN (d.location_a_id = la.id) ELSE (qs.location_a_id = la.id) END
8. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=120,979.61..125,783.67 rows=74,763 width=679) (actual rows= loops=)

  • Merge Cond: ((d.id = di.design_id) AND (d.primary_vendor_id = di.vendor_id))
9. 0.000 0.000 ↓ 0.0

Sort (cost=41,626.51..41,813.42 rows=74,763 width=632) (actual rows= loops=)

  • Sort Key: d.id, d.primary_vendor_id
10. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=7,633.02..14,619.94 rows=74,763 width=632) (actual rows= loops=)

  • Merge Cond: (d.quote_specification_id = qs.id)
11. 0.000 0.000 ↓ 0.0

Index Scan using ccm_design_quote_specification_id_78995cdb on ccm_design d (cost=0.29..5,643.62 rows=74,763 width=226) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Materialize (cost=7,632.73..7,703.19 rows=14,093 width=414) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=7,632.73..7,667.96 rows=14,093 width=414) (actual rows= loops=)

  • Sort Key: qs.id
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=881.99..4,012.03 rows=14,093 width=414) (actual rows= loops=)

  • Hash Cond: (q.folder_id = f.id)
15. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=861.29..3,797.55 rows=14,093 width=408) (actual rows= loops=)

  • Hash Cond: (q.customer_id = accc.id)
16. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=820.45..3,563.27 rows=14,093 width=394) (actual rows= loops=)

  • Hash Cond: (q.csa_id = accu2.id)
17. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=798.45..3,483.87 rows=14,093 width=383) (actual rows= loops=)

  • Hash Cond: (q.sales_representative_id = accu1.id)
18. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=776.44..3,386.18 rows=14,093 width=372) (actual rows= loops=)

  • Hash Cond: (q.partner_organization_id = acco2.id)
19. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=757.51..3,288.35 rows=14,093 width=358) (actual rows= loops=)

  • Hash Cond: (q.user_id = accu.id)
20. 0.000 0.000 ↓ 0.0

Hash Join (cost=708.63..3,045.69 rows=14,093 width=335) (actual rows= loops=)

  • Hash Cond: (qs.quote_id = q.id)
21. 0.000 0.000 ↓ 0.0

Seq Scan on ccm_quotespecification qs (cost=0.00..2,143.93 rows=14,093 width=272) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=533.28..533.28 rows=14,028 width=67) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on ccm_quote q (cost=0.00..533.28 rows=14,028 width=67) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=41.66..41.66 rows=578 width=31) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash Join (cost=18.93..41.66 rows=578 width=31) (actual rows= loops=)

  • Hash Cond: (accu.organization_id = acco1.id)
26. 0.000 0.000 ↓ 0.0

Seq Scan on accounts_user accu (cost=0.00..14.78 rows=578 width=23) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash (cost=15.08..15.08 rows=308 width=16) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on accounts_organization acco1 (cost=0.00..15.08 rows=308 width=16) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=15.08..15.08 rows=308 width=22) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on accounts_organization acco2 (cost=0.00..15.08 rows=308 width=22) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=14.78..14.78 rows=578 width=19) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Seq Scan on accounts_user accu1 (cost=0.00..14.78 rows=578 width=19) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash (cost=14.78..14.78 rows=578 width=19) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Seq Scan on accounts_user accu2 (cost=0.00..14.78 rows=578 width=19) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Hash (cost=25.37..25.37 rows=1,237 width=22) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on accounts_customer accc (cost=0.00..25.37 rows=1,237 width=22) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash (cost=13.09..13.09 rows=609 width=14) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on ccm_folder f (cost=0.00..13.09 rows=609 width=14) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Materialize (cost=79,353.09..81,343.29 rows=398,039 width=55) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Sort (cost=79,353.09..80,348.19 rows=398,039 width=55) (actual rows= loops=)

  • Sort Key: di.design_id, di.vendor_id
41. 0.000 0.000 ↓ 0.0

Seq Scan on ccm_designitem di (cost=0.00..28,722.39 rows=398,039 width=55) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Materialize (cost=37.05..1,107.65 rows=17,465 width=73) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash Join (cost=37.05..1,020.32 rows=17,465 width=73) (actual rows= loops=)

  • Hash Cond: (((la.location_type_id)::text = (lta.key)::text) AND (la.country_id = ca.country_code))
44. 0.000 0.000 ↓ 0.0

Seq Scan on ccm_location la (cost=0.00..546.65 rows=17,465 width=68) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Hash (cost=22.05..22.05 rows=1,000 width=26) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..22.05 rows=1,000 width=26) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on ccm_country ca (cost=0.00..8.50 rows=250 width=14) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.06 rows=4 width=12) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Seq Scan on ccm_locationtype lta (cost=0.00..1.04 rows=4 width=12) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Sort (cost=20,022.08..20,023.02 rows=374 width=4) (actual rows= loops=)

  • Sort Key: t.id
51. 0.000 0.000 ↓ 0.0

Subquery Scan on t (cost=14,959.60..20,006.10 rows=374 width=4) (actual rows= loops=)

  • Filter: (t.rn = 1)
52. 0.000 0.000 ↓ 0.0

WindowAgg (cost=14,959.60..19,071.56 rows=74,763 width=56) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Sort (cost=14,959.60..15,146.51 rows=74,763 width=48) (actual rows= loops=)

  • Sort Key: ccm_design.quote_specification_id, (((ccm_design.ug_nrr / ((replace(replace((((ccm_design.option_selections)::json -> 'term'::text))::text, '["'::text, ''::text), '"]'::text, ''::text))::integer)::numeric) + ccm_design.ug_mrr)), ccm_design.distance, ccm_design.id
54. 0.000 0.000 ↓ 0.0

Seq Scan on ccm_design (cost=0.00..6,604.52 rows=74,763 width=48) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Hash (cost=5.67..5.67 rows=167 width=20) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Seq Scan on ccm_currency cu (cost=0.00..5.67 rows=167 width=20) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Hash (cost=15.08..15.08 rows=308 width=14) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Seq Scan on accounts_organization acco (cost=0.00..15.08 rows=308 width=14) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Materialize (cost=37.05..1,107.65 rows=17,465 width=73) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Hash Join (cost=37.05..1,020.32 rows=17,465 width=73) (actual rows= loops=)

  • Hash Cond: (((lz.location_type_id)::text = (ltz.key)::text) AND (lz.country_id = cz.country_code))
61. 0.000 0.000 ↓ 0.0

Seq Scan on ccm_location lz (cost=0.00..546.65 rows=17,465 width=68) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Hash (cost=22.05..22.05 rows=1,000 width=26) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..22.05 rows=1,000 width=26) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Seq Scan on ccm_country cz (cost=0.00..8.50 rows=250 width=14) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.06 rows=4 width=12) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Seq Scan on ccm_locationtype ltz (cost=0.00..1.04 rows=4 width=12) (actual rows= loops=)