explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Dvb

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 3.308 ↓ 0.0 0 1

GroupAggregate (cost=7,222.84..7,228.94 rows=20 width=4,621) (actual time=3.308..3.308 rows=0 loops=1)

  • Group Key: s.quote_sq_id, s.quote_sq_per_loc_id, s.sku, s.name, s.qty, s.api_quote_id, s.term, s.sku_id, s.product_code, s.class, s.subclass, s."group", s.subgroup, s.promotion_group, s.promotion_name, s.type,
2.          

CTE leg1

3. 0.000 3.107 ↓ 0.0 0 1

Nested Loop (cost=2.60..286.74 rows=40 width=1,317) (actual time=3.107..3.107 rows=0 loops=1)

4. 0.000 3.107 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.17..225.58 rows=20 width=758) (actual time=3.107..3.107 rows=0 loops=1)

5. 0.001 3.107 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.87..219.01 rows=20 width=723) (actual time=3.107..3.107 rows=0 loops=1)

6. 0.000 3.106 ↓ 0.0 0 1

Nested Loop (cost=1.57..212.44 rows=20 width=686) (actual time=3.106..3.106 rows=0 loops=1)

7. 0.001 3.106 ↓ 0.0 0 1

Nested Loop (cost=1.15..203.01 rows=20 width=534) (actual time=3.106..3.106 rows=0 loops=1)

8. 0.001 3.105 ↓ 0.0 0 1

Nested Loop (cost=0.71..46.89 rows=17 width=27) (actual time=3.105..3.105 rows=0 loops=1)

9. 3.104 3.104 ↓ 0.0 0 1

Index Scan using quote_sq_rfq_idx_id_provider_id_index on quote_sq qs (cost=0.43..10.82 rows=17 width=18) (actual time=3.104..3.104 rows=0 loops=1)

  • Index Cond: (rfq_idx_id = 40685780)
10. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_providers_pkey on quote_providers qp (cost=0.28..2.11 rows=1 width=17) (never executed)

  • Index Cond: (id = qs.provider_id)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_per_loc_quote_sq_id on quote_sq_per_loc pl (cost=0.43..9.00 rows=18 width=515) (never executed)

  • Index Cond: (quote_sq_id = qs.id)
  • Filter: show
12. 0.000 0.000 ↓ 0.0 0

Index Scan using req_sq_idx_pkey on req_sq_idx r (cost=0.43..0.46 rows=1 width=160) (never executed)

  • Index Cond: (id = pl.req_sq_idx_id)
13. 0.000 0.000 ↓ 0.0 0

Index Scan using avail_fast_search_npanxx on avail_fast_search afs (cost=0.30..0.32 rows=1 width=45) (never executed)

  • Index Cond: (((npa)::text = "substring"((r.npanxx)::text, 1, 3)) AND ((nxx)::text = "substring"((r.npanxx)::text, 4, 6)))
14. 0.000 0.000 ↓ 0.0 0

Index Scan using avail_fast_search_npanxx on avail_fast_search afs_z (cost=0.30..0.32 rows=1 width=45) (never executed)

  • Index Cond: (((npa)::text = "substring"((r.npanxx_2)::text, 1, 3)) AND ((nxx)::text = "substring"((r.npanxx_2)::text, 4, 6)))
15. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_sq_per_loc_id_idx on quote_sq_skus s_1 (cost=0.44..2.29 rows=50 width=164) (never executed)

  • Index Cond: (quote_sq_per_loc_id = pl.id)
  • Filter: (amount_type = 'MRC'::text)
16.          

CTE leg2

17. 0.000 0.010 ↓ 0.0 0 1

Nested Loop (cost=2.60..279.62 rows=21 width=1,317) (actual time=0.010..0.010 rows=0 loops=1)

18. 0.000 0.010 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.17..225.58 rows=20 width=758) (actual time=0.010..0.010 rows=0 loops=1)

19. 0.002 0.010 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.87..219.01 rows=20 width=723) (actual time=0.010..0.010 rows=0 loops=1)

20. 0.000 0.008 ↓ 0.0 0 1

