explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sa03

Settings
# exclusive inclusive rows x rows loops node
1. 0.062 2.583 ↑ 3.2 5 1

GroupAggregate (cost=16,087.12..16,092.00 rows=16 width=4,621) (actual time=2.562..2.583 rows=5 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, s.sub_type, s.market, s.sub_market, s.in
2.          

CTE leg1

3. 0.101 0.257 ↑ 8.5 4 1

Nested Loop (cost=3.16..1,969.48 rows=34 width=1,261) (actual time=0.148..0.257 rows=4 loops=1)

4. 0.004 0.131 ↑ 16.0 1 1

Nested Loop Left Join (cost=2.58..961.13 rows=16 width=715) (actual time=0.072..0.131 rows=1 loops=1)

5. 0.006 0.127 ↑ 16.0 1 1

Nested Loop Left Join (cost=2.29..939.83 rows=16 width=679) (actual time=0.068..0.127 rows=1 loops=1)

6. 0.001 0.112 ↑ 16.0 1 1

Nested Loop (cost=1.99..918.52 rows=16 width=642) (actual time=0.053..0.112 rows=1 loops=1)

7. 0.006 0.105 ↑ 16.0 1 1

Nested Loop (cost=1.71..888.55 rows=16 width=633) (actual time=0.047..0.105 rows=1 loops=1)

8. 0.004 0.087 ↑ 16.0 1 1

Nested Loop (cost=1.14..862.67 rows=16 width=467) (actual time=0.032..0.087 rows=1 loops=1)

9. 0.018 0.018 ↑ 38.0 1 1

Index Scan using rfq_idx_id on quote_sq qs (cost=0.57..7.54 rows=38 width=18) (actual time=0.018..0.018 rows=1 loops=1)

  • Index Cond: (rfq_idx_id = 40685780)
10. 0.065 0.065 ↑ 67.0 1 1

Index Scan using quote_per_loc_quote_sq_id on quote_sq_per_loc pl (cost=0.57..21.83 rows=67 width=457) (actual time=0.011..0.065 rows=1 loops=1)

  • Index Cond: (quote_sq_id = qs.id)
  • Filter: show
11. 0.012 0.012 ↑ 1.0 1 1

Index Scan using req_sq_idx_pkey on req_sq_idx r (cost=0.56..1.61 rows=1 width=174) (actual time=0.009..0.012 rows=1 loops=1)

  • Index Cond: (id = pl.req_sq_idx_id)
12. 0.006 0.006 ↑ 1.0 1 1

Index Scan using quote_providers_pkey on quote_providers qp (cost=0.28..1.86 rows=1 width=17) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (id = qs.provider_id)
13. 0.009 0.009 ↓ 0.0 0 1

Index Scan using avail_fast_search_npanxx on avail_fast_search afs (cost=0.30..1.32 rows=1 width=45) (actual time=0.009..0.009 rows=0 loops=1)

  • 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 1

Index Scan using avail_fast_search_npanxx on avail_fast_search afs_z (cost=0.30..1.32 rows=1 width=45) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (((npa)::text = "substring"((r.npanxx_2)::text, 1, 3)) AND ((nxx)::text = "substring"((r.npanxx_2)::text, 4, 6)))
15. 0.025 0.025 ↑ 32.5 4 1

Index Scan using quote_sq_skus_per_loc_amount_type on quote_sq_skus s_1 (cost=0.57..61.44 rows=130 width=160) (actual time=0.024..0.025 rows=4 loops=1)

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

CTE leg2

17. 0.038 1.742 ↑ 16.0 1 1

Nested Loop Left Join (cost=3.16..1,460.59 rows=16 width=1,261) (actual time=1.739..1.742 rows=1 loops=1)

18. 0.007 1.703 ↑ 16.0 1 1

Nested Loop Left Join (cost=2.86..1,437.16 rows=16 width=835) (actual time=1.700..1.703 rows=1 loops=1)

19. 0.000 1.687 ↑ 16.0 1 1

Nested Loop (cost=2.56..1,415.86 rows=16 width=798) (actual time=1.684..1.687 rows=1 loops=1)

20. 0.008 1.681 ↑ 16.0 1 1

Nested Loop (cost=2.28..1,385.89 rows=16 width=789) (actual time=1.679..1.681 rows=1 loops=1)

21. 0.004 1.664 ↑ 16.0 1 1

Nested Loop (cost=1.72..1,360.01 rows=16 width=623) (actual time=1.662..1.664 rows=1 loops=1)

22. 0.002 0.015 ↑ 16.0 1 1

Nested Loop (cost=1.14..862.67 rows=16 width=467) (actual time=0.014..0.015 rows=1 loops=1)

23. 0.007 0.007 ↑ 38.0 1 1

Index Scan using rfq_idx_id on quote_sq qs_1 (cost=0.57..7.54 rows=38 width=18) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (rfq_idx_id = 40685780)
24. 0.006 0.006 ↑ 67.0 1 1

Index Scan using quote_per_loc_quote_sq_id on quote_sq_per_loc pl_1 (cost=0.57..21.83 rows=67 width=457) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (quote_sq_id = qs_1.id)
  • Filter: show
25. 1.645 1.645 ↑ 63.0 1 1

Index Scan using quote_sq_skus_per_loc_amount_type on quote_sq_skus s_2 (cost=0.57..30.45 rows=63 width=160) (actual time=1.645..1.645 rows=1 loops=1)

  • Index Cond: ((amount_type = 'NRC'::text) AND (quote_sq_per_loc_id = pl_1.id))
26. 0.009 0.009 ↑ 1.0 1 1

Index Scan using req_sq_idx_pkey on req_sq_idx r_1 (cost=0.56..1.61 rows=1 width=174) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (id = pl_1.req_sq_idx_id)
27. 0.006 0.006 ↑ 1.0 1 1

Index Scan using quote_providers_pkey on quote_providers qp_1 (cost=0.28..1.86 rows=1 width=17) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (id = qs_1.provider_id)
28. 0.009 0.009 ↓ 0.0 0 1

Index Scan using avail_fast_search_npanxx on avail_fast_search afs_1 (cost=0.30..1.32 rows=1 width=45) (actual time=0.009..0.009 rows=0 loops=1)

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

Index Scan using avail_fast_search_npanxx on avail_fast_search afs_z_1 (cost=0.30..1.32 rows=1 width=45) (actual time=0.001..0.001 rows=0 loops=1)

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

CTE leg3

31. 0.000 0.028 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.16..897.21 rows=1 width=1,038) (actual time=0.028..0.028 rows=0 loops=1)