Nested Loop (cost=1.57..212.44 rows=20 width=686) (actual time=0.008..0.008 rows=0 loops=1)

21. 0.001 0.009 ↓ 0.0 0 1

Nested Loop (cost=1.15..203.01 rows=20 width=534) (actual time=0.008..0.009 rows=0 loops=1)

22. 0.000 0.008 ↓ 0.0 0 1

Nested Loop (cost=0.71..46.89 rows=17 width=27) (actual time=0.008..0.008 rows=0 loops=1)

23. 0.008 0.008 ↓ 0.0 0 1

Index Scan using quote_sq_rfq_idx_id_provider_id_index on quote_sq qs_1 (cost=0.43..10.82 rows=17 width=18) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (rfq_idx_id = 40685780)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_providers_pkey on quote_providers qp_1 (cost=0.28..2.11 rows=1 width=17) (never executed)

  • Index Cond: (id = qs_1.provider_id)
25. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_per_loc_quote_sq_id on quote_sq_per_loc pl_1 (cost=0.43..9.00 rows=18 width=515) (never executed)

  • Index Cond: (quote_sq_id = qs_1.id)
  • Filter: show
26. 0.000 0.000 ↓ 0.0 0

Index Scan using req_sq_idx_pkey on req_sq_idx r_1 (cost=0.43..0.46 rows=1 width=160) (never executed)

  • Index Cond: (id = pl_1.req_sq_idx_id)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using avail_fast_search_npanxx on avail_fast_search afs_1 (cost=0.30..0.32 rows=1 width=45) (never executed)

  • Index Cond: (((npa)::text = "substring"((r_1.npanxx)::text, 1, 3)) AND ((nxx)::text = "substring"((r_1.npanxx)::text, 4, 6)))
28. 0.000 0.000 ↓ 0.0 0

Index Scan using avail_fast_search_npanxx on avail_fast_search afs_z_1 (cost=0.30..0.32 rows=1 width=45) (never executed)

  • Index Cond: (((npa)::text = "substring"((r_1.npanxx_2)::text, 1, 3)) AND ((nxx)::text = "substring"((r_1.npanxx_2)::text, 4, 6)))
29. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_sq_per_loc_id_idx on quote_sq_skus s_2 (cost=0.44..2.29 rows=27 width=164) (never executed)

  • Index Cond: (quote_sq_per_loc_id = pl_1.id)
  • Filter: (amount_type = 'NRC'::text)
30.          

CTE leg3

31. 0.000 0.012 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.60..216.34 rows=1 width=1,093) (actual time=0.012..0.012 rows=0 loops=1)

32. 0.001 0.012 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.31..215.92 rows=1 width=876) (actual time=0.012..0.012 rows=0 loops=1)

33. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=2.01..215.59 rows=1 width=839) (actual time=0.011..0.011 rows=0 loops=1)

34. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=1.73..213.47 rows=1 width=830) (actual time=0.011..0.011 rows=0 loops=1)

35. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=1.30..213.00 rows=1 width=678) (actual time=0.011..0.011 rows=0 loops=1)

36. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.86..166.94 rows=20 width=525) (actual time=0.011..0.011 rows=0 loops=1)

37. 0.011 0.011 ↓ 0.0 0 1

Index Scan using quote_sq_rfq_idx_id_provider_id_index on quote_sq qs_2 (cost=0.43..10.82 rows=17 width=18) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (rfq_idx_id = 40685780)
38. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_per_loc_quote_sq_id on quote_sq_per_loc pl_2 (cost=0.43..9.00 rows=18 width=515) (never executed)

  • Index Cond: (quote_sq_id = qs_2.id)
  • Filter: show
39. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_sq_per_loc_id_idx on quote_sq_skus s_3 (cost=0.44..2.29 rows=1 width=157) (never executed)

  • Index Cond: (quote_sq_per_loc_id = pl_2.id)
  • Filter: (amount_type = 'Rate/Min'::text)
40. 0.000 0.000 ↓ 0.0 0

Index Scan using req_sq_idx_pkey on req_sq_idx r_2 (cost=0.43..0.46 rows=1 width=160) (never executed)

  • Index Cond: (id = pl_2.req_sq_idx_id)
41. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_providers_pkey on quote_providers qp_2 (cost=0.28..2.11 rows=1 width=17) (never executed)

  • Index Cond: (id = qs_2.provider_id)
42. 0.000 0.000 ↓ 0.0 0

Index Scan using avail_fast_search_npanxx on avail_fast_search afs_2 (cost=0.30..0.32 rows=1 width=45) (never executed)

  • Index Cond: (((npa)::text = "substring"((r_2.npanxx)::text, 1, 3)) AND ((nxx)::text = "substring"((r_2.npanxx)::text, 4, 6)))
43. 0.000 0.000 ↓ 0.0 0

Index Scan using avail_fast_search_npanxx on avail_fast_search afs_z_2 (cost=0.30..0.32 rows=1 width=45) (never executed)

  • Index Cond: (((npa)::text = "substring"((r_2.npanxx_2)::text, 1, 3)) AND ((nxx)::text = "substring"((r_2.npanxx_2)::text, 4, 6)))
44.          

CTE skus

45. 0.001 3.136 ↓ 0.0 0 1

HashAggregate (cost=17.38..18.00 rows=62 width=4,037) (actual time=3.136..3.136 rows=0 loops=1)

  • Group Key: "*SELECT* 1".quote_sq_id, "*SELECT* 1".quote_sq_per_loc_id, "*SELECT* 1".api_quote_id, "*SELECT* 1".term, "*SELECT* 1".sku_id, "*SELECT* 1".sku, "*SELECT* 1".name, "*SELECT* 1".product_code,
46. 0.001 3.135 ↓ 0.0 0 1

Append (cost=0.00..2.96 rows=62 width=4,037) (actual time=3.135..3.135 rows=0 loops=1)

47. 0.001 3.108 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=0.00..1.90 rows=40 width=4,037) (actual time=3.108..3.108 rows=0 loops=1)

48. 3.107 3.107 ↓ 0.0 0 1

CTE Scan on leg1 (cost=0.00..0.80 rows=40 width=3,841) (actual time=3.107..3.107 rows=0 loops=1)

49. 0.001 0.012 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..1.00 rows=21 width=4,037) (actual time=0.012..0.012 rows=0 loops=1)

50. 0.011 0.011 ↓ 0.0 0 1

CTE Scan on leg2 (cost=0.00..0.42 rows=21 width=3,841) (actual time=0.011..0.011 rows=0 loops=1)

51. 0.001 0.014 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=0.00..0.07 rows=1 width=4,023) (actual time=0.014..0.014 rows=0 loops=1)

52. 0.013 0.013 ↓ 0.0 0 1

CTE Scan on leg3 (cost=0.00..0.02 rows=1 width=3,631) (actual time=0.013..0.013 rows=0 loops=1)

53.          

CTE prod_type

54. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=176.80..177.25 rows=20 width=40) (never executed)

  • Group Key: pl_3.quote_sq_id
55. 0.000 0.000 ↓ 0.0 0

Sort (cost=176.80..176.85 rows=20 width=12) (never executed)

  • Sort Key: pl_3.quote_sq_id
56. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.29..176.37 rows=20 width=12) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..166.94 rows=20 width=8) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_sq_rfq_idx_id_provider_id_index on quote_sq sq (cost=0.43..10.82 rows=17 width=4) (never executed)

  • Index Cond: (rfq_idx_id = 40685780)
59. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_per_loc_quote_sq_id on quote_sq_per_loc pl_3 (cost=0.43..9.00 rows=18 width=8) (never executed)

  • Index Cond: (quote_sq_id = sq.id)
  • Filter: show
60. 0.000 0.000 ↓ 0.0 0

Index Scan using req_sq_idx_pkey on req_sq_idx r_3 (cost=0.43..0.46 rows=1 width=12) (never executed)

  • Index Cond: (id = pl_3.req_sq_idx_id)
61.          

CTE site_mrc

62. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=3,123.75..3,126.23 rows=71 width=68) (never executed)

  • Group Key: qss.quote_sq_per_loc_id
63. 0.000 0.000 ↓ 0.0 0