32. 0.001 0.029 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.86..895.79 rows=1 width=827) (actual time=0.028..0.029 rows=0 loops=1)

33. 0.000 0.028 ↓ 0.0 0 1

Nested Loop (cost=2.56..894.45 rows=1 width=790) (actual time=0.028..0.028 rows=0 loops=1)

34. 0.000 0.028 ↓ 0.0 0 1

Nested Loop (cost=2.28..892.58 rows=1 width=781) (actual time=0.028..0.028 rows=0 loops=1)

35. 0.001 0.028 ↓ 0.0 0 1

Nested Loop (cost=1.72..890.96 rows=1 width=615) (actual time=0.028..0.028 rows=0 loops=1)

36. 0.003 0.016 ↑ 16.0 1 1

Nested Loop (cost=1.14..862.67 rows=16 width=467) (actual time=0.015..0.016 rows=1 loops=1)

37. 0.006 0.006 ↑ 38.0 1 1

Index Scan using rfq_idx_id on quote_sq qs_2 (cost=0.57..7.54 rows=38 width=18) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (rfq_idx_id = 40685780)
38. 0.007 0.007 ↑ 67.0 1 1

Index Scan using quote_per_loc_quote_sq_id on quote_sq_per_loc pl_2 (cost=0.57..21.83 rows=67 width=457) (actual time=0.006..0.007 rows=1 loops=1)

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

Index Scan using quote_sq_skus_per_loc_amount_type on quote_sq_skus s_3 (cost=0.57..1.76 rows=1 width=152) (actual time=0.011..0.011 rows=0 loops=1)

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