Sort (cost=3,123.75..3,123.92 rows=71 width=19) (never executed)

  • Sort Key: qss.quote_sq_per_loc_id
64. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.87..3,121.56 rows=71 width=19) (never executed)

65. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_sq_rfq_idx_id_provider_id_index on quote_sq qs_3 (cost=0.43..10.82 rows=17 width=4) (never executed)

  • Index Cond: (rfq_idx_id = 40685780)
66. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_sq_id_idx on quote_sq_skus qss (cost=0.44..181.53 rows=146 width=23) (never executed)

  • Index Cond: (quote_sq_id = qs_3.id)
  • Filter: (amount_type = 'MRC'::text)
67.          

CTE site_nrc

68. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=3,111.17..3,112.50 rows=38 width=68) (never executed)

  • Group Key: qss_1.quote_sq_per_loc_id
69. 0.000 0.000 ↓ 0.0 0

Sort (cost=3,111.17..3,111.27 rows=38 width=19) (never executed)

  • Sort Key: qss_1.quote_sq_per_loc_id
70. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.87..3,110.17 rows=38 width=19) (never executed)

71. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_sq_rfq_idx_id_provider_id_index on quote_sq qs_4 (cost=0.43..10.82 rows=17 width=4) (never executed)

  • Index Cond: (rfq_idx_id = 40685780)
72. 0.000 0.000 ↓ 0.0 0

Index Scan using quote_sq_id_idx on quote_sq_skus qss_1 (cost=0.44..181.53 rows=79 width=23) (never executed)

  • Index Cond: (quote_sq_id = qs_4.id)
  • Filter: (amount_type = 'NRC'::text)
73. 0.148 3.307 ↓ 0.0 0 1

Sort (cost=6.16..6.21 rows=20 width=4,205) (actual time=3.307..3.307 rows=0 loops=1)

  • Sort Key: s.quote_sq_id, s.quote_sq_per_loc_id, s.sku, s.name, s.qty, s.api_quote_id, s.term, s.sku_id, s.product_code, s.class, s.subclass, s."group", s.subgroup, s.promotion_group, s.promotion_name, s.t
  • Sort Method: quicksort Memory: 25kB
74. 0.009 3.159 ↓ 0.0 0 1

Hash Right Join (cost=3.84..5.73 rows=20 width=4,205) (actual time=3.159..3.159 rows=0 loops=1)

  • Hash Cond: (smrc.quote_sq_per_loc_id = s.quote_sq_per_loc_id)
75. 0.000 0.000 ↓ 0.0 0

CTE Scan on site_mrc smrc (cost=0.00..1.42 rows=71 width=68) (never executed)

76. 0.001 3.150 ↓ 0.0 0 1

Hash (cost=3.59..3.59 rows=20 width=4,101) (actual time=3.150..3.150 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
77. 0.011 3.149 ↓ 0.0 0 1

Hash Right Join (cost=2.57..3.59 rows=20 width=4,101) (actual time=3.149..3.149 rows=0 loops=1)

  • Hash Cond: (snrc.quote_sq_per_loc_id = s.quote_sq_per_loc_id)
78. 0.000 0.000 ↓ 0.0 0

CTE Scan on site_nrc snrc (cost=0.00..0.76 rows=38 width=68) (never executed)

79. 0.000 3.138 ↓ 0.0 0 1

Hash (cost=2.32..2.32 rows=20 width=4,037) (actual time=3.138..3.138 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
80. 0.002 3.139 ↓ 0.0 0 1

Hash Join (cost=0.65..2.32 rows=20 width=4,037) (actual time=3.138..3.139 rows=0 loops=1)

  • Hash Cond: (s.quote_sq_id = p.quote_sq_id)
81. 3.137 3.137 ↓ 0.0 0 1

CTE Scan on skus s (cost=0.00..1.24 rows=62 width=4,005) (actual time=3.137..3.137 rows=0 loops=1)

82. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.40..0.40 rows=20 width=36) (never executed)

83. 0.000 0.000 ↓ 0.0 0

CTE Scan on prod_type p (cost=0.00..0.40 rows=20 width=36) (never executed)

Planning time : 225.360 ms
Execution time : 7.965 ms