Index Scan using req_sq_idx_pkey on req_sq_idx r_2 (cost=0.56..1.61 rows=1 width=174) (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..1.86 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..1.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..1.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.035 2.105 ↑ 10.2 5 1

HashAggregate (cost=14.30..14.81 rows=51 width=4,037) (actual time=2.101..2.105 rows=5 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, "*SELECT* 1".qty, "*SELECT* 1".class, "*
46. 0.003 2.070 ↑ 10.2 5 1

Append (cost=0.00..2.44 rows=51 width=4,037) (actual time=0.161..2.070 rows=5 loops=1)

47. 0.013 0.283 ↑ 8.5 4 1

Subquery Scan on *SELECT* 1 (cost=0.00..1.62 rows=34 width=4,037) (actual time=0.161..0.283 rows=4 loops=1)

48. 0.270 0.270 ↑ 8.5 4 1

CTE Scan on leg1 (cost=0.00..0.68 rows=34 width=3,841) (actual time=0.153..0.270 rows=4 loops=1)

49. 0.007 1.755 ↑ 16.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.76 rows=16 width=4,037) (actual time=1.752..1.755 rows=1 loops=1)

50. 1.748 1.748 ↑ 16.0 1 1

CTE Scan on leg2 (cost=0.00..0.32 rows=16 width=3,841) (actual time=1.745..1.748 rows=1 loops=1)

51. 0.001 0.029 ↓ 0.0 0 1

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

52. 0.028 0.028 ↓ 0.0 0 1

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

53.          

CTE prod_type

54. 0.009 0.042 ↑ 16.0 1 1

GroupAggregate (cost=873.00..873.36 rows=16 width=40) (actual time=0.042..0.042 rows=1 loops=1)

  • Group Key: pl_3.quote_sq_id
55. 0.011 0.033 ↑ 16.0 1 1

Sort (cost=873.00..873.04 rows=16 width=13) (actual time=0.033..0.033 rows=1 loops=1)

  • Sort Key: pl_3.quote_sq_id
  • Sort Method: quicksort Memory: 25kB
56. 0.001 0.022 ↑ 16.0 1 1

Nested Loop (cost=1.71..872.68 rows=16 width=13) (actual time=0.021..0.022 rows=1 loops=1)

57. 0.001 0.016 ↑ 16.0 1 1

Nested Loop (cost=1.14..862.67 rows=16 width=8) (actual time=0.015..0.016 rows=1 loops=1)

58. 0.006 0.006 ↑ 38.0 1 1

Index Scan using rfq_idx_id on quote_sq sq (cost=0.57..7.54 rows=38 width=4) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (rfq_idx_id = 40685780)
59. 0.009 0.009 ↑ 67.0 1 1

Index Scan using quote_per_loc_quote_sq_id on quote_sq_per_loc pl_3 (cost=0.57..21.83 rows=67 width=8) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (quote_sq_id = sq.id)
  • Filter: show
60. 0.005 0.005 ↑ 1.0 1 1

Index Scan using req_sq_idx_pkey on req_sq_idx r_3 (cost=0.56..0.62 rows=1 width=13) (actual time=0.005..0.005 rows=1 loops=1)

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

CTE site_mrc

62. 0.015 0.037 ↑ 102.0 1 1

GroupAggregate (cost=7,235.60..7,239.17 rows=102 width=68) (actual time=0.037..0.037 rows=1 loops=1)

  • Group Key: qss.quote_sq_per_loc_id
63. 0.009 0.022 ↑ 25.5 4 1

Sort (cost=7,235.60..7,235.86 rows=102 width=21) (actual time=0.022..0.022 rows=4 loops=1)

  • Sort Key: qss.quote_sq_per_loc_id
  • Sort Method: quicksort Memory: 25kB
64. 0.000 0.013 ↑ 25.5 4 1

Nested Loop (cost=1.15..7,232.20 rows=102 width=21) (actual time=0.010..0.013 rows=4 loops=1)

65. 0.005 0.005 ↑ 38.0 1 1

Index Scan using rfq_idx_id on quote_sq qs_3 (cost=0.57..7.54 rows=38 width=4) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (rfq_idx_id = 40685780)
66. 0.008 0.008 ↑ 52.8 4 1

Index Scan using quote_sq_skus_quote_sq_id_amount_type_class_idx on quote_sq_skus qss (cost=0.57..188.01 rows=211 width=25) (actual time=0.005..0.008 rows=4 loops=1)

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

CTE site_nrc

68. 0.016 0.041 ↑ 49.0 1 1

GroupAggregate (cost=3,624.27..3,625.99 rows=49 width=68) (actual time=0.041..0.041 rows=1 loops=1)

  • Group Key: qss_1.quote_sq_per_loc_id
69. 0.009 0.025 ↑ 49.0 1 1

Sort (cost=3,624.27..3,624.40 rows=49 width=21) (actual time=0.025..0.025 rows=1 loops=1)

  • Sort Key: qss_1.quote_sq_per_loc_id
  • Sort Method: quicksort Memory: 25kB
70. 0.002 0.016 ↑ 49.0 1 1

Nested Loop (cost=1.15..3,622.90 rows=49 width=21) (actual time=0.016..0.016 rows=1 loops=1)

71. 0.005 0.005 ↑ 38.0 1 1

Index Scan using rfq_idx_id on quote_sq qs_4 (cost=0.57..7.54 rows=38 width=4) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (rfq_idx_id = 40685780)
72. 0.009 0.009 ↑ 102.0 1 1

Index Scan using quote_sq_skus_quote_sq_id_amount_type_class_idx on quote_sq_skus qss_1 (cost=0.57..94.12 rows=102 width=25) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: ((quote_sq_id = qs_4.id) AND (amount_type = 'NRC'::text))
73. 0.185 2.521 ↑ 3.2 5 1

Sort (cost=6.51..6.55 rows=16 width=4,205) (actual time=2.519..2.521 rows=5 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.type, s.sub_type, s.market, s.sub_market,
  • Sort Method: quicksort Memory: 27kB
74. 0.020 2.336 ↑ 3.2 5 1

Hash Right Join (cost=3.61..6.19 rows=16 width=4,205) (actual time=2.330..2.336 rows=5 loops=1)

  • Hash Cond: (smrc.quote_sq_per_loc_id = s.quote_sq_per_loc_id)
75. 0.038 0.038 ↑ 102.0 1 1

CTE Scan on site_mrc smrc (cost=0.00..2.04 rows=102 width=68) (actual time=0.037..0.038 rows=1 loops=1)

76. 0.007 2.278 ↑ 3.2 5 1

Hash (cost=3.41..3.41 rows=16 width=4,101) (actual time=2.278..2.278 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
77. 0.019 2.271 ↑ 3.2 5 1

Hash Right Join (cost=2.09..3.41 rows=16 width=4,101) (actual time=2.265..2.271 rows=5 loops=1)

  • Hash Cond: (snrc.quote_sq_per_loc_id = s.quote_sq_per_loc_id)
78. 0.044 0.044 ↑ 49.0 1 1

CTE Scan on site_nrc snrc (cost=0.00..0.98 rows=49 width=68) (actual time=0.043..0.044 rows=1 loops=1)

79. 0.009 2.208 ↑ 3.2 5 1

Hash (cost=1.89..1.89 rows=16 width=4,037) (actual time=2.208..2.208 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
80. 0.019 2.199 ↑ 3.2 5 1

Hash Join (cost=0.52..1.89 rows=16 width=4,037) (actual time=2.166..2.199 rows=5 loops=1)

  • Hash Cond: (s.quote_sq_id = p.quote_sq_id)
81. 2.134 2.134 ↑ 10.2 5 1

CTE Scan on skus s (cost=0.00..1.02 rows=51 width=4,005) (actual time=2.105..2.134 rows=5 loops=1)

82. 0.003 0.046 ↑ 16.0 1 1

Hash (cost=0.32..0.32 rows=16 width=36) (actual time=0.046..0.046 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
83. 0.043 0.043 ↑ 16.0 1 1

CTE Scan on prod_type p (cost=0.00..0.32 rows=16 width=36) (actual time=0.043..0.043 rows=1 loops=1)

Planning time : 20.281 ms
Execution time : 5.132 ms